Thursday, September 10, 2015

Creating a certificate from binary - redux

Back in a simpler time, I talked about new syntax introduced in SQL 2012 that would allow you to create a certificate from a varbinary string. In that post, I mentioned that some enterprising person might be able to script it out so you didn't have to go through the copy/paste dance. Well… I've been doing a lot of creating certificates for a service broker implementation. So, I figured it was time for that enterprising individual to be me. So here goes!

SELECT 'CREATE CERTIFICATE ' + QUOTENAME([name]) 
    + ' AUTHORIZATION ' + USER_NAME([c].[principal_id]) 
    + ' FROM BINARY = ' + CONVERT(VARCHAR(MAX), CERTENCODED([c].[certificate_id]), 1)
    + ' WITH PRIVATE KEY (BINARY = ' 
    + CONVERT(VARCHAR(MAX), CERTPRIVATEKEY([c].[certificate_id], 'f00bar!23'), 1)
    + ', DECRYPTION BY PASSWORD = ''f00bar!23'')'
FROM [sys].[certificates] AS [c]
WHERE [name] = ''
This assumes (as is the case for the certificates I'm dealing with) that the the private key is encrypted by the master key both at the source and the destination. If either of those is different in your case, you may need to add an "encryption by password" clause or an extra argument to the CERTPRIVATEKEY function to provide a decryption password. But there you (and future me) have it.

No comments:

Post a Comment