Htydjtk

First, this is for SQL Server 2016. If I was on 2017+, I would be using sp_add_trusted_assembly. Just wanted to clarify that before asking the question.

How do you register the assembly System.DirectoryServices.AccountManagement.dll without using TRUSTWORTHY ON? I cannot get it to work using an asymmetric key generated off of System.DirectoryServices.dll. The AccountManagement dll is signed differently than System.DirectoryServices.dll.

I've even tried creating a separate asymmetric key off of System.DirectoryServices.AccountManagement.dll but that results in:

Msg 15468, Level 16, State 7, Line XXXXX

An error occurred during the generation of the asymmetric key.

Here is a test script I have written to try to create this assembly.

USE master



IF DB_ID('CLR_Test') IS NULL BEGIN

    CREATE DATABASE CLR_Test

END

GO



USE [CLR_Test]

GO



EXEC sp_configure @configname=clr_enabled, @configvalue=1

GO

RECONFIGURE

GO



/*************************************************************************************/

-- DROP OBJECTS IF FOUND FIRST

/*************************************************************************************/



-- DROP System.DirectoryServices.AccountManagement

IF EXISTS(SELECT 1 FROM sys.assemblies WHERE name = 'System.DirectoryServices.AccountManagement') BEGIN 

    RAISERROR( 'DROP ASSEMBLY [System.DirectoryServices.AccountManagement]', 0, 1) WITH NOWAIT

    DROP ASSEMBLY [System.DirectoryServices.AccountManagement]

END



-- DROP System.DirectoryServices.Protocols

IF EXISTS(SELECT 1 FROM sys.assemblies WHERE name = 'System.DirectoryServices.Protocols') BEGIN 

    RAISERROR( 'DROP ASSEMBLY [System.DirectoryServices.Protocols]', 0, 1) WITH NOWAIT

    DROP ASSEMBLY [System.DirectoryServices.Protocols]

END



-- DROP System.DirectoryServices

IF EXISTS(SELECT 1 FROM sys.assemblies WHERE name = 'System.DirectoryServices') BEGIN 

    RAISERROR( 'DROP ASSEMBLY [System.DirectoryServices]', 0, 1) WITH NOWAIT

    DROP ASSEMBLY [System.DirectoryServices]

END

GO

IF EXISTS(SELECT 1 FROM sys.database_principals dp WHERE dp.name = 'MSFT_CLR_Login') 

BEGIN

    RAISERROR( 'DROP USER [MSFT_CLR_Login]', 0, 1) WITH NOWAIT

    DROP USER [MSFT_CLR_Login] 

END

GO

USE [master]

GO

IF (EXISTS(SELECT 1 FROM master.sys.syslogins WHERE name = 'MSFT_CLR_Login'))

BEGIN

    RAISERROR( 'DROP LOGIN [MSFT_CLR_Login]', 0, 1) WITH NOWAIT

    DROP LOGIN [MSFT_CLR_Login] 

END

GO

IF (EXISTS(SELECT 1 FROM master.sys.asymmetric_keys WHERE name = 'MSFT_CLR_Key'))

BEGIN

    --DROP ASYMMETRIC KEY [ClrKey]

    RAISERROR( 'DROP ASYMMETRIC KEY [MSFT_CLR_Key]', 0, 1) WITH NOWAIT

    DROP ASYMMETRIC KEY [MSFT_CLR_Key] 

END

GO



/*************************************************************************************/

-- CREATE THE OBJECTS

/*************************************************************************************/

USE [master]

GO

IF (NOT EXISTS(SELECT 1 FROM master.sys.asymmetric_keys WHERE name = 'MSFT_CLR_Key'))

BEGIN

    --DROP ASYMMETRIC KEY [ClrKey]

    RAISERROR( 'CREATE ASYMMETRIC KEY [MSFT_CLR_Key]', 0, 1) WITH NOWAIT

    CREATE ASYMMETRIC KEY [MSFT_CLR_Key] 

    FROM EXECUTABLE FILE = 'C:WindowsMicrosoft.NETFrameworkv4.0.30319System.DirectoryServices.dll'

END

GO

IF (NOT EXISTS(SELECT 1 FROM master.sys.syslogins WHERE name = 'MSFT_CLR_Login'))

BEGIN

    RAISERROR( 'CREATE LOGIN [MSFT_CLR_Login]', 0, 1) WITH NOWAIT

    CREATE LOGIN [MSFT_CLR_Login] FROM ASYMMETRIC KEY [MSFT_CLR_Key]

END

GO



RAISERROR( 'GRANT UNSAFE ASSEMBLY', 0, 1) WITH NOWAIT

GRANT UNSAFE ASSEMBLY TO [MSFT_CLR_Login]

GO

RAISERROR( 'GRANT EXTERNAL ASSEMBLY', 0, 1) WITH NOWAIT

GRANT EXTERNAL ACCESS ASSEMBLY TO [MSFT_CLR_Login]

GO



USE CLR_Test

GO



IF NOT EXISTS(SELECT 1 FROM sys.database_principals dp WHERE dp.name = 'MSFT_CLR_Login') 

BEGIN

    RAISERROR( 'CREATE USER [MSFT_CLR_Login]', 0, 1) WITH NOWAIT

    CREATE USER [MSFT_CLR_Login] FOR LOGIN [MSFT_CLR_Login]

END

GO



/*************************************************************************************/

-- CREATE THE CLR OBJECTS

/*************************************************************************************/



USE CLR_Test

GO



/****************************************************************************/

-- [System.DirectoryServices]

/****************************************************************************/

CREATE ASSEMBLY [System.DirectoryServices] 

FROM 'C:WindowsMicrosoft.NETFrameworkv4.0.30319System.DirectoryServices.dll'

WITH PERMISSION_SET = UNSAFE



/****************************************************************************/

-- [System.DirectoryServices.Protocols]

/****************************************************************************/

CREATE ASSEMBLY [System.DirectoryServices.Protocols] 

FROM 'C:WindowsMicrosoft.NETFrameworkv4.0.30319System.DirectoryServices.Protocols.dll'

WITH PERMISSION_SET = UNSAFE



/****************************************************************************/

-- [System.DirectoryServices.AccountManagement]

/****************************************************************************/

CREATE ASSEMBLY [System.DirectoryServices.AccountManagement] 

FROM 'C:WindowsMicrosoft.NETFrameworkv4.0.30319System.DirectoryServices.AccountManagement.dll'

WITH PERMISSION_SET = UNSAFE







/*

-- NOR CAN YOU CREATE AN ASSYMETRIC KEY OFF System.DirectoryServices.AccountManagement.dll



CREATE ASYMMETRIC KEY [MSFT_SDA_CLR_Key] 

FROM EXECUTABLE FILE = 'C:WindowsMicrosoft.NETFrameworkv4.0.30319System.DirectoryServices.AccountManagement.dll'



-- results in:

--      Msg 15468, Level 16, State 7, Line 130

--      An error occurred during the generation of the asymmetric key.

*/

No, an Asymmetric Key won't work, most likely because strong-naming assemblies changed in .NET 4.5 or somewhere around there, to now be Enhanced Strong Naming, which is not supported by SQL Server's CLR host (not without resigning it, which won't work here).

The key is to use Certificates. Create a Certificate in master from the DLL, then create the Login from that Certificate, and finally grant the UNSAFE ASSEMBLY permission to that Login.

It should be that simple (assuming, of course, that the DLL is pure-MSIL and not mixed, because only pure-MSIL assemblies can be loaded into SQL Server).

Also:

1. You shouldn't have to add / drop more than the main assembly, assuming that they are referenced by the main one. When adding assemblies via DLL (as opposed to a hex bytes / VARBINARY literal), SQL Server will grab any referenced assemblies that are in the same folder. It will set all of those auto-added assemblies to "visible = 0". Assemblies that are set to "visible = 0" that are referenced by a main assembly are automatically dropped if the main assembly is dropped.


2. You don't need to grant both UNSAFE ASSEMBLY and EXTERNAL ACCESS ASSEMBLY permissions to the signature-based login. The UNSAFE ASSEMBLY permission assumes the EXTERNAL ACCESS ASSEMBLY permission such that you can set assemblies to either PERMISSION_SET if you have the UNSAFE ASSEMBLY permission.


3. If using SQL Server 2017 or newer, there is no need to use the "trusted assemblies" feature. Please see my post here as to why and what should be done instead: SQLCLR vs. SQL Server 2017, Part 1: "CLR strict security" - The Problem. Of course, in this particular case it is a moot point since the proper approach is using the Certificate method as described above.

Please note that in other answers to similar questions, whether answered by myself or others, the answer was always that TRUSTWORTHY had to be enabled. This was due to misinformation provided by the Microsoft documentation that I did not realize until more recently. I am working on correcting both my previous answers and that documentation.

This answer is based upon Solomons suggestion above. The credit is all his. I rewrote my script as he suggested and it now works!!! TY SOLOMON! I am merely posting it as an example for others who come after me.

USE master



IF DB_ID('CLR_Test') IS NULL BEGIN

    CREATE DATABASE CLR_Test

END

GO



USE [CLR_Test]

GO



EXEC sp_configure @configname=clr_enabled, @configvalue=1

GO

RECONFIGURE

GO





/*************************************************************************************/

-- DROP OBJECTS IF FOUND FIRST

/*************************************************************************************/



-- DROP System.DirectoryServices.AccountManagement

IF EXISTS(SELECT 1 FROM sys.assemblies WHERE name = 'System.DirectoryServices.AccountManagement') BEGIN 

    RAISERROR( 'DROP ASSEMBLY [System.DirectoryServices.AccountManagement]', 0, 1) WITH NOWAIT

    DROP ASSEMBLY [System.DirectoryServices.AccountManagement]

END



-- DROP System.DirectoryServices.Protocols

IF EXISTS(SELECT 1 FROM sys.assemblies WHERE name = 'System.DirectoryServices.Protocols') BEGIN 

    RAISERROR( 'DROP ASSEMBLY [System.DirectoryServices.Protocols]', 0, 1) WITH NOWAIT

    DROP ASSEMBLY [System.DirectoryServices.Protocols]

END



-- DROP System.DirectoryServices

IF EXISTS(SELECT 1 FROM sys.assemblies WHERE name = 'System.DirectoryServices') BEGIN 

    RAISERROR( 'DROP ASSEMBLY [System.DirectoryServices]', 0, 1) WITH NOWAIT

    DROP ASSEMBLY [System.DirectoryServices]

END

GO

IF EXISTS(SELECT 1 FROM sys.database_principals dp WHERE dp.name = 'MSFT_CLR_Login') 

BEGIN

    RAISERROR( 'DROP USER [MSFT_CLR_Login]', 0, 1) WITH NOWAIT

    DROP USER [MSFT_CLR_Login] 

END

GO

USE [master]

GO

IF (EXISTS(SELECT 1 FROM master.sys.syslogins WHERE name = 'MSFT_CLR_Login'))

BEGIN

    RAISERROR( 'DROP LOGIN [MSFT_CLR_Login]', 0, 1) WITH NOWAIT

    DROP LOGIN [MSFT_CLR_Login] 

END

GO

IF (EXISTS(SELECT 1 FROM master.sys.certificates WHERE name = 'MSFT_CLR_Cert'))

BEGIN

    --DROP ASYMMETRIC KEY [ClrKey]

    RAISERROR( 'DROP CERTIFICATE [MSFT_CLR_Cert]', 0, 1) WITH NOWAIT

    DROP CERTIFICATE [MSFT_CLR_Cert] 

END

GO



/*************************************************************************************/

-- CREATE THE OBJECTS

/*************************************************************************************/

USE [master]

GO



RAISERROR( 'CREATE CERTIFICATE [MSFT_CLR_Cert]', 0, 1) WITH NOWAIT

CREATE CERTIFICATE [MSFT_CLR_Cert] FROM EXECUTABLE FILE = 'C:WindowsMicrosoft.NETFrameworkv4.0.30319System.DirectoryServices.dll'



RAISERROR( 'CREATE LOGIN [MSFT_CLR_Login]', 0, 1) WITH NOWAIT

CREATE LOGIN [MSFT_CLR_Login] FROM CERTIFICATE [MSFT_CLR_Cert]



RAISERROR( 'GRANT UNSAFE ASSEMBLY', 0, 1) WITH NOWAIT

GRANT UNSAFE ASSEMBLY TO [MSFT_CLR_Login]

GO



USE CLR_Test

GO



RAISERROR( 'CREATE USER [MSFT_CLR_Login]', 0, 1) WITH NOWAIT

CREATE USER [MSFT_CLR_Login] FOR LOGIN [MSFT_CLR_Login]





/*************************************************************************************/

-- CREATE THE CLR OBJECTS

/*************************************************************************************/



USE CLR_Test

GO



/****************************************************************************/

-- [System.DirectoryServices]

/****************************************************************************/

CREATE ASSEMBLY [System.DirectoryServices] 

FROM 'C:WindowsMicrosoft.NETFrameworkv4.0.30319System.DirectoryServices.dll'

WITH PERMISSION_SET = UNSAFE



/****************************************************************************/

-- [System.DirectoryServices.Protocols]

/****************************************************************************/

CREATE ASSEMBLY [System.DirectoryServices.Protocols] 

FROM 'C:WindowsMicrosoft.NETFrameworkv4.0.30319System.DirectoryServices.Protocols.dll'

WITH PERMISSION_SET = UNSAFE



/****************************************************************************/

-- [System.DirectoryServices.AccountManagement]

/****************************************************************************/

CREATE ASSEMBLY [System.DirectoryServices.AccountManagement] 

FROM 'C:WindowsMicrosoft.NETFrameworkv4.0.30319System.DirectoryServices.AccountManagement.dll'

WITH PERMISSION_SET = UNSAFE