I'd like to deploy my own CLR, which would require unsafe permissions and setting the database as trustworthy.
I found that it's possible to sign assemblies with external access with an asymmetric key, but I didn't find a way to sign an unsafe assembly with it.
Is that possible? If yes, what would be the steps to do it?
Beginning with SQL Server 2017 (14. x), an sp_configure option called clr strict security is introduced to enhance the security of CLR assemblies. clr strict security is enabled by default, and treats SAFE and EXTERNAL_ACCESS assemblies as if they were marked UNSAFE .
UNSAFE enables assemblies unrestricted access to resources, both within and outside an instance of SQL Server. Code running from within an UNSAFE assembly can call unmanaged code. Note. This option is not available in a contained database.
Assemblies which are built using normal computational functions are considered as safe assemblies. But when assemblies do external operations such as reading file information, creating files, etc., they are categorized as unsafe/external assemblies.
EXTERNAL_ACCESS addresses scenarios in which the code needs to access resources outside the server, such as files, network, registry, and environment variables. Whenever the server accesses an external resource, it impersonates the security context of the user calling the managed code.
Thank you for digging a little deeper to avoid setting the database to TRUSTWORTHY ON
, which is a far too common practice, unfortunately.
The same steps are taken to sign Assemblies and create the Asymmetric Key in master
from the Assembly DLL and then create the Login from that Asymmetric Key. The only difference is to then grant that Key-based Login the UNSAFE ASSEMBLY
permission instead of the EXTERNAL ACCESS ASSEMBLY
permission.
You do not ever need both permissions as the UNSAFE ASSEMBLY
permission allows for setting Assemblies to EXTERNAL ACCESS
, though it does not hurt to have both.
For more info, in general, related to working with SQLCLR, please see the series I am writing on SQL Server Central (free registration is required to read their content):
Stairway to SQLCLR
If you are using Visual Studio / SSDT to deploy / publish your SQLCLR project, please see "Stairway to SQLCLR Level 7: Development and Security" in that Stairway series as it shows a technique for automating the creation of the Asymmetric Key and Key-based Login, neither of which can be handled using regular SSDT objects. Another, even easier technique will be shown in the next (to be published) article.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With