4

We received an assembly from external vendor as

CREATE ASSEMBLY MYCALC_DLL
AUTHORIZATION dbo
FROM 0x42A728....<300,000 binary values> 
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 Assembly Files

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

<This is the first time, i ever worked with VS>

  1. Downloaded Visual Studio Community Edition 2019
  2. From the above set of files, opened the project file
  3. Build CALCproject
  4. It created a .dll file
  5. In the properties of the project, signing tab Signing 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
  6. Copy the DLL and snk file to the SQL Server box
  7. 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 CLR_SP_Login
GO

USE [target_database]
GO
CREATE USER CLR_SP_Login FOR LOGIN CLR_SP_Login
GO

CREATE ASSEMBLY DBCALC_DLL FROM 'H:\CLR_SP\CALC.dll'
WITH PERMISSION_SET=UNSAFE
GO
CREATE function dbo.Proc_CLR_SP
(
@name as nvarchar(200) ,
@name2 as nvarchar(200)
)
RETURNS nvarchar(200)
AS EXTERNAL NAME DBCALC_DLL

3
  • Do you have an in-house PKI Certificate server? If so can you not generate the certificates on a workstation that has VS Developer Tools installed (signtool etc)? If not, who would sign the certificates anyway? Oct 4, 2021 at 21:16
  • @Charlieface ....no, we don't have ...
    – Kris
    Oct 4, 2021 at 21:19
  • Perhaps install just the Developer Tools on the server and create a self-signed cert? Oct 4, 2021 at 21:20

1 Answer 1

3

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:

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

  2. Run the script to load the assembly

  3. Disable TRUSTWORTHY

  4. 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';  
    
  5. Sign the assembly using ADD SIGNATURE

    ADD SIGNATURE TO ASSEMBLY::[MYCALC_DLL]
       BY CERTIFICATE [{cert_name}]
       WITH PASSWORD = '{password}';
    
  6. Copy the certificate into [master] (public key only)

  7. Create a login from that certificate

  8. 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

  1. 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.

  2. 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).

  3. 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.

  4. !! 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:

  1. When opening in Visual Studio, I would open the solution file (i.e. .sln) instead of the project file (i.e. .*proj).
  2. Interesting that it's a .csproj file instead of a .sqlproj file, but if it works then it works.
  3. 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.
  4. 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).
  5. 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.
  6. 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.
  7. 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).
  8. Just out of curiosity, why is this called "PROC", even with "_SP" in the name, yet it's a function? 😉
4
  • Thanks a lot.... @solomon .... i will work on this.... also, what are the steps to be taken if we enable AlwaysOn for this DB (which has assembly, with certificate)....
    – Kris
    Oct 4, 2021 at 23:21
  • @Kris I haven't messed with Always On, but I don't think you will have to do anything special there. Just set it up the same way (i.e. same certificate / asymmetric key in [master] with same associated login and permission). Oct 5, 2021 at 18:59
  • Also, I just updated my answer based on the info you just added to the question. Oct 5, 2021 at 18:59
  • ...thanks for your help....yes, the posted only part of SP...however, i followed your instructions....and was able to create assembly with SAFE, and executed the SP successfully....thanks again...your details expert steps helped a lot....
    – Kris
    Oct 7, 2021 at 13:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.