Início / dba / Perguntas / 230993
Accepted
SpaceGhost440
Asked: 2019-03-01 11:29:17 +0800 CST

CRIAR ASSEMBLY System.DirectoryServices.AccountManagement.dll sem habilitar TRUSTWORTHY

  • 772

Primeiro, isso é para o SQL Server 2016. Se eu estivesse em 2017+, estaria usando o sp_add_trusted_assembly. Só queria esclarecer isso antes de fazer a pergunta.

Como você registra o assembly System.DirectoryServices.AccountManagement.dll sem usar TRUSTWORTHY ON? Não consigo fazê-lo funcionar usando uma chave assimétrica gerada a partir de System.DirectoryServices.dll . A dll AccountManagement é assinada de forma diferente de System.DirectoryServices.dll .

Eu até tentei criar uma chave assimétrica separada de System.DirectoryServices.AccountManagement.dll , mas isso resulta em:

Msg 15468, Level 16, State 7, Line XXXXX
Ocorreu um erro durante a geração da chave assimétrica.

Aqui está um script de teste que escrevi para tentar criar este 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.
*/
sql-server sql-server-2016

2 respostas

  1. Best Answer

    Não, uma chave assimétrica não funcionará, provavelmente porque os assemblies de nomenclatura forte foram alterados no .NET 4.5 ou em algum lugar por aí, para agora ser uma nomenclatura forte aprimorada, que não é suportada pelo host CLR do SQL Server (não sem renunciar a ele, o que não funcionará aqui).

    A chave é usar certificados. Crie um certificado na masterDLL, crie o logon desse certificado e, finalmente, conceda a UNSAFE ASSEMBLYpermissão para esse logon.

    Deve ser simples assim (assumindo, é claro, que a DLL é MSIL puro e não misto, porque apenas assemblies MSIL puros podem ser carregados no SQL Server).

    Também:

    1. Você não deve adicionar/soltar mais do que o assembly principal, supondo que eles sejam referenciados pelo main. Ao adicionar assemblies via DLL (em oposição a bytes hexadecimais/ VARBINARYliterais), o SQL Server pegará todos os assemblies referenciados que estiverem na mesma pasta. Ele definirá todos esses assemblies adicionados automaticamente para "visible = 0". Os assemblies definidos como "visible = 0" referenciados por um assembly principal são automaticamente descartados se o assembly principal for descartado.
    2. Você não precisa conceder permissões UNSAFE ASSEMBLYe EXTERNAL ACCESS ASSEMBLYpara o logon baseado em assinatura. A UNSAFE ASSEMBLYpermissão assume a EXTERNAL ACCESS ASSEMBLYpermissão de forma que você possa definir assemblies para qualquer um deles PERMISSION_SETse tiver a UNSAFE ASSEMBLYpermissão.
    3. Se estiver usando o SQL Server 2017 ou mais recente, não há necessidade de usar o recurso "assemblies confiáveis". Por favor, veja meu post aqui sobre por que e o que deve ser feito em vez disso: SQLCLR vs. SQL Server 2017, Parte 1: "CLR strict security" - The Problem . É claro que, neste caso específico, é um ponto discutível, pois a abordagem adequada é usar o método Certificate, conforme descrito acima.

    Observe que em outras respostas a perguntas semelhantes, respondidas por mim ou por outras pessoas, a resposta sempre foi que TRUSTWORTHYtinha que ser ativada. Isso ocorreu devido a informações incorretas fornecidas pela documentação da Microsoft que eu não percebi até mais recentemente. Estou trabalhando para corrigir minhas respostas anteriores e essa documentação.

    • 7

  2. Esta resposta é baseada na sugestão de Salomão acima. O crédito é todo dele. Eu reescrevi meu script como ele sugeriu e agora funciona!!! TI SALOMÃO! Estou apenas postando como exemplo para outros que vierem depois de mim.

    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:\Windows\Microsoft.NET\Framework\v4.0.30319\System.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:\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
    • 4

relate perguntas