Do Not Set Trustworthy On for the SSISDB Catalog

  • Ronald Beuker

    Old Hand

    Points: 387

    Comments posted to this topic are about the item Do Not Set Trustworthy On for the SSISDB Catalog

  • anthony.green

    SSC Guru

    Points: 112473

    Good post.

     

    Out of interest, did you manage to find the problem which caused the permission to be removed in the first place?

  • Jeff Moden

    SSC Guru

    Points: 996645

    Awesome post, Ronald. And thanks for including the methods of revelation and discovery.  Those help in effectively troubleshooting similar problems.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Eric M Russell

    SSC Guru

    Points: 125089

    To truly fix SSISDB, Microsoft would need to remove the dependencies on .NET assemblies.

     

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • jyoung

    Newbie

    Points: 3

    Eric M Russell wrote:

    To truly fix SSISDB, Microsoft would need to remove the dependencies on .NET assemblies.

    Not that it is possible, but that would pretty much render SSIS useless.

  • jyoung

    Newbie

    Points: 3

    I notice that in SQL 2017 the login ##MS_SQLEnableSystemAssemblyLoadingUser## is not present.

  • Ronald Beuker

    Old Hand

    Points: 387

    anthony.green wrote:

    Out of interest, did you manage to find the problem which caused the permission to be removed in the first place?

    Well, the only thing that was installed around the same time the problem started, was the Microsoft OLE DB Driver for SQL Server. But I find it hard to believe that installing that could have caused this.

    The other possibility, our having changed the rights of the MS_SQLEnableSystemAssemblyLoadingUser SQL user by accident, also does not seem likely at all. In my company, only 5 people (myself included) could have done that, and I'm very sure none of these people had ever even heard of this SQL user before. 😉

    • This reply was modified 5 months, 1 week ago by  Ronald Beuker.
  • Solomon Rutzky

    SSCoach

    Points: 16256

    Hi Ronald. Great job sticking with not enabling TRUSTWORTHY, and figuring out how to accomplish that!

     

    Just one error I noticed in the article that should probably be updated to be accurate, if possible. You stated:

    The assembly is physically located inside the C:\Windows\Assembly folder ...

    That is not how SQLCLR works. All assemblies that you call methods from within SQL Server are entirely contained within SQL Server (hence they are backed up when you backup a database containing any assemblies, unlike the deprecated extended stored procedures that were external DLLs). The files within C:\Windows\Assembly and its subfolders should be run-time copies.

    The assembly containing the CLR code that is executed within SQL Server can be found via:

    USE [SSISDB];
    SELECT *
    FROM sys.assembly_files
    WHERE [assembly_id] = 65536;

    The [content] field, being VARBINARY(MAX), contains an exact copy of the DLL it was loaded from. That value is the assembly that gets loaded into memory.

    The original filesystem location of the DLL can be seen in those backup/restore instructions that you linked to, in the step regarding the creation of the Asymmetric Key:

    CREATE Asymmetric Key [MS_SQLEnableSystemAssemblyLoadingKey]
    FROM Executable File =
    'C:\Program Files\Microsoft SQL Server\110\DTS\Binn\Microsoft.SqlServer.IntegrationServices.Server.dll'

     

    Of course, if you ever find yourself in a situation with an Assembly and don't have the public key it was signed with, you can do the following:

    1. TEMPORARILY enable TRUSTWORTHY for the [master] DB:

      ALTER DATABASE [master] SET TRUSTWORTHY ON;

    2. Create the Assembly in [master]:

      1. get assembly bits from the [content] field in sys.assembly_files
      2. CREATE ASSEMBLY [temp] FROM assembly_bits;

    3. Create Asymmetric Key from the Assembly
    4. Create a Login from the Asymmetric Key
    5. Grant the Login the EXTERNAL ACCESS ASSEMBLY permission xor the UNSAFE ASSEMBLY permission (whichever one you need)
    6. Drop the Assembly:

      DROP ASSEMBLY [temp];

    7. Re-enable TRUSTWORTHY for [master]:

      ALTER DATABASE [master] SET TRUSTWORTHY OFF;

     

    Take care,

    Solomon...

     

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply