Deploying SQL CLR assembly using Asymmetric key

In my opinion, One of the most complex part in deploying SQL CLR stored procedure is when you need to enable it for “External_Access” or “Unsafe”. The third option is “Safe” , this is pretty straight forward and I am not going to cover that in this blog.

External_Access privilege allows the assembly to access external resource like files, registry, environment variables etc. Where as Unsafe privilege allows the assembly to perform operations that are considered type-unsafe by the CLR verifier. Only members of the sysadmin fixed server role can create UNSAFE assemblies

So once you have decided that your assembly needs to be enabled for “External_Access” or “Unsafe”, you have two options. Either set Trustworthy Property of a database to ON or to sign your assembly with a certificate or Asymmetric key . The easiest way is to set the Trustworthy Property of a database to ON, but it’s not good from security prospective. The TRUSTWORTHY database property is used to indicate whether the instance of SQL Server trusts the database and the contents within it. But once you set it to ON , it might open up doors for malicious program. So the best way is to sign your assembly with a certificate or asymmetric key.

In this blog I am going to talk mainly about signing assembly with asymmetric key. But the process is same for signing with certificate except a few syntax where you need to refer to the certificate instead of the Asymmetric key.

Another situation when you can use asymmetric key is when you want to give permission to the assembly for a perticular resource, and you don’t want the user who calls the assembly to have access to it. Generally when a user calls a stored procedure or asembly, it runs in the context of that user, if it needs to access an external resource, it uses the user’s credential to acces it. As long as the user is logged into SQL server with windows authentication. Otherwise the SQL service account is used.

So here are the steps to Deploy your assembly into SQL server with External_Access privilege enabled. I am assuming that you already have the assembly created and the Trustworthy property of the database is set to OFF.

1) Generate a key for the Asembly dll from visual studio. You can go to Project>project properties> “Signing” tab and create the key. In my case I created the key with the same name as the dll and copied it to the same path where the dll exists (to keep it simple), otherwise you have to write a few lines of extra SQL Script to point to the key.

2) Create an asymmetric key. You can run the following script from SQL management studio to create it.

USE [Master]

GO

CREATE ASYMMETRIC KEY CLR_SP_Key

FROM EXECUTABLE FILE = 'C:\CLR_SP\bin\Debug\CLR_SP.dll'

GO

-- You can change the path of the dll as per your need

This will create a asymmetric key with the name CLR_SP_Key.

3) Create a SQL server login with the above asymmetric key.

USE [Master]

GO

CREATE LOGIN CLR_SP_Login FROM ASYMMETRIC KEY CLR_SP_Key

GO

You cannot use a existing login here. You need to create a new login and associate it with the key. In the 2^nd step, we coupled our assembly with the asymmetric key and in this step we coupled the asymmetric key with a Login. So now when ever we execute this assembly, it will run under the context of this login and not under the login of the user who is calling it.

4) Grant External_Access privilege to the login we just created.

USE [Master]

GO

GRANT EXTERNAL ACCESS ASSEMBLY TO CLR_SP_Login

GO

Till this point we were doing all operations on the master database, now we will start using the database where we want to deploy the assembly.

5) Add a database user in the SQLCLR_DB Database for the Login

USE [SQLCLR_DB]

GO

CREATE USER CLR_SP_Login FOR LOGIN CLR_SP_Login

GO

6) Now we need to deploy the assembly and grant it the External_Access privilege.

USE [SQLCLR_DB]

GO

CREATE ASSEMBLY CLR_SP FROM 'C:\CLR_SP\bin\Debug\CLR_SP.dll'

WITH PERMISSION_SET=EXTERNAL_ACCESS

GO

This step is equivalent to deploying the assembly from visual studio. Sometime when you do it from the Visual studio, it fails and when you run the above command you might get an error which says cannot find the file probably because the SQL service account doesn't have permission on that directory. This happens when the logged in user (in case of windows authentication) doesn’t have permission to access that dll. If you are logged in using SQL login that probably the SQL service account has permission to access the file and if you are deploying it from visual studio probably your windows login used doesn’t have the permission. Please provide the necessary access to the user.

7) The last step is to create a stored procedure and associate it with a perticular method in the assembly.

USE [SQLCLR_DB]

GO

CREATE PROCEDURE dbo.Proc_CLR_SP AS EXTERNAL NAME [CLR_SP].[CLR_SP].[HelloWorld] --[Assembly Name].[Class Name].[Method Name]

GO

Sometime the above script may throw an error:

Msg 6505, Level 16, State 2, Procedure Proc_CLR_SP, Line 1

Could not find Type 'CLR_SP' in assembly 'CLR_SP'.

In that case use the below format to refer to the method.

[Assembly Name].[ Assembly Name.Class Name].[Method Name]

I have also seen, that you sometime get the following error while creating the stored procedure:

Msg 6567, Level 16, State 2, Procedure spu_aa, Line 1

CREATE PROCEDURE failed because a CLR Procedure may only be defined on CLR methods that return either SqlInt32, System.Int32, void.

This happens when the method takes a parameter of type SQLString and the stored procedure you are creating is using varchar to map it instead of nvarchar.

You need to map SQLString of the SQL CLR assembly with nvarchar in SQL Server. Like this :

CREATE function dbo.Proc_CLR_SP

(

@name as nvarchar(200) ,

@name2 as nvarchar(200)

)

RETURNS nvarchar(200)

AS EXTERNAL NAME Assembly.class.method

Hope this blog will help you to deploy your SQL CLR assembly to SQL server successfully using asymmetric key.

 

Author : Snehadeep (MSFT), SQL Developer Engineer, Microsoft

Reviewed by : Jason (MSFT), SQL Escalation Services, Microsoft

Comments

• Anonymous
  March 19, 2012
  It's useful to note that if a XmlSerializers.dll assembly is needed, SQL Server expects the assembly name to be <assemblyName>XML instead of <assemblyName>.XmlSerializers.

• Anonymous
  October 31, 2013
  This guide was amazing.  Complete step-by step walkthrough that saved our team a lot of research time and pain.  Much thanks!!

• Anonymous
  December 20, 2013
  In step #1 when you created the key for the assembly, is it an asymmetric key, meaning with a private and public key ? Why is the need for creating it when in step 2 you are creating an asymmetric key too. Can you explain it please…

• Anonymous
  December 20, 2013
  Hello Ravi, In step 1 we are creating the key just as a part of signing the .net assembly with strong name. Its sole porpose is just for the SQL server to identify the dll. This is done for any shared assembly, even if its not a SQL clr assembly.

• Anonymous
  September 16, 2015
  May seem like a silly question but bare with me. So you have to create 1 Asymmetric Key and 1 login per assembly. I only since an implementation I'm looking at they seem to have signed both dll's with the same key. Used 1 dll to create the asymmetric key and use both dll's against the same login. Not sure how they've managed to get that to work with 2 separate assemblies. Does that sound right?