Impact of CLR Strict Security configuration setting in SQL Server 2017

存储架构 2018-02-14

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';

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

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

    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 .

    CREATE FUNCTION Split (@String NVARCHAR(MAX), @delimter NVARCHAR(MAX))
    RETURNS TABLE(SplitOutput NVARCHAR(MAX))
    AS
         EXTERNAL NAME [CLRStringSplit].[CLRStringSplit.StringSplit].[Split];
    GO
    

    Script 3

  4. 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', ',');
    

    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:

    EXEC sp_configure 'show advanced options',1;
    GO
    RECONFIGURE;
    GO
    EXEC sp_configure 'clr strict security',0;
    GO
    RECONFIGURE;
    GO
    

    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 .

    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

      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

      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

      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

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

      Script 10

    • Step 5: Create CLR Assembly

      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 Kits8.1binx64

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

    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 :

    Figure 7

    We next switch to SSMS and run the following steps:

    1. Create Certificate

      USE master;
      GO
      CREATE CERTIFICATE CLRStringSplitCert FROM FILE = 'C:sqlclrCLRStringSplit.cer';
      GO
      

      Script 12

    2. Create SQL Server Login for the Certificate

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

      Script 13

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

      USE master;
      GO
      GRANT UNSAFE ASSEMBLY TO CLRStringSplitCertLogin;
      GO
      

      Script 14

    4. 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
      

      Script 15

    5. Create CLR Assembly

      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

    References

    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

    Latest posts by Sifiso W. Ndlovu ( see all )

    Related posts:

    1. How to enable and disable the Identity Cache in SQL Server 2017
    2. How to plot a SQL Server 2017 graph database using SQL Server R
    3. SQL Server security and Policy Based Management – In practice
    4. How to plot a SQL Server 2017 graph database using PowerBI
    5. SQL Server Logins, Users and Security Identifiers (SIDs)
SQL Shack

责编内容by:SQL Shack (源链)。感谢您的支持!

您可能感兴趣的

APT组织正在利用IE浏览器中的零日漏洞“double play”... 近日,来自中国防病毒厂商奇虎360 Core Security的安全研究人员观察到,一个APT组织正在利用IE浏览器中的零日漏洞——“double play”,且目前该漏洞尚未修复。 360 Core Security的安全研究人员发现IE浏览器中存在一个零日漏洞,并将其称为“double pl...
韩国信息安全局已开始调查Bithumb被盗事件,Bithumb称将对被盗数字货币负责... 17分钟前 作者:Shirly 1,425 鸵鸟区块链 消息:据MoneyToday 6月20日报道,韩国信息安全局(KISA)表示,收到Bithumb 的入侵事故报告后,为分析事故原因,已经前往现场,对此事进行调查。 据鸵鸟区块链6月20日...
Receive SQL Server Alerts through Slack By: Maria Zakourdaev | | Related Tips:More >Monitoring "SQL Server Keeps Getting Better" - click to register for this free webcast...
中汽中心情报所郭松泉:雄安新区将成中国新能源汽车最领先的示范城市... 2017年9月16日,由清华x-lab发起,联合清华大学全球产业4.5研究院、清华大学汽车工程系、启迪协信共同主办的“迎接智造•未来汽车新时代”主题论坛在清华大学经管学院伟伦楼伟伦国际报告厅举行, 共论智能汽车的产业趋势、技术前沿和创投机遇。 亿欧作为此次大会的支持媒体,参与全程报道。 ...
UK leads global upward trend in SOC maturity The past year has seen a 12% improvement in global security operations centre (SOC) maturity, with all assessment areas showing improvement for th...