How to create an asymmetric key in SQL Server

Question

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.

Answer 1

First, just to get an obvious question / issue out of the way:

I am trying to create an ... assembly in SQL Server without having to alter any global database or server permissions...

Is SQLCLR enabled on this server / instance? That is a server permission that is disabled by default. If it's not already enabled, and you're not allowed to make such changes, then the rest of this answer won't make a difference. If you aren't sure, execute the following:

EXEC sys.sp_configure 'CLR enabled';

If the run_value column (far right) shows 1, then you are good. Else, you need to start talking to some folks at your organization before continuing.

---

Assuming that SQLCLR is enabled in production, then you are off to a good start by having the assembly signed with an asymmetric key (i.e. strongly named).

The ability to load a non-SAFE assembly, or any assembly starting in SQL Server 2017 (you haven't specified which version you're using) is controlled by the EXTERNAL ACCESS ASSEMBLY and UNSAFE ASSEMBLY server-level permissions (i.e. permissions assigned to a Login, not to a DB-level User).

The asymmetric key used to sign your assembly needs to be created in the [master] database, not in the database where you are attempting to load the assembly (unless that DB is [master], of course), as it will be used to create a Login. (Side Note: hopefully you have permission to create both an Asymmetric Key, and a Login from that key, in the [master] database, else again, this information won't help).

My preference has always been to load assemblies and keys from their binary string representation (i.e. hex "bits" as referred to by the SQL Server documentation). This avoids any potential file system permission issues as everything can be in a single, self-contained SQL script. Unfortunately, because Microsoft refuses to add the FROM BINARY = option to CREATE ASYMMETRIC KEY (I've been begging them for many years now), we have to find a different way.

Prior to SQL Server 2017, which introduced the horrible, and most likely entirely unnecessary server option CLR strict security, we could create an empty SAFE assembly, signed by the same key, load that into [master], and extract the asymmetric key from it. But now even SAFE assemblies require security set up prior to loading.

This leads to a convoluted, yet effective, approach using Certificates, since they can be created from a binary string / hex bytes. But, the assembly isn't signed by a certificate, it's signed by a strong name key (i.e. asymmetric key). That leaves us with the following approach:

1. Create an empty assembly that is signed with the same strong name key .snk file used to sign the existing assembly(ies). If Visual Studio won't create a DLL from any empty code file, just include a single [SqlProcedure] method that doesn't do anything, or just declares a variable.
2. Create a certificate (if creating within SQL Server, you will need to export/backup both the certificate and the private key as they will be used outside of SQL Server).
3. Sign the empty assembly with the certificate (yes, an assembly can be simultaneously signed by a strong name key {i.e. be strongly named; this is how Visual Studio "signs" assemblies} and one or more certificates).
4. Create a Certificate (using FROM BINARY = 0x....) in [master] from the certificate used to sign the empty assembly. {this will be temporary}
5. Create a Login from the new Certificate in [master]. {this will be temporary}
6. Grant the new Login the UNSAFE ASSEMBLY permission.
7. Load the empty assembly into [master] (using FROM 0x....`). {this will be temporary}
8. Create an Asymmetric Key in [master] from the new Assembly.
9. Create a Login from the new Asymmetric Key.
10. Grant the new Asymmetric Key-based Login the UNSAFE ASSEMBLY permission.
11. Drop the empty Assembly.
12. Drop the Certificate-based Login.
13. Drop the Certificate.

Now you can load any Assembly signed with that same Asymmetric Key / Strong Name Key (.snk) into any database.

For a more detailed look at the steps noted above, please see the following post of mine:

SQLCLR vs. SQL Server 2017, Part 2: “CLR strict security” – Solution 1

---

P.S. If Microsoft were to accept my suggestion of adding the FROM BINARY = option to CREATE ASYMMETRIC KEY, just like it works for CREATE CERTIFICATE, then the steps described here could be reduced to simply:

1. Create an Asymmetric Key in [master] from the strong name key (.snk file) used to sign the assembly(ies) (using FROM BINARY = 0x....).
2. Create a Login from the new Asymmetric Key.
3. Grant the new Asymmetric Key-based Login the UNSAFE ASSEMBLY permission.

AND, that could be automated by SSDT, if they wanted to. It would make developing and deploying SQLCLR 1000 times easier, which I guess is why they aren't in a hurry to do it. Though it would also make SQL Server more secure because it would drastically reduce the number of people using SET TRUSTWORTHY ON simply due to needing to deploy an assembly and either not knowing how to do these steps, or not having the time and/or patience for the extra steps. 😿

---

P.P.S. It is also possible to use Certificates instead of an Asymmetric Key / strong name key (.snk file). Doing so will allow for a much simpler process than what was initially described above (and get closer to the ideal process if only Microsoft would add the FROM BINARY = option to CREATE ASYMMETRIC KEY). This is due to being able to create Certificates in SQL Server using FROM BINARY = 0x.... (like we saw above). If the Assembly (i.e. .dll file) is signed with a certificate, then there's no need for the empty Assembly used only to get the Asymmetric Key loaded into [master], and all of the steps associated with it.

You will still need to create a Certificate, and because "signing" within Visual Studio / SSDT really means "strong naming" (i.e. using a .snk file / Asymmetric Key), you will need to do a one-time (per Project / Assembly) manual update of the .sqlproj file to allow for using the Certificate (assuming you want to use Visual Studio / SSDT for publishing to SQL Server).

For a more detailed look at this option / method, please see the following post of mine:

SQLCLR vs. SQL Server 2017, Part 3: “CLR strict security” – Solution 2

Answer 2

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.