Impact of CLR Strict Security configuration setting in SQL Server 2017

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.

1	SELECT * FROM sys.configurations WHERE name LIKE 'clr strict security';

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:

Create Assembly from File

1
2
3
4

USE SampleDB;
GO
CREATE ASSEMBLY CLRStringSplit FROM 'C:\sqlclr\CLRStringSplit.dll' WITH PERMISSION_SET = SAFE;
GO

Script 2

The newly created assembly is shown in Figure 2.

Figure 2

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))

EXTERNAL NAME [CLRStringSplit].[CLRStringSplit.StringSplit].[Split];

Script 3

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

1

SELECT * FROM dbo.Split('Jan,Feb,Mar,Apr,May,Jun', ',');

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

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;

RECONFIGURE;

EXEC sp_configure 'clr strict security',0;

RECONFIGURE;

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.

1 2	ALTER DATABASE SampleTest2017 SET TRUSTWORTHY ON; GO

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

1
2
3
4

USE master;
GO
CREATE ASYMMETRIC KEY CLRStringSplitKey FROM EXECUTABLE FILE = 'C:\CLRStringSplit.dll';
GO

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

1
2
3
4

USE master;
GO
CREATE LOGIN CLRStringSplitKeyLogin FROM ASYMMETRIC KEY CLRStringSplitKey;
GO

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

1
2
3
4

USE master;
GO
GRANT UNSAFE ASSEMBLY TO CLRStringSplitKeyLogin;
GO

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

1
2
3
4

USE SampleTest2017;
GO
CREATE USER CLRStringSplitKeyLogin FOR LOGIN CLRStringSplitKeyLogin;
GO

Script 10
Step 5: Create CLR Assembly

1
2
3
4

USE SampleTest2017;
GO
CREATE ASSEMBLY CLRStringSplit FROM 'C:\CLRStringSplit.dll' WITH PERMISSION_SET = SAFE;
GO

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:

Create Certificate

1
2
3
4

USE master;
GO
CREATE CERTIFICATE CLRStringSplitCert FROM FILE = 'C:\sqlclr\CLRStringSplit.cer';
GO

Script 12
Create SQL Server Login for the Certificate

1
2
3
4

USE master;
GO
CREATE LOGIN CLRStringSplitCertLogin FROM CERTIFICATE CLRStringSplitCert;
GO

Script 13
Grant UNSAFE assembly permission to the login created in Step 2

1
2
3
4

USE master;
GO
GRANT UNSAFE ASSEMBLY TO CLRStringSplitCertLogin;
GO

Script 14
Create a SQL Server database user for the SQL Server login created in Step 2

1
2
3
4

USE SampleTest2017;
GO
CREATE USER CLRStringSplitCertLogin FOR LOGIN CLRStringSplitCertLogin;
GO

Script 15
Create CLR Assembly

1
2
3
4

USE SampleTest2017;
GO
CREATE ASSEMBLY CLRStringSplit FROM 'C:\CLRStringSplit.dll' WITH PERMISSION_SET = SAFE;
GO

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

CLRStringSplit Visual Studio Solution File

Author
Recent Posts

Sifiso Ndlovu

Sifiso is Data Architect and Technical Lead at SELECT SIFISO – a technology consulting firm focusing on cloud migrations, data ingestion, DevOps, reporting and analytics. Sifiso has over 15 years of across private and public business sectors, helping businesses implement Microsoft, AWS and open-source technology solutions. 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.

Sifiso's LinkedIn profile

View all posts by Sifiso W. Ndlovu

Impact of CLR Strict Security feature in SQL Server 2017

Summary

Downloads

Related posts: