- Signed modules that need a principal both at the server and database level
- Service broker
- Encryption by certificate in multiple places
Until now, you would create the certificate in one place, back it up, and then "restore" the certificate in all of the other places you needed it. Now, you can do it all in T-SQL which I can see saving time and effort. Sometimes, it's the little things. So, how do you go about this? Let's say I create a certificate in one of my user databases:
So now, I can get the hexadecimal representation of the certificate with the following:
USE [AdventureWorks2012]; CREATE CERTIFICATE [MyCert] ENCRYPTION BY PASSWORD = 'Do not tell anyone!' WITH SUBJECT = 'MyCert'
This will be two columns, one is the public key portion of the certificate and the other the private key portion. Now I can create that same certificate anywhere with the following:
SELECT certencoded(CERT_ID('MyCert')), certprivatekey(CERT_ID('MyCert'), 'foobar', 'Do not tell anyone!')
If you wanted to get fancy, I'm sure that you could do some dynamic SQL and not have to look at the ugly hex dump. But there you have it.
USE [tempdb]; CREATE CERTIFICATE [MyCert] FROM BINARY = <hex dump from above> WITH PRIVATE KEY ( BINARY = <private key hex dump from above> DECRYPTION BY PASSWORD = 'foobar', ENCRYPTION BY PASSWORD = 'Do not tell anyone!' )