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 130
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:\Windows\Microsoft.NET\Framework\v4.0.30319\System.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:\Windows\Microsoft.NET\Framework\v4.0.30319\System.DirectoryServices.dll'
WITH PERMISSION_SET = UNSAFE
/****************************************************************************/
-- [System.DirectoryServices.Protocols]
/****************************************************************************/
CREATE ASSEMBLY [System.DirectoryServices.Protocols]
FROM 'C:\Windows\Microsoft.NET\Framework\v4.0.30319\System.DirectoryServices.Protocols.dll'
WITH PERMISSION_SET = UNSAFE
/****************************************************************************/
-- [System.DirectoryServices.AccountManagement]
/****************************************************************************/
CREATE ASSEMBLY [System.DirectoryServices.AccountManagement]
FROM 'C:\Windows\Microsoft.NET\Framework\v4.0.30319\System.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:\Windows\Microsoft.NET\Framework\v4.0.30319\System.DirectoryServices.AccountManagement.dll'
-- results in:
-- Msg 15468, Level 16, State 7, Line 130
-- An error occurred during the generation of the asymmetric key.
*/