8

I have a certificate I would like to remove from the database.

If I issue the command

DROP CERTIFICATE <FooCert>

I get the error

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

According to Jason Strate, I should be able to find out what's signed by the certificate.

The following query returns 0 rows:

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'

I've also tried decoupling the entities per this SO question. https://stackoverflow.com/questions/52460/how-do-i-find-and-decouple-entities-from-a-certificate-when-upgrading-ms-sqlserv

How can I remove the dependencies on this cert so I can remove it?

5
  • Can you please try the query I posted here: Find signed procedures, functions, triggers, assemblies and by which certificates / asymmetric keys. Does that find anything? If so, I can post it, or just a link to it here. If not, I am guessing it is a Certificate-based Login / User and I can post a query for that. Jun 7 '16 at 20:12
  • 0 rows returned. Jun 7 '16 at 20:17
  • Do you have 'Transparent Data Encryption' (TDE) enabled on any of your database?
    – SQLPRODDBA
    Jun 8 '16 at 2:49
  • 1
    @SQLPRODDBA Thanks for mentioning TDE :-). I hadn't thought about that but have added a query to my answer to find that as well (and have tested it and confirmed that it works). Jun 8 '16 at 15:38
  • 1
    @srutzky Thanks for considering it! Your script is awesome!
    – SQLPRODDBA
    Jun 9 '16 at 2:30
6

To find items associated with Certificates and Asymmetric Keys, first try the query posted in this DBA.SE Answer:

Find signed procedures, functions, triggers, assemblies and by which certificates / asymmetric keys

If that does not return any objects, next try the following queries which look for:

  • Logins
  • Users
  • Service Broker Endpoints
  • Database Mirroring Endpoints
  • Symmetric Keys
  • Database Encryption Keys (used for TDE)

Please note that Logins are Server/Instance-level while everything else is Database-level. Also, Database Encryption Keys, while being at the Database-level, are reported in a DMV that returns data for all databases, and so does not change based on the "current" Database.

-- 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];
8
  • Thank you for providing the queries. Most queries returned 0 rows.The Symmetric Key Encryption returned one row which appears to be my certificate. However, I'm still unclear on what is signed or encrypted by this certificate and how to remove it. Jun 8 '16 at 15:03
  • 1
    @GeoffDawdy Well, getting farther :-). It should be the Symmetric Key itself. I can update the query to JOIN in that table as well. Hold on. Jun 8 '16 at 15:09
  • @GeoffDawdy I have updated that one query. Please try the new version of it. Of course, if one or more Symmetric Keys is in use, that is another issue ;-). And be sure to have a backup file of the Certificate and its Private Key before deleting :-) Jun 8 '16 at 15:21
  • Yes, the same certificate showed up. So does this mean that certificate is signed by itself? How can I go about deleting it if I get the error saying it cannot be dropped. Jun 8 '16 at 15:23
  • 1
    I just figured it out. My key that was signed by the certificate needed to be dropped. Once I dropped the symmetric key, I was able to drop the certificate as well. Thanks for all your help! Jun 8 '16 at 15:29
1

Encountered a similar issue, for me this query helped me find the signed object:

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'

Source

Then I just used the following command as an example where dbo.sp_name is the signed object and STOREDPROCEDURESIGNINGCERT is the signing certificate.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.