Sifiso W. Ndlovu

Impact of CLR Strict Security configuration setting in SQL Server 2017

February 13, 2018 by

Every seasoned SQL Server developer will tell you that no matter how hard you try, there are just operations in SQL Server better implemented elsewhere than relying on native Transact-SQL language (T-SQL). Operations such as performing complex calculations, implementing regular expression checks and accessing external web service applications can easily lead to your SQL Server instance incurring significant performance overhead. Thankfully, through its common language runtime (CLR) feature, SQL Server provides developers with a platform to address some of the inconveniences of native T-SQL by supporting an import of assembly files produced from projects written in. Net programming languages (i.e. C#, VB.NET). I have personally found CLR to be very useful when it comes to splitting string characters into multiple delimited lines.

Unfortunately – with all its benefits – the recently launched SQL Server 2017 introduces security changes in its support for the creation of CLR assemblies which could leave you at a risk of no longer continuing to enjoy the benefits of CLR within a SQL Server 2017 environment. In other words, if you don’t take any actions, your existing CLR-dependent objects (i.e. CLR functions, CLR Stored Procedures etc.) are bound to break as soon as you upgrade your database engine to SQL Server 2017. In this article, we go through the CLR security changes in SQL Server 2017 and provide several options that you could utilise to ensure that your CLR-dependent objects continue to execute in SQL Server 2017 without incurring any significant production downtime.

Impact of CLR Strict Security feature in SQL Server 2017

SQL Server 2017 introduces CLR Strict Security configuration option that – unless signed with a certificate or asymmetric keys – basically treats CLR assemblies as UNSAFE, thus preventing them from being registered into SQL Server 2017. The CLR Strict Security option is an advanced configuration setting that is enabled by default as can be verified by running the command in Script 1.

Script 1

The value of 1 shown in Figure 1 means that the CLR Strict Security option is switched on.

Figure 1

As you might recall that prior to SQL Server 2017 you could get away from having to sign a CLR assembly by simply granting it the SAFE permission. Say for instance we have successfully written and produced a CLRStringSplit.dll assembly file used to split a string and that we are now just tasked with importing the assembly into a SQL Server 2016 instance, creating a CLR function and calling that CLR function in a T-SQL query. We could achieve all of these tasks through the following steps:

  1. Create Assembly from File

    Script 2

  2. The newly created assembly is shown in Figure 2.

    Figure 2

  3. Create CLR Function

    Having, successfully created our CLR assembly, we move on to create a table-valued function that references the CLRStringSplit assembly shown in Script 3.

    Script 3

  4. Query CLR Function

    Finally, we get to test our CLR function as shown in Script 4.

    Script 4

    The results of querying Script 4 are shown in Figure 3 and as it can be seen we have successfully split our character string into multiple rows.

    Figure 3

  5. Well, when we try to repeat the above steps against a SQL Server 2017 instance we encounter an error message at the very first step as per below:

    Error Message 1

    Option #1: BAD – Disable CLR Strict Security feature

    The simplest way to ensure that all your unsigned CLR assemblies previously created for versions of SQL Server prior to SQL Server 2017 continue to work in SQL Server 2017 is to update the configuration value shown in Figure 1 from 1 to 0 – effectively disabling the CLR Strict Security feature. Although disabling the CLR Strict Security configuration option is highly unrecommended by Microsoft, it can still be done by executing the following code:

    Script 5

    Once the CLR Strict Security feature has been disabled, you should be able to successfully create your unsigned CLR assembly in SQL Server 2017 but a reminder that should you decide to re-enable the CLR Strict Security feature, all your existing SQL Server objects dependent on unsigned CLR assemblies will throw an error shown below when you next run them:

    Error Message2

    Therefore, whilst disabling of CLR Strict Security helps you avoid an immediate production downtime, on the long run it contributes to your technical debt.

    Option #2: BAD – Enable Database Trustworthy feature

    SQL Server databases have Trustworthy property set to false – which basically reduces the chances of your SQL Server instance being vulnerable to malicious attack and related hacking attempts. Similarly, to Option #1 discussed above, enabling database Trustworthy setting is not recommended by Microsoft but it can still be done by running a code similar to what is shown in Script 6.

    Script 6

    Once Script 6 has been executed, you should be able to register your CLR unsigned assemblies into SQL Server 2017. Yet again, should somebody later convince you to comply to Microsoft SQL Server recommendations including disabling database Trustworthy feature, you will find yourself facing the same error message shown in Error Message 2. In other words, just like in Option #1, Option #2 also just delays the inevitable – which is getting your CLR assemblies signed.

    Furthermore, whilst the underlying theme in Options #1 and #2 are about violating Microsoft SQL Server recommendations in the name of getting your CLR assemblies to continue to work in SQL Server 2017, the tweaking of the Trustworthy setting is a much more dangerous violation as it can potentially allow attackers to have full control of your entire SQL Server instance. Thus, if you are hellbent on disregarding Microsoft’s recommendations when it comes to unsigned CLR assemblies, at least do so by implementing Option #1 instead of #2.

    Option #3: GOOD – Sign CLR Assembly with Asymmetric Key

    I like to refer to the remaining options – Option #3 and #4 – as GOOD options because they both involve dealing with the issue at hand in a constructive manner by getting the mischievous CLR assemblies signed.

    As you probably aware, the road to doing something by the book is long and daunting. Thus, unlike in Option #1 and #2 wherein the “fixes” were done solely in SQL Server Management Studio (SSMS), signing CLR assemblies means you will have to launch a few more programs alongside SSMS.

    In getting the CLR assembly signed using an asymmetric key, you have to go back to the Visual Studio solution that was used to build the assembly, navigate to its properties and sign the assembly as shown in Figure 4.

    Figure 4

    Once you have rebuilt your CLR assembly project, you should switch back to SSMS and follow the below steps to getting your assembly signed with an asymmetric key:

    • Step 1: Create Asymmetric Key from Assembly File

      Script 7

    • Step 2: Create SQL Server Login linked to the Asymmetric Key

      Script 8

    • Step 3: Grant UNSAFE assembly permission to the login created in Step 2

      Script 9

    • Step 4: Create a SQL Server database user for the SQL Server login created in Step 2

      Script 10

    • Step 5: Create CLR Assembly

      Script 11

    Having successfully executed the above steps, you should be able to create your CLR string-split function based off a signed assembly.

    Option #4: GOOD – Sign CLR Assembly with Certificate Key

    The final option that could be used to ensure that you CLR assemblies continue to work in SQL Server 2017 involves signing those assemblies using a Certificate. To achieve this, we start off by launching Windows Command Prompt program in administrative mode and switch to a Windows Kits sub-directory, in my machine the path to that sub-directory is: C:\Program Files (x86)\Windows Kits\8.1\bin\x64

    Figure 5

    We next create a certificate using the makecert.exe program which should be in your Windows Kits directory. The command for creating a certificate as shown below:

    C:\Program Files (x86)\Windows Kits\8.1\bin\x64>makecert -r -pe -n “CN=CLRString Split Test Root Authority” -a sha256 -sky signature -cy authority -sv CLRStringS plit.pvk -len 2048 -m 144 CLRStringSplit.cer

    During the execution of the above code, you will be prompted to create a private key password in a dialog box similar to what is shown in Figure 6.

    Figure 6

    Once the certificate has been successfully created, we move on to create a PFX file using PVK2PFX.exe file as shown below:

    C:\Program Files (x86)\Windows Kits\8.1\bin\x64>PVK2PFX -pvk CLRStringSplit.pvk -spc CLRStringSplit.cer -pfx CLRStringSplit.pfx -pi P@ssw0rd1 -po P@ssw0rd1

    Finally, we switch to the signtool.exe tool to sign our assembly file, as shown below:

    C:\Program Files (x86)\Windows Kits\8.1\bin\x64>signtool sign /f CLRStringSplit.pfx /p P@ssw0rd1 CLRStringSplit.dll

    At this point, you should have 3 more files added into your Windows Kits sub-directory as shown in Figure 7:

    Figure 7

    We next switch to SSMS and run the following steps:

    1. Create Certificate

      Script 12

    2. Create SQL Server Login for the Certificate

      Script 13

    3. Grant UNSAFE assembly permission to the login created in Step 2

      Script 14

    4. Create a SQL Server database user for the SQL Server login created in Step 2

      Script 15

    5. Create CLR Assembly

      Script 16

    Again, once you have successfully executed all of the above steps, you will be all set to create a safely signed CLR assembly into your SQL Server 2017 environment.

    Summary

    SQL Server 2017 promises to be an exciting release with several features designed to enhance the security of the SQL Server engine. Although the latest security requirements for CLR assemblies in SQL Server further contributes to an environment free of malicious assemblies, it could be a nightmare for some developers as their CLR-dependent scripts may suddenly break shortly after upgrading to SQL Server 2017. In this article, we have demonstrated 4 options that can be used to ensure that changes to the support for CLR assemblies in SQL Server are less disruptive.

    Downloads

    See more

    Automatically fill in SQL keywords and names with ApexSQL Complete, a free T-SQL complete add-in

    References



    Sifiso W. Ndlovu

    Sifiso W. Ndlovu

    Sifiso is a Johannesburg based certified professional within a wide range of Microsoft Technology Competencies such SQL Server and Visual Studio Application Lifecycle Management.

    He is the member of the Johannesburg SQL User Group and also hold a Master’s Degree in MCom IT Management from the University of Johannesburg.

    He currently works for Clientele Life as an Assistant Manager in Business Software Solutions.

    View all posts by Sifiso W. Ndlovu
    Sifiso W. Ndlovu
SQL Server 2017, T-SQL

About Sifiso W. Ndlovu

Sifiso is a Johannesburg based certified professional within a wide range of Microsoft Technology Competencies such SQL Server and Visual Studio Application Lifecycle Management. He is the member of the Johannesburg SQL User Group and also hold a Master’s Degree in MCom IT Management from the University of Johannesburg. He currently works for Clientele Life as an Assistant Manager in Business Software Solutions. View all posts by Sifiso W. Ndlovu

3,118 Views