Do Not Set Trustworthy On for the SSISDB Catalog

Question

Post reply

Do Not Set Trustworthy On for the SSISDB Catalog

Ronald Beuker

Old Hand

Points: 389
More actions
January 28, 2020 at 12:00 am

#3713439

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

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

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

Ant-Green SSC Guru Points: 113498 More actions · Answer 1

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: 1004290 More actions · Answer 2

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.

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

Eric M Russell SSC Guru Points: 125560 More actions · Answer 3

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 More actions · Answer 4

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 More actions · Answer 5

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

Ronald Beuker Old Hand Points: 389 More actions · Answer 6

Ant-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 4 years, 9 months ago by Ronald Beuker.

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 7

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:

TEMPORARILY enable TRUSTWORTHY for the [master] DB:
ALTER DATABASE [master] SET TRUSTWORTHY ON;
Create the Assembly in [master]:
1. get assembly bits from the [content] field in sys.assembly_files
2. CREATE ASSEMBLY [temp] FROM assembly_bits;
Create Asymmetric Key from the Assembly
Create a Login from the Asymmetric Key
Grant the Login the EXTERNAL ACCESS ASSEMBLY permission xor the UNSAFE ASSEMBLY permission (whichever one you need)
Drop the Assembly:
DROP ASSEMBLY [temp];
Re-enable TRUSTWORTHY for [master]:
ALTER DATABASE [master] SET TRUSTWORTHY OFF;

Take care,

Solomon...

This reply was modified 4 years, 9 months ago by Solomon Rutzky.

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