Load assembly from a certificate in SQL Server

We received an assembly from external vendor as

CREATE ASSEMBLY MYCALC_DLL
AUTHORIZATION dbo
FROM 0x42A728.... 
WITH PERMISSION_SET = UNSAFE
GO

And this DLL is been called from an SP.

We would not like to set TRUSTWORTHY ON for the database to load this assembly into SQL Server DB and was exploring options. Came across these links
https://nielsberglund.com/2017/07/01/sqlclr-and-certificates/

https://sqlquantumleap.com/2017/08/09/sqlclr-vs-sql-server-2017-part-2-clr-strict-security-solution-1/

https://sqlquantumleap.com/2017/08/16/sqlclr-vs-sql-server-2017-part-3-clr-strict-security-solution-2/

However these links use tools like MAKECERT (replaced by new-selfsignedcertificate), pvk2pfx.exe and signtool.exe are not existing on our win2016 servers.

Also would the DLL be called with this approach

Enable TRUSTWORTHY in the DB ->
Install the assembly in the DB AS WITH PERMISSION_SET = UNSAFE->
Disable TRUSTWORTHY in the DB

I suppose that's a stupid question, however i'm loading assembly as UNSAFE, then what's the point to disable TRUSTWORTHY? Correct me...

We would like to use certificates. How to proceed further? And these DB's will be soon configured for AlwaysOn, would there be any complications? Thanks

Now the developer gave these files

----- It worked this way. Is this correct method?------

• Downloaded Visual Studio Community Edition 2019

• From the above set of files, opened the project file

• Build CALCproject

• It created a .dll file

• In the properties of the project, signing tab

created a new strong name key file
**Did not select the checkbox "Protect my key file with a password"
ANd chosen sha256RSA as the Signature Algorithm
Save the Project and Build, it creates a .snk file

• Copy the DLL and snk file to the SQL Server box

• Run the below script

```
USE [Master]
GO

CREATE ASYMMETRIC KEY CLR_SP_Key
FROM EXECUTABLE FILE = 'H:\CLR_SP\CALC.dll'
GO

CREATE LOGIN CLR_SP_Login FROM ASYMMETRIC KEY CLR_SP_Key
GO

GRANT UNSAFE ASSEMBLY TO

Given that you were delivered a scripted out assembly instead of a DLL (and the developer wasn't kind enough to sign it, that we know of at the moment), that won't be super easy to sign at the OS-level (the preferred method) as you would first need to create a real binary file out of that assembly (there are ways, just not built into Windows).

So, in this case, you can do the next best thing which is to sign the assembly within SQL Server. The steps would be:

-
Enable TRUSTWORTHY for the DB in which the assembly will be loaded (this is temporary!)

-
Run the script to load the assembly

-
Disable TRUSTWORTHY

-
Create a certificate in the DB in which the assembly was loaded (use CREATE CERTIFICATE)
CREATE CERTIFICATE [{cert_name}]
ENCRYPTION BY PASSWORD = '{password}'
WITH SUBJECT = 'Allow assembly to be loaded',
EXPIRY_DATE = '20991031';


-
Sign the assembly using ADD SIGNATURE
ADD SIGNATURE TO ASSEMBLY::[MYCALC_DLL]
BY CERTIFICATE [{cert_name}]
WITH PASSWORD = '{password}';


-
Copy the certificate into [master] (public key only)

-
Create a login from that certificate

-
grant that login the UNSAFE ASSEMBLY permission (the following code handles these last 3 steps)
DECLARE @Cert NVARCHAR(MAX);
SET @Cert = CONVERT(NVARCHAR(MAX), CERTENCODED(CERT_ID(N'ObjectSigner')), 1);

EXEC (N'USE [master]
CREATE CERTIFICATE [{cert_name}]
FROM BINARY = ' + @Cert + N';

CREATE LOGIN [{cert_name}]
FROM CERTIFICATE [{cert_name}];

GRANT UNSAFE ASSEMBLY TO [{cert_name}];');


Now everything should be fine.

NOTES

-
You would only need makecert.exe, pvk2pfk.exe, and signtool.exe if you had the actual DLL binary file to sign at the OS level. One of these should already exist on your machine (even if not in the PATH), and I believe it's signtool.

-
You can get the other two utilities from the Windows SDK (I will soon publish a post with links to the locations and which specific packages to get so as to not require getting them all).

-
TRUSTWORTHY and UNSAFE are two very different things. Enabling TRUSTWORTHY is a broad approach, like using impersonation (i.e. EXECUTE AS) as it isn't specific for what it's granting.

-
!! Now, it's possible that the developer of this assembly did, in fact, strong name and/or sign it. Once you have done the steps noted above to load the assembly, then you can check to see if it has been signed by trying to create an asymmetric key and certificate from it. If either one works, then you might have more options for loading this assembly again, especially if it has been signed with a certificate (in the future, if you can request that the developer sign it, that would be ideal). You can try via:
CREATE ASYMMETRIC KEY [_test_key] FROM ASSEMBLY [MYCALC_DLL];

CREATE CERTIFICATE [_test_cert] FROM ASSEMBLY [MYCALC_DLL];


RE: NEW CODE ADDED TO QUESTION

I'm not sure you saved any time or effort with the developer sending you all of the project files (sending you the strongly-named DLL binary file would have been so much easier), but based on what you have added to the question:

• When opening in Visual Studio, I would open the solution file (i.e. .sln) instead of the project file (i.e. .*proj).

• Interesting that it's a .csproj file instead of a .sqlproj file, but if it works then it works.

• On the "Signing" tab within Visual Studio: I've never tried not protecting the SNK file with a password, but I suppose it should still work since that just protects the private key, yet only the public key is needed for importing into SQL Server.

• You don't need to copy the .snk file to the SQL Server machine as it won't be used. The public key, which can be loaded into SQL Server from the .snk file, is also inside the strongly-named/signed DLL itself, so you only need the DLL (which you are already creating the asymmetric key from, based on the script shown in the question).

• No need to create the user within [target_database] as it won't be used, or doesn't need to be used. Currently, it isn't being used so that step can be skipped.

• Creating the assembly: I would start with PERMISSION_SET = SAFE to see if that works. If so, then stick with that. If you get an error, upon creating the assembly, then try UNSAFE. If SAFE works for loading the assembly but then executing the proc gets a permission error (filesystem most likely given the names of some of the source code files), then alter the assembly to have PERMISSION_SET = EXTERNAL_ACCESS instead of SAFE.

• Not sure if the CREATE PROCEDURE statement is complete or redacted, but the AS EXTERNAL NAME part is incomplete. You currently only have the assembly name, DBCALC_DLL, when you also need the name of the class that the method you are calling is in, and then the name of the method that you are calling (e.g. DBCALC_DLL.class.method).

• Just out of curiosity, why is this called "PROC", even with "_SP" in the name, yet it's a function?

StackExchange Database Administrators Q#300601, answer score: 3