Início / dba / Perguntas / 300601
Accepted
Kris
Asked: 2021-10-05 09:41:30 +0800 CST

Carregar assembly de um certificado no SQL Server

  • 772

Recebemos uma montagem de fornecedor externo como

CREATE ASSEMBLY MYCALC_DLL
AUTHORIZATION dbo
FROM 0x42A728....<300,000 binary values> 
WITH PERMISSION_SET = UNSAFE
GO

E esta DLL é chamada de um SP.

Não gostaríamos de definir TRUSTWORTHY ON para o banco de dados carregar esse assembly no banco de dados do SQL Server e estávamos explorando opções. Encontrei esses links https://nielsberglund.com/2017/07/01/sqlclr-and-certificates/
https://sqlquantumleap.com/2017/08/09/sqlclr-vs-sql-server-2017-part- 2-clr-strict-security-solution-1/
https://sqlquantumleap.com/2017/08/16/sqlclr-vs-sql-server-2017-part-3-clr-strict-security-solution-2/

No entanto, esses links usam ferramentas como MAKECERT (substituído por new-selfsignedcertificate), pvk2pfx.exe e signtool.exe não existem em nossos servidores win2016.

A DLL também seria chamada com essa abordagem

Habilite TRUSTWORTHY no DB -> Instale o assembly no DB AS WITH PERMISSION_SET = UNSAFE-> Desabilite TRUSTWORTHY no DB

Suponho que seja uma pergunta estúpida, no entanto, estou carregando o assembly como UNSAFE, então qual é o ponto de desativar o TRUSTWORTHY? Me corrija...

Gostaríamos de usar certificados. Como prosseguir? E esses DB's serão configurados em breve para AlwaysOn, haveria alguma complicação? Obrigado

Agora o desenvolvedor deu esses arquivos Arquivos de montagem

----- Funcionou assim. Este é o método correto?------

<Esta é a primeira vez que trabalho com VS>

  1. Baixado Visual Studio Community Edition 2019
  2. Do conjunto de arquivos acima, abriu o arquivo de projeto
  3. Construir projeto CALC
  4. Ele criou um arquivo .dll
  5. Nas propriedades do projeto, a aba de assinatura Assinatura criou um novo arquivo de chave de nome forte **Não marquei a caixa de seleção "Proteger meu arquivo de chave com senha" E escolheu sha256RSA como algoritmo de assinatura Salve o projeto e Build, cria um .snk Arquivo
  6. Copie o arquivo DLL e snk para a caixa SQL Server
  7. Execute o script abaixo
USE [Master]
GO

CREATE ASYMMETRIC KEY CLR_SP_Key
FROM EXECUTABLE FILE = 'H:\CLR_SP\CALC.dll'
GO

CREATE LOGIN CLR_SP_Login FROM ASYMMETRIC KEY CLR_SP_Key
GO

GRANT UNSAFE ASSEMBLY TO CLR_SP_Login
GO

USE [target_database]
GO
CREATE USER CLR_SP_Login FOR LOGIN CLR_SP_Login
GO

CREATE ASSEMBLY DBCALC_DLL FROM 'H:\CLR_SP\CALC.dll'
WITH PERMISSION_SET=UNSAFE
GO
CREATE function dbo.Proc_CLR_SP
(
@name as nvarchar(200) ,
@name2 as nvarchar(200)
)
RETURNS nvarchar(200)
AS EXTERNAL NAME DBCALC_DLL
sql-server sql-server-2016

1 respostas

  1. Best Answer

    Dado que você recebeu um assembly com script em vez de uma DLL (e o desenvolvedor não teve a gentileza de assiná-lo, que sabemos no momento), não será super fácil assinar no nível do sistema operacional ( o método preferido), pois primeiro você precisaria criar um arquivo binário real a partir desse assembly (há maneiras, mas não incorporadas ao Windows).

    Portanto, nesse caso, você pode fazer a próxima melhor coisa que é assinar o assembly no SQL Server. Os passos seriam:

    1. Habilite TRUSTWORTHYpara o banco de dados no qual o assembly será carregado (isso é temporário!)

    2. Execute o script para carregar o assembly

    3. DesativarTRUSTWORTHY

    4. Crie um certificado no banco de dados em que o assembly foi carregado (use CREATE CERTIFICATE)

      CREATE CERTIFICATE [{cert_name}]
   ENCRYPTION BY PASSWORD = '{password}'
   WITH SUBJECT = 'Allow assembly to be loaded',
   EXPIRY_DATE = '20991031';

    5. Assine a assembléia usandoADD SIGNATURE

      ADD SIGNATURE TO ASSEMBLY::[MYCALC_DLL]
   BY CERTIFICATE [{cert_name}]
   WITH PASSWORD = '{password}';

    6. Copie o certificado para [master](somente chave pública)

    7. Crie um login a partir desse certificado

    8. conceda a UNSAFE ASSEMBLYpermissão a esse login ( o código a seguir lida com essas últimas 3 etapas )

      DECLARE @Cert NVARCHAR(MAX);
SET @Cert = CONVERT(NVARCHAR(MAX), CERTENCODED(CERT_ID(N'ObjectSigner')), 1);

EXEC (N'USE [master]
CREATE CERTIFICATE [{cert_name}]
FROM BINARY = ' + @Cert + N';

CREATE LOGIN [{cert_name}]
   FROM CERTIFICATE [{cert_name}];

GRANT UNSAFE ASSEMBLY TO [{cert_name}];');

    Agora tudo deve ficar bem.

    NOTAS

    1. Você só precisaria de makecert.exe , pvk2pfk.exe e signtool.exe se tivesse o arquivo binário DLL real para assinar no nível do sistema operacional. Um desses já deve existir na sua máquina (mesmo que não esteja no PATH), e acredito que seja signtool .

    2. Você pode obter os outros dois utilitários do SDK do Windows (em breve publicarei um post com links para os locais e quais pacotes específicos obter para não exigir a obtenção de todos).

    3. TRUSTWORTHYe UNSAFEsão duas coisas muito diferentes. A habilitação TRUSTWORTHYé uma abordagem ampla, como usar a representação (ou seja EXECUTE AS, ), pois não é específica para o que está concedendo.

    4. !! Agora, é possível que o desenvolvedor deste assembly tenha, de fato, um nome forte e/ou assinado. Depois de executar as etapas mencionadas acima para carregar o assembly, você poderá verificar se ele foi assinado tentando criar uma chave assimétrica e um certificado a partir dele. Se qualquer um funcionar, você poderá ter mais opções para carregar esse assembly novamente, especialmente se ele tiver sido assinado com um certificado (no futuro, se você puder solicitar que o desenvolvedor o assine, isso seria o ideal). Você pode tentar através de:

      CREATE ASYMMETRIC KEY [_test_key] FROM ASSEMBLY [MYCALC_DLL];

CREATE CERTIFICATE [_test_cert] FROM ASSEMBLY [MYCALC_DLL];

    RE: NOVO CÓDIGO ADICIONADO À PERGUNTA

    Não tenho certeza se você economizou tempo ou esforço com o desenvolvedor enviando todos os arquivos do projeto (enviar o arquivo binário DLL com nome forte teria sido muito mais fácil), mas com base no que você adicionou à pergunta:

    1. Ao abrir no Visual Studio, eu abriria o arquivo de solução (ou seja, .sln ) em vez do arquivo de projeto (ou seja, .*proj ).
    2. Interessante que é um arquivo .csproj em vez de um arquivo .sqlproj , mas se funcionar, então funciona.
    3. Na guia "Assinatura" no Visual Studio: nunca tentei não proteger o arquivo SNK com uma senha, mas suponho que ainda deva funcionar, pois protege apenas a chave privada, mas apenas a chave pública é necessária para importar para o SQL Servidor.
    4. Você não precisa copiar o arquivo .snk para a máquina do SQL Server, pois ele não será usado. A chave pública, que pode ser carregada no SQL Server a partir do arquivo .snk , também está dentro da própria DLL fortemente nomeada/assinada, então você só precisa da DLL (da qual você já está criando a chave assimétrica, com base no script mostrado na pergunta).
    5. Não há necessidade de criar o usuário dentro [target_database], pois ele não será usado ou não precisa ser usado. Atualmente, ele não está sendo usado para que essa etapa possa ser ignorada.
    6. Criando a montagem: eu começaria com PERMISSION_SET = SAFEpara ver se isso funciona. Se sim, então fique com isso. Se você receber um erro, ao criar o assembly, tente UNSAFE. Se SAFEfunciona para carregar o assembly, mas a execução do proc recebe um erro de permissão (o sistema de arquivos provavelmente recebe os nomes de alguns dos arquivos de código-fonte), altere o assembly para ter PERMISSION_SET = EXTERNAL_ACCESSem vez de SAFE.
    7. Não tenho certeza se a CREATE PROCEDUREdeclaração está completa ou redigida, mas a AS EXTERNAL NAMEparte está incompleta. Atualmente, você só tem o nome do assembly, DBCALC_DLL, quando também precisa do nome da classe em que o método que está chamando está e, em seguida, o nome do método que está chamando (por exemplo DBCALC_DLL.class.method, ).
    8. Só por curiosidade, por que isso se chama "PROC", mesmo com "_SP" no nome, mas é uma função? ?
    • 2

relate perguntas