Share via

SQL Server 2017 CLR strict security

sakuraime 2,321 Reputation points
2021-08-05T13:55:18.73+00:00

May I know if I have such CLR in an old database , say for example SQL Server 2014 ,

I would like to do backup restore to SQL Server 2017 or SQL Server 2019 , the restore process will failed ? Or still pass , however whenever calling the CLR in new SQL Server will failed ?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,361 questions
0 comments
Accepted answer
  1. Erland Sommarskog 107.2K Reputation points
    2021-08-05T21:27:41.437+00:00

    The RESTORE will succeed. But if you have a safe assembly in the database, execution of modules defined from this assembly, will fail if the instance has "clr strict seurity" is set to 1, unless you apply any of the measures to make it trusted.

    Beware that CLR strict security can be enabled also on SQL 2012, SQL 2014 and SQL 2016 as detailed in KB article 4018930.

    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. CathyJi-MSFT 21,131 Reputation points Microsoft Vendor
    2021-08-06T07:13:42.877+00:00

    Hi @sakuraime ,

    > May I know if I have such CLR in an old database , say for example SQL Server 2014

    CRL can be enabled in SQL server 2014. You can using below T-SQL to check this.

    EXEC sp_configure 'clr enabled'

    > would like to do backup restore to SQL Server 2017 or SQL Server 2019 , the restore process will failed ? Or still pass , however whenever calling the CLR in new SQL Server will failed ?

    Yes, you can restore it to SQL 2017 or SQL 2019 successfully. Beginning with SQL Server 2017 (14.x), an sp_configure option called clr strict security is introduced to enhance the security of CLR assemblies. clr strict security is enabled by default, and treats SAFE and EXTERNAL_ACCESS assemblies as if they were marked UNSAFE. The clr strict security option can be disabled for backward compatibility, but this is not recommended.

    Refer to CLR strict security.

    This is a similar thread SQL 2014 CLR Migration to SQL 2019, hope it could help you.

    If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar thread.

    0 comments