Início / dba / Perguntas / 140657
Accepted
Geoff Dawdy
Asked: 2016-06-08 11:35:02 +0800 CST

Erro: "O certificado não pode ser descartado porque uma ou mais entidades estão assinadas ou criptografadas usando-o."

  • 772

Tenho um certificado que gostaria de remover do banco de dados.

Se eu emitir o comando

DROP CERTIFICATE <FooCert>

eu recebo o erro

The certificate cannot be dropped because one or more entities are either signed or encrypted using it

De acordo com Jason Strate, devo conseguir descobrir o que está assinado no certificado.

A consulta a seguir retorna 0 linhas:

SELECT OBJECT_SCHEMA_NAME(co.major_id) + '.' + OBJECT_NAME(co.major_id)
FROM sys.certificates c 
INNER JOIN sys.crypt_properties co ON c.thumbprint = co.thumbprint
WHERE co.crypt_type_desc = 'SIGNATURE BY CERTIFICATE' 
AND c.name = 'FooCert'

Eu também tentei desacoplar as entidades por esta questão SO. https://stackoverflow.com/questions/52460/how-do-i-find-and-decouple-entities-from-a-certificate-when-upgrading-ms-sqlserv

Como posso remover as dependências deste certificado para removê-lo?

sql-server sql-server-2012

2 respostas

  1. Best Answer

    Para encontrar itens associados a Certificados e Chaves Assimétricas, primeiro tente a consulta postada nesta Resposta DBA.SE:

    Encontre procedimentos, funções, gatilhos, assemblies assinados e por quais certificados/chaves assimétricas

    Se isso não retornar nenhum objeto, tente as seguintes consultas que procuram:

    • logins
    • Usuários
    • Pontos finais do Service Broker
    • Endpoints de espelhamento de banco de dados
    • Chaves Simétricas
    • Chaves de criptografia de banco de dados (usadas para TDE)

    Observe que os logins são de nível de servidor/instância, enquanto todo o resto é de nível de banco de dados. Além disso, as chaves de criptografia do banco de dados, embora estejam no nível do banco de dados, são relatadas em um DMV que retorna dados para todos os bancos de dados e, portanto, não muda com base no banco de dados "atual".

    -- Server / Instance Logins (results not sensitive to local / current Database)
;WITH certs_n_keys AS
(
  SELECT 'Certifcate' AS [Type], crts.name, crts.certificate_id AS [cert_or_asymkey_id],
         crts.principal_id, crts.pvt_key_encryption_type_desc, crts.[sid],
         crts.thumbprint
  FROM   [master].sys.certificates crts
  UNION ALL
  SELECT 'Asymmetric Key' AS [Type], asym.name, asym.asymmetric_key_id AS
         [cert_or_asymkey_id], asym.principal_id, asym.pvt_key_encryption_type_desc,
         asym.[sid], asym.thumbprint
  FROM   [master].sys.asymmetric_keys asym
)
SELECT cnk.*, '---' AS [---],
       sp.[name] AS [PrincipalName], sp.principal_id, sp.type_desc,
       sp.create_date, sp.modify_date
FROM   certs_n_keys cnk
INNER JOIN sys.server_principals sp
        ON sp.[sid] = cnk.[sid];


-- Database Users
;WITH certs_n_keys AS
(
  SELECT 'Certifcate' AS [Type], crts.name, crts.certificate_id AS [cert_or_asymkey_id],
         crts.principal_id, crts.pvt_key_encryption_type_desc, crts.[sid],
         crts.thumbprint
  FROM   sys.certificates crts
  UNION ALL
  SELECT 'Asymmetric Key' AS [Type], asym.name, asym.asymmetric_key_id AS
         [cert_or_asymkey_id], asym.principal_id, asym.pvt_key_encryption_type_desc,
         asym.[sid], asym.thumbprint
  FROM   sys.asymmetric_keys asym
)
SELECT cnk.*, '---' AS [---],
       dp.[name] AS [PrincipalName], dp.principal_id, dp.type_desc,
       dp.create_date, dp.modify_date
FROM   certs_n_keys cnk
INNER JOIN sys.database_principals dp
        ON dp.[sid] = cnk.[sid];


-- Service Broker Endpoints
SELECT crts.name, crts.certificate_id, crts.principal_id,
       crts.pvt_key_encryption_type_desc, crts.[sid], crts.thumbprint, '---' AS [---],
       endpts.*
FROM   sys.certificates crts
INNER JOIN sys.service_broker_endpoints endpts
        ON endpts.certificate_id = crts.certificate_id;


-- Database Mirroring Endpoints
SELECT crts.name, crts.certificate_id, crts.principal_id,
       crts.pvt_key_encryption_type_desc, crts.[sid], crts.thumbprint, '---' AS [---],
       endpts.*
FROM   sys.certificates crts
INNER JOIN sys.database_mirroring_endpoints endpts
        ON endpts.certificate_id = crts.certificate_id;


-- Symmetric Keys (scroll results to the right to see Key name)
SELECT crts.name, crts.certificate_id, crts.principal_id,
       crts.pvt_key_encryption_type_desc, crts.[sid], crts.thumbprint, '---' AS [---],
       ncrptns.*, '---' AS [---], symkys.*
FROM   sys.certificates crts
INNER JOIN sys.key_encryptions ncrptns
        ON ncrptns.[thumbprint] = crts.[thumbprint]
INNER JOIN sys.symmetric_keys symkys
        ON symkys.[symmetric_key_id] = ncrptns.[key_id];


-- Database Encryption Keys (for TDE; results not sensitive to local / current Database)
SELECT crts.name, crts.certificate_id, crts.principal_id,
       crts.pvt_key_encryption_type_desc, crts.[sid], crts.thumbprint, '---' AS [---],
       DB_NAME(dbkeys.[database_id]) AS [DatabaseName], dbkeys.*
FROM   [master].sys.certificates crts
INNER JOIN sys.dm_database_encryption_keys dbkeys
        ON dbkeys.[encryptor_thumbprint] = crts.[thumbprint];
    • 6

  2. Encontrei um problema semelhante, para mim esta consulta me ajudou a encontrar o objeto assinado:

    SELECT OBJECT_SCHEMA_NAME(co.major_id) + '.' + OBJECT_NAME(co.major_id), c.name 
FROM sys.certificates c 
INNER JOIN sys.crypt_properties co ON c.thumbprint = co.thumbprint
WHERE co.crypt_type_desc = 'SIGNATURE BY CERTIFICATE'

    Fonte

    Então usei apenas o seguinte comando como exemplo onde dbo.sp_nameestá o objeto assinado e STOREDPROCEDURESIGNINGCERTestá o certificado de assinatura.

    DROP SIGNATURE FROM OBJECT::dbo.sp_name BY CERTIFICATE STOREDPROCEDURESIGNINGCERT
    • 1

relate perguntas