www.sqlshack.com Open in urlscan Pro
3.136.173.2  Public Scan

Form analysis
2 forms found in the DOM

Name: cookieAgreement —

<form name="cookieAgreement"><input type="checkbox" name="agreed" value="Agreed" checked="" style="display: none"><span class="acceptance"></span></form>

GET https://www.sqlshack.com/

<form role="search" method="get" id="searchform" action="https://www.sqlshack.com/">
  <div class="social-icons-search">
    <li class="lang-item lang-item-52 lang-item-es no-translation" style="margin-right: 5px;"><a href="/sql-server-training/">SQL Server training</a></li>
    <li class="lang-item lang-item-52 lang-item-es no-translation lang-item-first"><a lang="es-ES" hreflang="es-ES" href="https://www.sqlshack.com/es/">Español</a></li>
    <a href="/about-us" style="text-decoration:none; color:black;">
      <i class="fa fa-question-circle fa-3x" title="About" style="color: darkcyan;"></i>
    </a>
    <div style="display: inline-block; padding-right: 1em; vertical-align: top !important;">
      <p id="change-button">
        <input type="button" id="mysearchsubmit" value="">
      </p>
    </div>
  </div>
</form>

Text Content

This website uses cookies. By continuing to use this site and/or clicking the
"Accept" button you are providing consent


Quest Software and its affiliates do NOT sell the Personal Data you provide to
us either when you register on our websites or when you do business with us. For
more information about our Privacy Policy and our data protection efforts,
please visit GDPR-HQ






SQLSHACK

Skip to content




SQL Server training Español






IMPACT OF CLR STRICT SECURITY CONFIGURATION SETTING IN SQL SERVER 2017

February 13, 2018 by Sifiso W. Ndlovu

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:

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

    

 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.
    
    1
    2
    3
    4
    5
    CREATE FUNCTION Split (@String NVARCHAR(MAX), @delimter NVARCHAR(MAX))
    RETURNS TABLE(SplitOutput NVARCHAR(MAX))
    AS
         EXTERNAL NAME [CLRStringSplit].[CLRStringSplit.StringSplit].[Split];
    GO
    
    
    
    Script 3
    
    

 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:

    1
    2
    3
    4
    5
    6
    7
    8
    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.

    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:

     1. Create Certificate
        1
        2
        3
        4
        USE master;
        GO
        CREATE CERTIFICATE CLRStringSplitCert FROM FILE =
        'C:\sqlclr\CLRStringSplit.cer';
        GO
        
        
        
        Script 12
        
        
    
     2. Create SQL Server Login for the Certificate
        1
        2
        3
        4
        USE master;
        GO
        CREATE LOGIN CLRStringSplitCertLogin FROM CERTIFICATE
        CLRStringSplitCert;
        GO
        
        
        
        Script 13
        
        
    
     3. 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
        
        
    
     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 CLRStringSplitCertLogin FOR LOGIN CLRStringSplitCertLogin;
        GO
        
        
        
        Script 15
        
        
    
     5. 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


    SEE MORE
    
    Check out ApexSQL Plan, to view SQL execution plans, including comparing
    plans, stored procedure performance profiling, missing index details, lazy
    profiling, wait times, plan execution history and more
    
    
    
     

    
    
    
     * Author
     * Recent Posts
    
    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 Sambe Consulting as a Principal Consultant.
    
    View all posts by Sifiso W. Ndlovu
    Latest posts by Sifiso W. Ndlovu (see all)
     * Dynamic column mapping in SSIS: SqlBulkCopy class vs Data Flow - February
       14, 2020
     * Monitor batch statements of the Get Data feature in Power BI using SQL
       Server extended events - July 1, 2019
     * Bulk-Model Migration in SQL Server Master Data Services - May 30, 2019

    
    
    
    
    
    
    


    RELATED POSTS:
    
     1. Using SQL Bulk Insert with Strict Business Rules
     2. SQL Server security and Policy Based Management – In practice
     3. SQL Server security and Policy Based Management – Alerting
     4. SQL Server Logins, Users and Security Identifiers (SIDs)
     5. SQL Server data security feature RLS (Row-Level Security) and GDPR

Functions, Security, SQL Server 2017


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 Sambe Consulting as a Principal Consultant.
View all posts by Sifiso W. Ndlovu

View all posts by Sifiso W. Ndlovu →
42,577 Views




FOLLOW US!








POPULAR

 * SQL Convert Date functions and formats
 * SQL PARTITION BY Clause overview
 * Different ways to SQL delete duplicate rows from a SQL Table
 * SQL Variables: Basics and usage
 * How to UPDATE from a SELECT statement in SQL Server
 * SQL WHILE loop with simple examples
 * SQL Server functions for converting a String to a Date
 * CASE statement in SQL
 * Understanding the SQL MERGE statement
 * SELECT INTO TEMP TABLE statement in SQL Server
 * Overview of SQL RANK functions
 * How to backup and restore MySQL databases using the mysqldump command
 * SQL multiple joins for beginners with examples
 * INSERT INTO SELECT statement overview and examples
 * SQL Server Common Table Expressions (CTE)
 * SQL Server table hints – WITH (NOLOCK) best practices
 * SQL CROSS JOIN with examples
 * SQL percentage calculation examples in SQL Server
 * The Table Variable in SQL Server
 * SQL index overview and strategy




TRENDING

 * SQL Server Transaction Log Backup, Truncate and Shrink Operations
 * Six different methods to copy tables between databases in SQL Server
 * How to implement error handling in SQL Server
 * Working with the SQL Server command line (sqlcmd)
 * Methods to avoid the SQL divide by zero error
 * Query optimization techniques in SQL Server: tips and tricks
 * How to create and configure a linked server in SQL Server Management Studio
 * SQL replace: How to replace ASCII special characters in SQL Server
 * How to identify slow running queries in SQL Server
 * SQL varchar data type deep dive
 * How to implement array-like functionality in SQL Server
 * All about locking in SQL Server
 * SQL Server stored procedures for beginners
 * Database table partitioning in SQL Server
 * How to drop temp tables in SQL Server
 * How to determine free space and file size for SQL Server databases
 * Using PowerShell to split a string into an array
 * KILL SPID command in SQL Server
 * How to install SQL Server Express edition
 * SQL Union overview, usage and examples




SOLUTIONS

 * Read a SQL Server transaction log
 * SQL Server database auditing techniques
 * How to recover SQL Server data from accidental UPDATE and DELETE operations
 * How to quickly search for SQL database data and objects
 * Synchronize SQL Server databases in different remote sources
 * Recover SQL data from a dropped table without backups
 * How to restore specific table(s) from a SQL Server database backup
 * Recover deleted SQL data from transaction logs
 * How to recover SQL Server data from accidental updates without backups
 * Automatically compare and synchronize SQL Server data
 * Open LDF file and view LDF file content
 * Quickly convert SQL code to language-specific client code
 * How to recover a single table from a SQL Server database backup
 * Recover data lost due to a TRUNCATE operation without backups
 * How to recover SQL Server data from accidental DELETE, TRUNCATE and DROP
   operations
 * Reverting your SQL Server database back to a specific point in time
 * How to create SSIS package documentation
 * Migrate a SQL Server database to a newer version of SQL Server
 * How to restore a SQL Server database backup to an older version of SQL Server


CATEGORIES AND TIPS

 * ►Auditing and compliance (50)
    * Auditing (40)
      
    * Data classification (1)
      
    * Data masking (9)
      

   
 * Azure (280)
   
 * Azure Data Studio (46)
   
 * Backup and restore (107)
   
 * ►Business Intelligence (479)
    * Analysis Services (SSAS) (47)
      
    * Biml (10)
      
    * Data Mining (14)
      
    * Data Quality Services (4)
      
    * Data Tools (SSDT) (13)
      
    * Data Warehouse (16)
      
    * Excel (20)
      
    * General (39)
      
    * Integration Services (SSIS) (125)
      
    * Master Data Services (6)
      
    * OLAP cube (15)
      
    * PowerBI (95)
      
    * Reporting Services (SSRS) (67)
      

   
 * Data science (21)
   
 * ►Database design (224)
    * Clustering (16)
      
    * Common Table Expressions (CTE) (11)
      
    * Concurrency (1)
      
    * Constraints (8)
      
    * Data types (11)
      
    * FILESTREAM (21)
      
    * General database design (97)
      
    * Partitioning (13)
      
    * Relationships and dependencies (12)
      
    * Temporal tables (12)
      
    * Views (16)
      

   
 * ▼Database development (416)
    * Comparison (4)
      
    * Continuous delivery (CD) (5)
      
    * Continuous integration (CI) (11)
      
    * Development (145)
      
    * Functions (105)
      
    * Hyper-V (1)
      
    * Search (10)
      
    * Source Control (15)
      
    * SQL unit testing (23)
      
    * Stored procedures (34)
      
    * String Concatenation (2)
      
    * Synonyms (1)
      
    * Team Explorer (2)
      
    * Testing (35)
      
    * Visual Studio (14)
      

   
 * DBAtools (35)
   
 * DevOps (23)
   
 * DevSecOps (2)
   
 * Documentation (22)
   
 * ETL (74)
   
 * ►Features (212)
    * Adaptive query processing (11)
      
    * Bulk insert (16)
      
    * Database mail (10)
      
    * DBCC (7)
      
    * Experimentation Assistant (DEA) (3)
      
    * High Availability (35)
      
    * Query store (10)
      
    * Replication (40)
      
    * Transaction log (59)
      
    * Transparent Data Encryption (TDE) (21)
      

   
 * Importing, exporting (50)
   
 * Installation, setup and configuration (118)
   
 * Jobs (42)
   
 * ►Languages and coding (685)
    * Cursors (9)
      
    * DDL (9)
      
    * DML (6)
      
    * JSON (17)
      
    * PowerShell (77)
      
    * Python (37)
      
    * R (16)
      
    * SQL commands (196)
      
    * SQLCMD (7)
      
    * String functions (21)
      
    * T-SQL (274)
      
    * XML (15)
      

   
 * Lists (12)
   
 * Machine learning (37)
   
 * Maintenance (98)
   
 * Migration (50)
   
 * Miscellaneous (1)
   
 * ►Performance tuning (867)
    * Alerting (8)
      
    * Always On Availability Groups (81)
      
    * Buffer Pool Extension (BPE) (9)
      
    * Columnstore index (9)
      
    * Deadlocks (16)
      
    * Execution plans (125)
      
    * In-Memory OLTP (22)
      
    * Indexes (79)
      
    * Latches (5)
      
    * Locking (10)
      
    * Monitoring (100)
      
    * Performance (196)
      
    * Performance counters (28)
      
    * Performance Testing (9)
      
    * Query analysis (121)
      
    * Reports (20)
      
    * SSAS monitoring (3)
      
    * SSIS monitoring (10)
      
    * SSRS monitoring (4)
      
    * Wait types (11)
      

   
 * ►Professional development (68)
    * Professional development (27)
      
    * Project management (9)
      
    * SQL interview questions (32)
      

   
 * Recovery (32)
   
 * Security (84)
   
 * Server management (22)
   
 * SQL Azure (267)
   
 * SQL Server Management Studio (SSMS) (90)
   
 * SQL Server on Linux (14)
   
 * ▼SQL Server versions (177)
    * SQL Server 2012 (6)
      
    * SQL Server 2016 (63)
      
    * SQL Server 2017 (49)
      
    * SQL Server 2019 (57)
      
    * SQL Server 2022 (2)
      

   
 * ►Technologies (322)
    * AWS (45)
      
    * AWS RDS (56)
      
    * Azure Cosmos DB (26)
      
    * Containers (12)
      
    * Docker (8)
      
    * Graph database (13)
      
    * Kerberos (2)
      
    * Kubernetes (1)
      
    * Linux (41)
      
    * LocalDB (2)
      
    * MySQL (49)
      
    * Oracle (10)
      
    * PolyBase (10)
      
    * PostgreSQL (30)
      
    * SharePoint (4)
      
    * Ubuntu (13)
      

   
 * Uncategorized (4)
   
 * Utilities (21)
   
 * 

Helpers and best practices
 * BI performance counters
 * SQL code smells rules
 * SQL Server wait types




© 2022 Quest Software Inc. ALL RIGHTS RESERVED.   |   GDPR   |   Terms of Use  
|   Privacy




AddThis Sharing Sidebar
Share to FacebookFacebookShare to TwitterTwitterShare to LinkedInLinkedInShare
to RedditRedditShare to EmailEmail
Hide
Show
Close

AddThis

AddThis Sharing
FacebookTwitterLinkedInRedditEmail