none
migration from 2008 to 2017 RRS feed

  • Question

  • Hi

    We migrated from 2008 to 2017  with compatibility level 140 and we faced issues CLR restriction for assembly loading.  

    then we created assembly with CERTIFICATION.

    My questions

    1)  if we set compatibility level 100 then I did not get any error all datas restored in target 2017 successfully.

    So  what are features in 2017 that I will be missing if compatibility level 100

    2)  After migrating if I change to 2017 compatibility to 140 did I miss anything.

    Please suggest

    Supersent

    Wednesday, September 4, 2019 7:17 AM

All replies

  • Please go through below articles it may helps you.

    https://sqlquantumleap.com/2018/10/30/sqlclr-vs-sql-server-2017-part-9-does-permission_set-still-matter-or-is-everything-now-unsafe/

    https://docs.microsoft.com/en-us/sql/relational-databases/clr-integration/database-objects/clr-integration-programming-model-restrictions?view=sql-server-2017


    Please click "Mark as Answer" if it solved your issue and/or "Vote as helpful" if it helped. This can be beneficial to other community members reading this thread.

    Wednesday, September 4, 2019 1:11 PM
  • Hi Supersent,

    The act of changing the compatibility level tells the database to change its feature set. That is, some features will be added, but at the same time some old features will be removed. Compatibility levels is to allow for Backward Compatibility. Each compatibility level has its own set of features. You can get more information about the difference from DATABASE Compatibility Level.

    If the application does not need to leverage enhancements that are only available in a higher database compatibility level, it is a valid approach to upgrade the SQL Server Database Engine and maintain the previous database compatibility level.

    Please refer to The Importance of Database Compatibility Level in SQL Server.

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, September 5, 2019 6:33 AM
  • We migrated from 2008 to 2017 with compatibility level 140 and we faced issues with SQLCLR restriction for assembly loading.

    Yes, this is to be expected. SQL Server 2017 introduced a new SQLCLR security feature that requires ALL Assemblies to meet the requirements of being created using WITH PERMISSION_SET = UNSAFE, even if they are using EXTERNAL_ACCESS or even SAFE. Prior to SQL Server 2017, SAFE assemblies could be loaded even if unsigned and the database was set to TRUSTWORTHY OFF. This is no longer possible starting in SQL Server 2017 when the instance-level setting of "CLR strict security" is enabled (which it is, by default). Please see the following post for details:

    SQLCLR vs. SQL Server 2017, Part 1: “CLR strict security” – The Problem

    Then we created assembly with CERTIFICATION.

    I'm not sure what this means exactly as it is not an option, at least not as stated. Do you mean that you signed the assembly before loading it into SQL Server? If so, then did this work? If so, you don't need to many any other changes.

    My questions

    1)  if we set compatibility level 100 then I did not get any error all datas restored in target 2017 successfully.

    You should not need to change compatibility level or make any configuration changes at all. ALL you need to do is the following:

    1. Create a Certificate in the database containing the assembly (use CREATE CERTIFICATE).
    2. Sign the Assembly using that Certificate (use ADD SIGNATURE).
    3. Copy the Certificate (public key only) to the [master] database (use the CERTENCODED() and CERT_ID() functions).
    4. In [master], create a Login from the Certificate (use CREATE LOGIN).
    5. In [master], grant the Certificate-based Login the "UNSAFE ASSEMBLY" permission (use GRANT).

    For details, a step-by-step walk-through, and a link to a working example script, please see:

    SQLCLR vs. SQL Server 2017, Part 4: "Trusted Assemblies" – The Disappointment

    Take care,
    Solomon Rutzky...

    _____________________________________________________________
    SQL#  https://SQLsharp.com/ ( SQLCLR library of over 340 Functions and Procedures )
    Sql Quantum Lift  https://SqlQuantumLift.com/ ( company )
    Sql Quantum Leap  https://SqlQuantumLeap.com/ ( blog )
    Info sites — Collations     •     Module Signing     •     SQLCLR
    _____________________________________________________________

     

    Thursday, September 5, 2019 1:12 PM