Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.
Security for CLR Assamblies in SQL Server 2005
The SAFE and EXTERNAL_ACCESS security settings are appropriate for most CLR assemblies, but here's one situation in which you need to use the UNSAFE setting.
Vinod Kumar
April 4, 2005
2 Min Read
With the support for managed code inside the SQL Server database, Microsoft has developed special security settings to protect the CLR’s important new objects, assemblies. Assemblies are basically managed Dynamic Link Libraris (DLLs) that contain metadata and information about dependencies and are used as a deployment unit. Because assemblies are stored in the database, they also get backed up and restored with the database.
Each assembly is associated with one of three permission sets, which you need to specify with the CREATE ASSEMBLY DDL statement.
SAFE is the default permission set, but it’s highly restrictive. With the SAFE setting, you can access only data from a local database to perform computational logic on that data.
EXTERNAL_ACCESS is the next step in the permissions hierarchy. This setting lets you access external resources such as the file system, Windows Event Viewer, and Web services. This type of resource access isn’t possible in SQL Server 2000 and earlier. This permission set also restricts operations such as pointer access that affect the robustness of your assembly.
The UNSAFE permission set assumes full trust of the assembly and thus imposes no "Code Access Security" limitations. This setting is comparable to the way extended stored procedures function—you assume all the code is safe. However, this setting does restrict the creation of unsafe assemblies to users who have sysadmin permissions. Microsoft recommends that you avoid creating unsafe assemblies as much as possible.
The SAFE and EXTERNAL_ACCESS settings are appropriate for the requirements of the examples in the main article. But I do see a niche requirement in which you might have a reason to call unmanaged code from CLR assemblies; in such a case, you’d have to use the UNSAFE permission set. While using EXTERNAL_ACCESS, you might need to use specific credentials to access external resources that your SQL Server logins don’t have permissions to access. In SQL Server and earlier releases, you use the SQL service account to solve this problem. But in SQL Server 2005, you can use the CREATE CREDENTIALS command and map a Windows NT login, then use the ALTER LOGIN command to map a SQL Server login with the credentials you created. SQL Server 2005 Books Online (BOL) contains more information about this use of the UNSAFE security setting.
About the Author
You May Also Like
.jpg?width=700&auto=webp&quality=80&disable=upscale)
