locked
signing clr assemblies for 2019 migration RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi, i'm showing the clr warnings from the migration assistant regarding our migration from 2014 to 2019. 

    can someone explain at a high level what mitigating this entails and why? is it safe to say that at the time the warning was issued the target 2019 "instance" didnt have one or both of the 2 more vulnerable alternatives set?  is there an easy way to see what those settings are on the source right now?

    basically we have 2 assemblies, 3 if you count one that seems to have no dependencies.  clrroutines ->fn_splitstringsclr-> 23 procs.  clrutilities->fn_splitlongstringclr->15 procs.  microsoft.sqlserver.types->no dependencies. 

    Assembly '[CLRRoutines]' with PERMISSION_SET = 'Safe' cannot be migrated. For more details, please see: Line 1, Column 1.

<p>When CLR Strict Security is enabled, attempting to create an unsigned assembly will cause an error. Use the following steps to create a signed assembly: </p>
<ol>
<li>Create a key pair file. For more information, see How to: Create a Public-Private Key Pair.</li>
<li>Compile the module and sign it using the key file. For more information, see How to: Sign an Assembly with a Strong Name.</li>
<li>Create an asymmetric key in the master database from the key file 
CREATE ASYMMETRIC KEY MyStrongNameKey FROM FILE = 'C:\MyStrongNameKey.snk'</li>
<li>Create a login from the asymmetric key 
CREATE LOGIN MyStrongNameKeyLogin FROM ASYMMETRIC KEY MyStrongNameKey</li>
<li>Grant UNSAFE ASSEMBLY permission to the login 
GRANT UNSAFE ASSEMBLY TO MyStrongNameKeyLogin</li>
<li>Now users with CREATE ASSEMBLY permissions can create assemblies signed by the key</li>
</ol>
We recommend signing assemblies by following the steps outlined above. However, you can alternatively do one of the following: 
<ul>
<li>Set the Trustworthy Property of the database to ON and grant UNSAFE ASSEMBLY permission to the login that owns the database. This is not recommended because marking a database as Trustworthy can allow malicious assemblies to impact resources outside the database. For more information, see Guidelines for using the TRUSTWORTHY database setting in SQL Server. </li>
<li>Disable CLR Strict Security by using sp_configure. This is not recommended because assemblies marked SAFE or EXTERNAL_ACCESS may enable elevation of privilege from a database user to a sysadmin.
sp_configure 'show advanced options', 1 
GO 
sp_configure 'clr strict security', 0 
GO 
RECONFIGURE 
GO</li>
</ol>
</p>

     


    Wednesday, April 1, 2020 12:38 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Originally, when the the CLR support was added to SQL 2005, there was distinction of SAFE, EXTERNAL_ACCESS and UNSAFE assemblies. The implementation of SAFE relied on seomthing called CAS, Code Access Security .NET. That is, CASE guaranteed that the safe assembly could not do dirty things.

    However, CAS eeased to be a security bounadry with .NET 3.5, and since SQL 2012, SQL Server have used .NET 4. Apparently, the SQL Server folks did not really notice the CAS change for a couple of years, but they woke up in time for SQL 2017.

    Since CAS is not a security boundary any more, all assemblies are by definition unsafe. A skillful programmer could be able to put in unsafe stuff that is able to access the internal memory of SQL Server and other ugly things inside an assembly that passes as SAFE. No, I don't know how do it, and I don't know if has been exploited.

    The text you quote lists three options. There is a fourth which may be the easiest one to manage. You can use sp_add_trusted_assembly to enter a hash for the assembly. Google this name to find the details.

    Assuming that these are assemblies you have used for years, there is little reason why you would not trust them.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, April 1, 2020 9:21 PM