CREATE ASSEMBLY System.DirectoryServices.AccountManagement.dll without enabling TRUSTWORTHYCREATE DATABASE...
How can animals be objects of ethics without being subjects as well?
How to avoid being sexist when trying to employ someone to function in a very sexist environment?
Early credit roll before the end of the film
If I delete my router's history can my ISP still provide it to my parents?
How can I deliver in-universe written lore to players without it being dry exposition?
Which one of these password policies is more secure?
Is a debit card dangerous in my situation?
Cookies - Should the toggles be on?
Are there any modern advantages of a fire piston?
Can a person refuse a presidential pardon?
Can a hotel cancel a confirmed reservation?
Why Normality assumption in linear regression
What is the wife of a henpecked husband called?
How to deal with an incendiary email that was recalled
Does SQL Server 2017, including older versions, support 8k disk sector sizes?
Would a National Army of mercenaries be a feasible idea?
Word or phrase for showing great skill at something WITHOUT formal training in it
Why did other German political parties disband so fast when Hitler was appointed chancellor?
How to solve a large system of linear algebra?
Could a phylactery of a lich be a mirror or does it have to be a box?
If I deleted a game I lost the disc for, can I reinstall it digitally?
Why is mind meld hard for T'pol in Star Trek: Enterprise?
Digits in an algebraic irrational number
Why publish a research paper when a blog post or a lecture slide can have more citation count than a journal paper?
CREATE ASSEMBLY System.DirectoryServices.AccountManagement.dll without enabling TRUSTWORTHY
CREATE DATABASE Permission denied in database 'master' errorWhat could trigger that a login created from a certificate is not visible in metadataOracle GoldenGate add trandata errorsGrant Admin to an Active Directory account in SQL Server.NET SQLCLR Assembly not working in SQL Server 2016 (Error msg 10314)Assembly is not authorized for PERMISSION_SET=UNSAFE when creating a CLR assemblyUPDATE STATISTICS and User-Defined Types (UDT) from AssembliesRestore SQL Server DB encrypted by EKM - where's the asymmetric key?Assembly deployment with permission UNSAFE or EXTERNAL_ACCESS using asymmetric keyError publishing SQLCLR C# Function using Visual Studio 2017
3
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.
*/
sql-server sql-server-2016 security active-directory sql-clr
edited 10 hours ago
Solomon Rutzky
48.8k581177
asked 11 hours ago
SpaceGhost440SpaceGhost440
11818
add a comment |
3
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.
*/
sql-server sql-server-2016 security active-directory sql-clr
edited 10 hours ago
Solomon Rutzky
48.8k581177
asked 11 hours ago
SpaceGhost440SpaceGhost440
11818
add a comment |
3
3
3
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.
*/
sql-server sql-server-2016 security active-directory sql-clr
edited 10 hours ago
Solomon Rutzky
48.8k581177
asked 11 hours ago
SpaceGhost440SpaceGhost440
11818
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.
*/
sql-server sql-server-2016 security active-directory sql-clr
sql-server sql-server-2016 security active-directory sql-clr
edited 10 hours ago
Solomon Rutzky
48.8k581177
asked 11 hours ago
SpaceGhost440SpaceGhost440
11818
edited 10 hours ago
Solomon Rutzky
48.8k581177
asked 11 hours ago
SpaceGhost440SpaceGhost440
11818
edited 10 hours ago
Solomon Rutzky
48.8k581177
edited 10 hours ago
Solomon Rutzky
48.8k581177
edited 10 hours ago
Solomon Rutzky
48.8k581177
48.8k581177
asked 11 hours ago
SpaceGhost440SpaceGhost440
11818
asked 11 hours ago
SpaceGhost440SpaceGhost440
11818
asked 11 hours ago
SpaceGhost440SpaceGhost440
11818
11818
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
7
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:
- 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 /
VARBINARYliteral), 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. - You don't need to grant both
UNSAFE ASSEMBLYandEXTERNAL ACCESS ASSEMBLYpermissions to the signature-based login. TheUNSAFE ASSEMBLYpermission assumes theEXTERNAL ACCESS ASSEMBLYpermission such that you can set assemblies to eitherPERMISSION_SETif you have theUNSAFE ASSEMBLYpermission. - 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.
answered 11 hours ago
Solomon RutzkySolomon Rutzky
48.8k581177
Solomon is there a post you know of demonstrating the certificate method?
– SpaceGhost440
10 hours ago
@SpaceGhost440 I am working on that post now ;-)
– Solomon Rutzky
10 hours ago
add a comment |
4
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
answered 10 hours ago
SpaceGhost440SpaceGhost440
11818
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f230993%2fcreate-assembly-system-directoryservices-accountmanagement-dll-without-enabling%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
7
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:
- 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 /
VARBINARYliteral), 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. - You don't need to grant both
UNSAFE ASSEMBLYandEXTERNAL ACCESS ASSEMBLYpermissions to the signature-based login. TheUNSAFE ASSEMBLYpermission assumes theEXTERNAL ACCESS ASSEMBLYpermission such that you can set assemblies to eitherPERMISSION_SETif you have theUNSAFE ASSEMBLYpermission. - 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.
answered 11 hours ago
Solomon RutzkySolomon Rutzky
48.8k581177
Solomon is there a post you know of demonstrating the certificate method?
– SpaceGhost440
10 hours ago
@SpaceGhost440 I am working on that post now ;-)
– Solomon Rutzky
10 hours ago
add a comment |
7
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:
- 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 /
VARBINARYliteral), 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. - You don't need to grant both
UNSAFE ASSEMBLYandEXTERNAL ACCESS ASSEMBLYpermissions to the signature-based login. TheUNSAFE ASSEMBLYpermission assumes theEXTERNAL ACCESS ASSEMBLYpermission such that you can set assemblies to eitherPERMISSION_SETif you have theUNSAFE ASSEMBLYpermission. - 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.
answered 11 hours ago
Solomon RutzkySolomon Rutzky
48.8k581177
Solomon is there a post you know of demonstrating the certificate method?
– SpaceGhost440
10 hours ago
@SpaceGhost440 I am working on that post now ;-)
– Solomon Rutzky
10 hours ago
add a comment |
7
7
7
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:
- 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 /
VARBINARYliteral), 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. - You don't need to grant both
UNSAFE ASSEMBLYandEXTERNAL ACCESS ASSEMBLYpermissions to the signature-based login. TheUNSAFE ASSEMBLYpermission assumes theEXTERNAL ACCESS ASSEMBLYpermission such that you can set assemblies to eitherPERMISSION_SETif you have theUNSAFE ASSEMBLYpermission. - 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.
answered 11 hours ago
Solomon RutzkySolomon Rutzky
48.8k581177
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:
- 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 /
VARBINARYliteral), 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. - You don't need to grant both
UNSAFE ASSEMBLYandEXTERNAL ACCESS ASSEMBLYpermissions to the signature-based login. TheUNSAFE ASSEMBLYpermission assumes theEXTERNAL ACCESS ASSEMBLYpermission such that you can set assemblies to eitherPERMISSION_SETif you have theUNSAFE ASSEMBLYpermission. - 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.
answered 11 hours ago
Solomon RutzkySolomon Rutzky
48.8k581177
edited 11 hours ago
answered 11 hours ago
Solomon RutzkySolomon Rutzky
48.8k581177
answered 11 hours ago
Solomon RutzkySolomon Rutzky
48.8k581177
answered 11 hours ago
Solomon RutzkySolomon Rutzky
48.8k581177
48.8k581177
Solomon is there a post you know of demonstrating the certificate method?
– SpaceGhost440
10 hours ago
@SpaceGhost440 I am working on that post now ;-)
– Solomon Rutzky
10 hours ago
add a comment |
Solomon is there a post you know of demonstrating the certificate method?
– SpaceGhost440
10 hours ago
@SpaceGhost440 I am working on that post now ;-)
– Solomon Rutzky
10 hours ago
Solomon is there a post you know of demonstrating the certificate method?
– SpaceGhost440
10 hours ago
Solomon is there a post you know of demonstrating the certificate method?
– SpaceGhost440
10 hours ago
@SpaceGhost440 I am working on that post now ;-)
– Solomon Rutzky
10 hours ago
@SpaceGhost440 I am working on that post now ;-)
– Solomon Rutzky
10 hours ago
add a comment |
4
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
answered 10 hours ago
SpaceGhost440SpaceGhost440
11818
add a comment |
4
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
answered 10 hours ago
SpaceGhost440SpaceGhost440
11818
add a comment |
4
4
4
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
answered 10 hours ago
SpaceGhost440SpaceGhost440
11818
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
answered 10 hours ago
SpaceGhost440SpaceGhost440
11818
answered 10 hours ago
SpaceGhost440SpaceGhost440
11818
answered 10 hours ago
SpaceGhost440SpaceGhost440
11818
answered 10 hours ago
SpaceGhost440SpaceGhost440
11818
11818
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f230993%2fcreate-assembly-system-directoryservices-accountmanagement-dll-without-enabling%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
