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.
SELECT * FROM sys.configurations WHERE name LIKE 'clr strict security';
The value of 1 shown in Figure 1 means that the CLR Strict Security option is switched on.
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:
Create Assembly from File1234USE SampleDB;GOCREATE ASSEMBLY CLRStringSplit FROM 'C:\sqlclr\CLRStringSplit.dll' WITH PERMISSION_SET = SAFE;GO
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.12345CREATE FUNCTION Split (@String NVARCHAR(MAX), @delimter NVARCHAR(MAX))RETURNS TABLE(SplitOutput NVARCHAR(MAX))ASEXTERNAL NAME [CLRStringSplit].[CLRStringSplit.StringSplit].[Split];GO
Query CLR Function
Finally, we get to test our CLR function as shown in Script 4.1SELECT * FROM dbo.Split('Jan,Feb,Mar,Apr,May,Jun', ',');
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.
The newly created assembly is shown in Figure 2.
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:
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:
EXEC sp_configure 'show advanced options',1;
EXEC sp_configure 'clr strict security',0;
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:
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.
ALTER DATABASE SampleTest2017 SET TRUSTWORTHY ON;
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.
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 File1234USE master;GOCREATE ASYMMETRIC KEY CLRStringSplitKey FROM EXECUTABLE FILE = 'C:\CLRStringSplit.dll';GO
Step 2: Create SQL Server Login linked to the Asymmetric Key1234USE master;GOCREATE LOGIN CLRStringSplitKeyLogin FROM ASYMMETRIC KEY CLRStringSplitKey;GO
Step 3: Grant UNSAFE assembly permission to the login created in Step 21234USE master;GOGRANT UNSAFE ASSEMBLY TO CLRStringSplitKeyLogin;GO
Step 4: Create a SQL Server database user for the SQL Server login created in Step 21234USE SampleTest2017;GOCREATE USER CLRStringSplitKeyLogin FOR LOGIN CLRStringSplitKeyLogin;GO
Step 5: Create CLR Assembly1234USE SampleTest2017;GOCREATE ASSEMBLY CLRStringSplit FROM 'C:\CLRStringSplit.dll' WITH PERMISSION_SET = SAFE;GO
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
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.
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:
We next switch to SSMS and run the following steps:
Create Certificate1234USE master;GOCREATE CERTIFICATE CLRStringSplitCert FROM FILE = 'C:\sqlclr\CLRStringSplit.cer';GO
Create SQL Server Login for the Certificate1234USE master;GOCREATE LOGIN CLRStringSplitCertLogin FROM CERTIFICATE CLRStringSplitCert;GO
Grant UNSAFE assembly permission to the login created in Step 21234USE master;GOGRANT UNSAFE ASSEMBLY TO CLRStringSplitCertLogin;GO
Create a SQL Server database user for the SQL Server login created in Step 21234USE SampleTest2017;GOCREATE USER CLRStringSplitCertLogin FOR LOGIN CLRStringSplitCertLogin;GO
Create CLR Assembly1234USE SampleTest2017;GOCREATE ASSEMBLY CLRStringSplit FROM 'C:\CLRStringSplit.dll' WITH PERMISSION_SET = SAFE;GO
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.
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.
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