What are specific steps to install signed CLR storedproc to SQL?

▲
1
▼

This has been dogging me for 2 days now. I have a CLR sp that needs EXTERNAL_ACCESS. I can deploy it via VS2010 on my dev box by setting TRUSTWORTHY ON but we don't want to do that to the production server. We purchased an AuthentiCode compatible cert and I tried to sign my assembly with that but it failed due to chaining so I followed the instructions detailed here to strip out chaining from the cert.

Next I tried signing the assembly in VS but got the error "An attempt was made to reference a token that does not exist."

So went to the commandline and signed the assembly with the de-chained cert using SignTool.exe as several bloggers have recommended. The utility reports that signing succeeded.

Now to import the assembly into SQL Server (express 2008R2) on my dev box. First Set TRUSTWORTHY off as this procedure will have to be applied to the production server. Then I run

   CREATE ASSEMBLY SqlClrProcedures from 'c:\<snip>\SqlClrProcedures.dll'
   WITH PERMISSION_SET = EXTERNAL_ACCESS

This gets the following error: *CREATE ASSEMBLY for assembly 'SqlClrProcedures' failed because assembly 'SqlClrProcedures' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission.*

I was logged in as sa. Ok so I create a user, assign him ownership of the db and grant him EXTERNAL ACCESS:

GRANT EXTERNAL ACCESS Assembly to ClrLogin

Then try

CREATE ASSEMBLY SqlClrProcedures AUTHORIZATION ClrLogin from 'c:\<snip>\SqlClrProcedures.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS

which produces the same error above.

The dbo has been granted EXTERNAL ACCESS ASSEMBLY and the assembly is signed, but I don't understand the part about the corresponding login, do I need a login for the cert?

If set TRUSTWORTHY ON just to get past the CREATE ASSEMBLY the assembly is imported fine but when I run the sp I get this error:

An error occurred in the Microsoft .NET Framework while trying to load assembly id 65573. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error: 
System.IO.FileLoadException: Could not load file or assembly 'sqlclrprocedures, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)
System.IO.FileLoadException: 
   at System.Reflection.Assembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
   at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
   at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
   at System.Reflection.Assembly.Load(String assemblyString)

so it appears the cert is not getting recognized. Can someone please tell what I am doing wrong?

sql-server
sqlclr
code-signing-certificate
asked on Stack Overflow Aug 11, 2011 by StuTheDog • edited Mar 16, 2018 by Solomon Rutzky

2 Answers

▲
1
▼

Kent Tegels has a step-by-step example showing the security process for signing a clr assembly with a certificate, then loading the certificate into the server so that the assembly is considered trusted.

answered on Stack Overflow Aug 26, 2011 by stevehem
▲
0
▼

  1. Correct, TRUSTWORTHY should be set to OFF. For more info, please see my post: PLEASE, Please, please Stop Using Impersonation, TRUSTWORTHY, and Cross-DB Ownership Chaining

  2. Signing an Assembly in Visual Studio means applying a Strong Name Key; it does not allow for signing with a Certificate (quite unfortunately).

  3. Since you are going to use a signed Assembly, you do not need to worry about the database owner (dbo) being linked to a Login that has been granted either the EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY permission (the error message indicates that the dbo's permission only matter when TRUSTWORTHY is ON). When using a signed assembly, it is the Login created from the Asymmetric Key or Certificate (that was used to sign the Assembly) that will be granted either the EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY permission.

  4. You don't need the AUTHORIZATION ClrLogin part

  5. What you need (or needed) to do is:

    1. Create a Certificate in the master Database. You can create the Certificate in a few different ways:

      • An already loaded SAFE Assembly (this would be the FROM ASSEMBLY option). HOWEVER, thanks to changes introduced in SQL Server 2017, this is no longer an option.
      • The .cer file (i.e. the public key) from the file system (this would be the FROM FILE option)
      • The .dll file from the file system (this would be the FROM EXECUTABLE FILE option)
      • The .cer file directly from a VARBINARY literal (this would be the FROM BINARY option). To easily convert that file into a hex bytes string (i.e. 0x12AB00003D...), you can use the open source BinaryFormatter command-line utility that I wrote that can be used in automation / Continuous Integration (by transforming it into a file to be imported / included), or used for manually scripting the CREATE CERTIFICATE statement (by transforming it directly to the clipboard to be pasted into a script).
    2. Create a Login from that Certificate
    3. Grant that Login either the EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY permission (starting in SQL Server 2017, just the UNSAFE ASSEMBLY permission).

Regarding SQL Server 2017

SQL Server 2017 introduced a new security feature ("CLR strict security", an advanced option) that is enabled by default and requires that ALL Assemblies, even those marked as SAFE, be signed with either an Asymmetric Key (i.e. strong name) or Certificate and have a Login (based on whatever was used to sign the Assembly) that has the UNSAFE ASSEMBLY permission. For details on how to make this work, with or without Visual Studio / SSDT, please see the following two posts of mine:

Please avoid the new Trusted Assemblies "feature" as it has many more flaws than benefits, not to mention it being entirely unnecessary in the first place given that existing functionality already handled the situation "Trusted Assemblies" was meant to address. For full details on that and a demo of the proper way to handle existing, unsigned Assemblies, please see: SQLCLR vs. SQL Server 2017, Part 4: “Trusted Assemblies” – The Disappointment.

answered on Stack Overflow Mar 16, 2018 by Solomon Rutzky

User contributions licensed under CC BY-SA 3.0