Musings of a technophile

If you ever have to deploy a SQL CLR Function that is not using Safe Permissions, then you may run into a roadblock where you get the error

CREATE ASSEMBLY for assembly 'MyProject' failed because assembly 'MyProject' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS.
The assembly is authorized when either of the following is true:
the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on;
or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission.
If you have restored or attached this database, make sure the database owner is mapped to the correct login on this server. If not, use sp_changedbowner to fix the problem.

Before I go futher, let me redefine the different permissions sets for SQL CLR

• SAFE: This permissions set is applied by default if not specified otherwise, and SAFE is the most restrictive permission set. Code executed by an assembly with SAFE permissions cannot access external system resources such as files, the network, environment variables, or the registry.
• EXTERNAL_ACCESS: This permissions set enables assemblies to access certain external system resources such as files, networks, environmental variables, and the registry.
• UNSAFE: enables assemblies unrestricted access to resources, both within and outside an instance of SQL Server. Code running from within an UNSAFE assembly can call unmanaged code as well.

SAFE is highly recommended by Microsoft.

So basically you are getting the error because using EXTERNAL or UNSAFE allows code to do something outside the scope of what SQL can normally do, and therefore for security measures SQL blocks you from just going ahead and loading this onto the server.

There are two fixes to this. The first is to go to the database you will be deploying the CLR function to and running the command

ALTER DATABASE Databasename SET TRUSTWORTHY ON;

While this works, it also is not the right solution as this will enable your database to run any CLR that is deployed to it, making it easier for bad or unreviewed code to get onto the SQL Server (again – this is bad).

The correct solution is to make an Asymmetric Key for your CLR Function, create a user to use the key, and then Grant the permission set access for that user. To do this you do the following:

USE master
GO 
-- First Create the Asymmetric Key from the Assembly
CREATE ASYMMETRIC KEY SQLCLRUserKey
FROM EXECUTABLE FILE = 'C:\SQL CLR Functions\SQLCLR\bin\Release\SQLCLR.dll'
GO

You can change “SQLCLRUserKey” to whatever you would like the key name to be, and the path to the dll must be the path to your CLR functions output dll.

-- Create the Login from the Asymmetric Key
CREATE LOGIN SQLCLRUser FROM ASYMMETRIC KEY SQLCLRUserKey
GO

This creates the user based off the key we made in step 1. Again, “SQLCLRUser” can be whatever user name you want to use, and you just need to make sure “SQLCLRUserKey” matches whatever name you used for Step 1.

-- Grant the External Access Priviledge to the Login
GRANT EXTERNAL ACCESS ASSEMBLY TO SQLCLRUser
GO

This grants the permissions for this user to deploy SQL CLR Projects with EXTERNAL permission sets. In order to do UNSAFE just change the words “EXTERNAL ACCESS” to “UNSAFE”.

Last is

USE [MyDatabase]
GO
-- Add a database user in the SQLCLR_Net Database for the Login
CREATE USER SQLCLRUser FOR LOGIN SQLCLRUser
GO

Where “MyDatabase” is the name of the database where your CLR Project will be deployed. This creates the user mapping in that database.

After this, you should be able to deploy your project as normal, and by doing these steps you will just be granting permissions for the specified CLR Project instead of ANY CLR Project (much better don’t you think?)