2 of 3
edited tags
McNets
  • 23.7k
  • 10
  • 48
  • 88

CREATE ASSEMBLY System.DirectoryServices.AccountManagement.dll without using trustworthy

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.
*/