question

sakuraime avatar image
0 Votes"
sakuraime asked Cathyji-msft edited

SQL Server 2017 CLR strict security

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-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered Cathyji-msft edited

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.