New CREATE CERTIFICATE syntax in 2012

While setting up a demo for an upcoming post, I stumbled across a new bit of hotness in CREATE CERTIFICATE. Specifically, you can specify the bits of the certificate right in the T-SQL (much like you can with assemblies). Why is this awesome? There are a couple of scenarios where you need to create the same certificate in multiple places. Off the top of my head:

• 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:

USE [AdventureWorks2012];
CREATE CERTIFICATE [MyCert]
ENCRYPTION BY PASSWORD = 'Do not tell anyone!'
WITH SUBJECT = 'MyCert'

So now, I can get the hexadecimal representation of the certificate with the following:

SELECT 
   certencoded(CERT_ID('MyCert')), 
   certprivatekey(CERT_ID('MyCert'), 'foobar', 'Do not tell anyone!')

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:

USE [tempdb];
CREATE CERTIFICATE [MyCert]
FROM BINARY = 0x308201B730820120A0030201020210271EDD027FAF8FA642154AD85D9DBFCD300D06092A864886F70D0101050500301A311830160603550403130F436F64655369676E696E6743657274301E170D3132313030393139303432305A170D3133313030393139303432305A301A311830160603550403130F436F64655369676E696E674365727430819F300D06092A864886F70D010101050003818D0030818902818100CDA420FE5DAAF66D4624AFA7D88F581EC4AED1A323C170DDB383FE94DF96F54F4FBF71C8AECEA6F5B103CDC9EB272D68E557CF1309FB1F83ACFB3C56D17E565957DCD078B563D229AC5B6C96D2CC1728E224A29BE1BC9BAC4ECA4F2AF146BD2BD92196F2E6CD12F70B5596E8F5D0BBA23A5D1ED5AF0704CFD5DB81E9007B353D0203010001300D06092A864886F70D01010505000381810034A28ABE6BD5EF6CE784CC0C66004BAF019F44C160D1284B181309ECB149DD65848F7C66D415ABFC715C3EB13E8AC151AA6A2A779364F34B51ADEF83DA6D4C6DFE17B6A727CC778FEB9C390C5843647DB90D20A4BB65E01EFA6D44A728B347EB660130FB28A33F59E3D54534C8E2CFD696FEB27E87A9784FDE855A2C79B5D75C 
WITH PRIVATE KEY (
    BINARY = 0x1EF1B5B000000000010000000100000010000000540200005F3C64C3C588A9FCA9DDC5AF64CE4F370702000000A400005C6E54FDDB0BD2A46AC04FF812855EB240128E51B902E25A0F338F3010D9C3C7A592DB881999260100B90E59CA570A94945563283DDEF19C747E5D7ABEFB2653CEC60C7E9601D7AD0C07EFFECBFF8BEBB648183EA0132D5317CF27268348DD4D15003AB1A480C29F0CFB03B28DD92C911AE5774E6799CEAE0FEF4A474C04A049638BD08952026CA8FEBB3CD5ABDD1C686E4CC6CE21CCB729CC22A9F7B0E3A3AE0CE7956099C86278B60C436B89AA3581DC683842F2DBCEFFAFBB5519397777978879CD14B74444AF8C1B0E45AC08959ECFDCE59195DE3482A72A14F1AFB3670A2B8349BC17D75FF7B3A017A1A680BC520500E7EA44D70A24A1081A94240C58CC3FB3E06AAF0F24F556396EA4E6F7AE4CB5F85A41D51E2C641755AAE70514BE670688A52667F4662EFD817BDEA490000A1EB5997427355CB067433E737D5226D46492CFFD2B92C38D3A5EC5BC4E6478E453AE4A364AF6513E90EB2ECA08B699C4566C4A866F610CEEB15965CA1A35359B87683CE61A0592CBAC5446B241D3056679B012A864D1542F0C0F066A072B5A31AC6B0FD12DB4D458B00FC22F29B5AA7C4BEA64416A670E1E907D37AE6FFC7A1D043B2BABAA025261347FAC76FBAD658EC4880993AE67D45D8E960468D55CB47B048CAEF4030DFC29FC6537AD8A203B8961BDD3E6FCBA9A7E1DE2FD949F265EB7CAB55374472CC1C69B47C8D71124BEE22B01E45F085F20EFA6F5BEA689612F32E2FC2D60B51664AB75B53538A71209AC9E67603FEDC56EAFC6F9E1CF2550AF5655E28DD7C4D6208D38AC665140CC617D5DFC13B1502D137CB6A72ACF, 
    DECRYPTION BY PASSWORD = 'foobar',
    ENCRYPTION BY PASSWORD = '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.