sql-serversqlclrencryption-asymmetric

How to create an asymmetric key in SQL Server

I am trying to create an UNSAFE assembly in SQL Server without having to alter any global database or server permissions such as EXEC sp_configure 'clr strict security', 0 or ALTER DATABASE MyDatabase SET TRUSTWORTHY ON (because I'm not allowed to do either of those things on our production server). Therefore I am trying to create an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission as instructed by SSMS.

I run the command

use MyDatabase
CREATE ASYMMETRIC KEY MyUtilsKey 
FROM EXECUTABLE FILE = 'C:\Users\Me\MyCode\MyUtils.dll';

and I receive the error

--The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it.

I am the owner of MyDatabase and I have verfied that the path to the file is correct. I have also verified that MyUtils.dll is signed and has a valid key pair from the command-line as follows:

sn -v MyUtils.dll  # Assembly 'MyUtils.dll' is valid
sn -p MyUtils.snk publicKey.snk  # Public key written to publicKey.snk
sn -tp publicKey.snk  # Public key (hash algorithm: sha1): [hex key], Public key token is [hex token]

I don't know what else to try.

EDIT: I am using SQL Server 16.0.1105.1.

Solution

  • siggemannen was essentially right in his comment. When creating an asymmetric key from a DLL, the DLL must not reside in a subdirectory of C:\Users, even if you are running a local instance of SQL Server.

    So the correct sequence of steps (if you don't mind creating a key from an external DLL file) is as follows (with a nod of gratitude to Solomon Rutzky for his comprehensive answer and many other blog posts on the subject):

    1. Create a signed DLL (e.g. in Visual Studio: Project Properties > Signing > Sign the assembly) and put it in a directory that is accessible to all Windows accounts (e.g. C:\Temp).
    2. Ensure your database server has enabled the CLR by running EXEC sp_configure 'clr enabled', 1;.
    3. Switch to the master database with USE master.
    4. Create the asymmetric key from the DLL with CREATE ASYMMETRIC KEY MyUtilsKey FROM EXECUTABLE FILE = 'C:\Temp\MyUtils.dll';

    That gets you as far as creating the asymmetric key (provided you have the necessary privileges to run these commands). To go on and create the assembly from it requires these extra steps:

    1. (Still in master) Create a new SQL Server principal from the asymmetric key with CREATE LOGIN MyUtilsKeyLogin FROM ASYMMETRIC KEY MyUtilsKey.
    2. Assign the new principal permissions to create an UNSAFE ASSEMBLY with GRANT UNSAFE ASSEMBLY TO MyUtilsKeyLogin;.
    3. Now switch to your application database with USE MyDatabase.
    4. Finally, create the assembly in your application database with CREATE ASSEMBLY MyUtils FROM 'C:\Temp\MyUtils.dll' WITH PERMISSION_SET = [UN]SAFE;

    Two further points to note:

    1. CREATE ASSEMBLY MyUtils FROM 'C:\Users\Me\MyCode\MyUtils.dll' ... actually works! SQL Server can actually create assemblies from DLLs in subdirectories of C:\Users. (I have proven this many times in testing.) It just can't create asymmetric keys from DLLs in those directories. I have no idea why, and it threw me off the scent for days.
    2. GRANT UNSAFE ASSEMBLY TO MyUtilsKeyLogin; is still required even if all your assemblies are SAFE! Microsoft changed the default behaviour in SQL Server 2017 and Solomon Rutzky has written extensively on this topic (e.g., https://sqlquantumleap.com/2017/08/07/sqlclr-vs-sql-server-2017-part-1-clr-strict-security/). I can highly recommend his comprehensive tutorial on the SQL CLR at https://www.sqlservercentral.com/steps/stairway-to-sqlclr-level-1-what-is-sqlclr.