CLR Migration to newer version of SQL Server RRS feed

  • Question

  • Suppose the existing CLR assemblies using in SQL Server 2008 is using .NET framework 3.5

    any thing need to change/recompile the CLR in order to using in SQL server 2017 ?

    And how to migrate the assemblies ? Reload again ? backup restore ?

    Sunday, June 16, 2019 6:39 PM

All replies

  • The assmeblies are part of the database, so they are no different from tables, stored procedures etc.

    There is however an important change in SQL 2017 with regards to CLR assemblies. SQL 2017 introduces something known as "CLR strict security". With this setting, all assemblies are considered unsafe, and must have a blessing of some sort. The reason for this change is that the mechanism in .NET that SQL Server relies on for the division into SAFE, EXTERNAL ACCESS and UNSAFE assemblies is no longer a security boundary.

    This is setting is on by default when you install a new instance, but off by default if you upgrade an existing instance.

    Since I don't know what sort of assemblies you have, I don't go into details on how to achieve this blessing. If you only have unsafe and external acess assemblies, there is little difference to before. If you also have safe assemblies, you have something to consider.

    Erland Sommarskog, SQL Server MVP,

    Sunday, June 16, 2019 7:30 PM
  • what I mean is , the assemblies need to rewrite ?
    Tuesday, June 18, 2019 1:21 PM
  • what I mean is , the assemblies need to rewrite ?

    No, there is no reason to change the assemblies as such. Only the read tape around them. I would recommend that you enable CLR Strict Security and the you enter your assemblies with sp_trusted_assemblies. For the assemblies you have - and which you probably trust - use hash_bytes to compute a hash to store with sp_add_trusted_assembly.
    You will however need to review how you deploy new and changed assemblies.

    Erland Sommarskog, SQL Server MVP,

    Tuesday, June 18, 2019 9:25 PM