SSISDB Project Deployment expired Certificates

John Couch 21

We have hundreds of project deployment certificates that are expired, but I tried to find what is using them and am unable to. I tried dropping them, but it says they are in use. How do you figure out which project is using the certificates?

Olaf Helper 45,106 Reputation points

2024-05-31T19:02:20.0233333+00:00

What for certificates in relation to SSIS projects/packages?
John Couch 21 Reputation points

2024-06-03T17:51:17.77+00:00

Thats what I am trying to figure out. Looks like they are tied to Project parameters, project packages...etc. The keys I assumed were here: ssisdb.internal.catalog_encryption_keys and should be tied back to asymetric keys in master but I do not see how they are linked outside of key name (which is weird) because the key values (thumbprimts) dont match up.

1 answer

ZoeHui-MSFT 37,671 Reputation points

2024-05-31T06:45:16.9833333+00:00
Hi @John Couch,

You may use below code to check the certificate.

SELECT * FROM sys.certificates

All certificates have an expiry date, but for most uses of certificates in SQL Server, the expiry is not checked.

If the certificates do not affect your business and do not show errors, you may ignore it.

Regards，

Zoe Hui

If the answer is helpful, please click "Accept Answer" and upvote it.
Please sign in to rate this answer.
John Couch 21 Reputation points

2024-05-31T13:39:14.0533333+00:00

Thank you, but I do not have an issue figuring out what certificates there are, I have an issue figuring out in SSISDB what objects (projects) are using the certificate. Every project deployment creates a new certificate. Nothing shows the link between the certificate and the project.

John Couch 21 Reputation points

2024-05-31T13:43:37.97+00:00

Thanks for the answer, but I do not have a problem finding the certificates. I have a problem with showing which certificate is tied to which project in SSISDB. Every project deployment creates a new certificate with nothing showing what objects are encrypted with which certificate.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

SSISDB Project Deployment expired Certificates

1 answer

Your answer