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 File
USE SampleDB; GO CREATE ASSEMBLY CLRStringSplit FROM 'C:sqlclrCLRStringSplit.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 .
CREATE FUNCTION Split (@String NVARCHAR(MAX), @delimter NVARCHAR(MAX)) RETURNS TABLE(SplitOutput NVARCHAR(MAX)) AS EXTERNAL NAME [CLRStringSplit].[CLRStringSplit.StringSplit].[Split]; GO
Query CLR Function
Finally, we get to test our CLR function as shown in Script 4 .
SELECT * 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:
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:
EXEC sp_configure 'show advanced options',1; GO RECONFIGURE; GO EXEC sp_configure 'clr strict security',0; GO RECONFIGURE; GO
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; GO
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 File
USE master; GO CREATE ASYMMETRIC KEY CLRStringSplitKey FROM EXECUTABLE FILE = 'C:CLRStringSplit.dll'; GO
Step 2: Create SQL Server Login linked to the Asymmetric Key
USE master; GO CREATE LOGIN CLRStringSplitKeyLogin FROM ASYMMETRIC KEY CLRStringSplitKey; GO
Step 3: Grant UNSAFE assembly permission to the login created in Step 2
USE master; GO GRANT UNSAFE ASSEMBLY TO CLRStringSplitKeyLogin; GO
Step 4: Create a SQL Server database user for the SQL Server login created in Step 2
USE SampleTest2017; GO CREATE USER CLRStringSplitKeyLogin FOR LOGIN CLRStringSplitKeyLogin; GO
Step 5: Create CLR Assembly
USE SampleTest2017; GO CREATE 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 Kits8.1binx64
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 Kits8.1binx64>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 Kits8.1binx64>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 Kits8.1binx64>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:
USE master; GO CREATE CERTIFICATE CLRStringSplitCert FROM FILE = 'C:sqlclrCLRStringSplit.cer'; GO
Create SQL Server Login for the Certificate
USE master; GO CREATE LOGIN CLRStringSplitCertLogin FROM CERTIFICATE CLRStringSplitCert; GO
Grant UNSAFE assembly permission to the login created in Step 2
USE master; GO GRANT UNSAFE ASSEMBLY TO CLRStringSplitCertLogin; GO
Create a SQL Server database user for the SQL Server login created in Step 2
USE SampleTest2017; GO CREATE USER CLRStringSplitCertLogin FOR LOGIN CLRStringSplitCertLogin; GO
Create CLR Assembly
USE SampleTest2017; GO CREATE 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.
- SQL Server Certificates and Asymmetric Keys
- TRUSTWORTHY Database Property
- Introduction to SQL Server CLR Integration
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.
Latest posts by Sifiso W. Ndlovu ( see all )
- Impact of CLR Strict Security configuration setting in SQL Server 2017 - February 13, 2018
- How to plot a SQL Server 2017 graph database using PowerBI - January 9, 2018
- How to plot a SQL Server 2017 graph database using SQL Server R - January 3, 2018
- How to enable and disable the Identity Cache in SQL Server 2017
- How to plot a SQL Server 2017 graph database using SQL Server R
- SQL Server security and Policy Based Management – In practice
- How to plot a SQL Server 2017 graph database using PowerBI
- SQL Server Logins, Users and Security Identifiers (SIDs)