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:\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.
*/
Best Answer
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 theUNSAFE 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:
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.UNSAFE ASSEMBLY
andEXTERNAL ACCESS ASSEMBLY
permissions to the signature-based login. TheUNSAFE ASSEMBLY
permission assumes theEXTERNAL ACCESS ASSEMBLY
permission such that you can set assemblies to eitherPERMISSION_SET
if you have theUNSAFE ASSEMBLY
permission.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.