{"id":2384,"date":"2014-05-05T14:00:59","date_gmt":"2014-05-05T18:00:59","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=2384"},"modified":"2014-11-13T12:27:30","modified_gmt":"2014-11-13T17:27:30","slug":"restore-certificate","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/2384\/restore-certificate\/","title":{"rendered":"How to Restore a Certificate in SQL Server"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nA few days ago, we talked about that backing up your certificates<\/a> is an important part of your SQL Server recovery strategy. A recovery strategy cannot be complete without a way to execute a restore. Yet, SQL Server does not have a RESTORE CERTIFICATE<\/span> statement. So, how do you restore a certificate from a backup taken with BACKUP CERTIFICATE<\/span>?\n<\/p>\n

Restoring your Certificates<\/h3>\n

\nThe BACKUP CERTIFICATE<\/span> statement actually creates the backup file in a well-defined format that is part of the X.509 standard, the DER<\/a> format. That format however is incidentally the same format that the CREATE CERTIFICATE<\/span> statement can take as input. To demonstrate, let us first create a simple certificate and then take a backup:\n<\/p>\n

\n[sql]\nCREATE CERTIFICATE ACertificate WITH SUBJECT='A Simple Certificate';
\nBACKUP CERTIFICATE ACertificate TO FILE = 'C:\\temp\\ACertificate.cer'
\n WITH PRIVATE KEY(ENCRYPTION BY PASSWORD='%%%%%%%%', FILE='C:\\temp\\ACertificate.pvk');
\n[\/sql]\n<\/div>\n

\nTo show that the backup file is indeed in a standard format, you can for example use the openssl<\/a> command line tool:\n<\/p>\n

\n[sourcecode]\nopenssl.exe x509 -in C:\\temp\\ACertificate.cer -inform der -text -noout
\n[\/sourcecode]\n<\/div>\n

\nThe -inform der<\/span> parameter specifies that the certificate is provided in DER format. In the below screenshot I used xp_cmdshell<\/span> to execute the above statement.\n<\/p>\n

\n\"BACKUP<\/a>\n<\/p>\n

\nNow we can drop the existing certificate in the database and then use the CREATE CERTIFICATE<\/span> statement to restore it:\n<\/p>\n

\n[sql]\nDROP CERTIFICATE ACertificate;
\nCREATE CERTIFICATE ACertificate FROM FILE ='C:\\temp\\ACertificate.cer'
\n WITH PRIVATE KEY(FILE='C:\\temp\\ACertificate.pvk', DECRYPTION BY PASSWORD='%%%%%%%%');
\n[\/sql]\n<\/div>\n

\nThe CREATE CERTIFICATE<\/span> statement has the usual two parts, one for the actual certificate including the public key, and one for the private key. The FROM FILE<\/span> clause specifies that the certificate should be loaded from the referenced file instead of being newly created. The WITH PRIVATE KEY<\/span> clause specifies the file containing the private key. It also specifies that password that was used to protect that file during the backup.\n<\/p>\n

\nThe CREATE CERTIFICATE<\/span> statement itself does not produce any output, but we can use the sys.certificates<\/span><\/a> catalog view to see that the restore was successful:\n<\/p>\n

\n\"CREATE<\/a>\n<\/p>\n

Restoring Password Protected Certificates<\/h3>\n

\nThe entire WITH PRIVATE KEY<\/span> clause is optional. If you leave it out, the certificate is created without the private key. If you provide it as shown above, the private key of the restored certificate is going to be protected by the database master key. This is the case even if the certificate's private key was protected by a password at the time the backup was taken. That password is not actually stored in the backup file. If you need to restore a password-protected certificate, you need to specify the password during the restore process using the ENCRYPTION BY PASSWORD<\/span> clause:\n<\/p>\n

\n[sql]\nCREATE CERTIFICATE ACertificate FROM FILE ='C:\\temp\\ACertificate.cer'
\n WITH PRIVATE KEY(FILE='C:\\temp\\ACertificate.pvk',
\n DECRYPTION BY PASSWORD='%%%%%%%%',
\n ENCRYPTION BY PASSWORD='********');
\n[\/sql]\n<\/div>\n

\nFor this reason, you need to make sure, when backing up a password-protected certificate, to not only store the password used to protect the backup file in a safe place, but also the password that people expect the certificate to be protected with. Or, even better, switch the certificate to be protected by the database master key instead.\n<\/p>\n

Other Certificate Sources<\/h3>\n

\nThe CREATE CERTIFICATE<\/span> cannot only read DER formatted files that were created by the BACKUP CERTIFICATE<\/span> statement. It can in fact create a certificate in SQL Server based on any DER formatted file. Such a file could for example be created using Microsoft's MakeCert<\/span> tool.\n<\/p>\n

Summary<\/h3>\n

\nBACKUP CERTIFICATE<\/span> writes a certificate file in the DER format. That CREATE CERTIFICATE<\/span> statement can be used to restore such a backup. It can also be used to create a certificate from any other source, as long as it is provided in DER format.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

Do you know how to restore certificates in SQL Server? Read on to find out how, even though there is no RESTORE CERTIFICATE statement.<\/p>\n

[more…]<\/a><\/p>\n","protected":false},"author":3,"featured_media":2386,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[32,5,34],"tags":[70,49,273,178,38,15],"class_list":["post-2384","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-cryptography","category-general","category-security","tag-backup-2","tag-certificates","tag-cryptography","tag-restore","tag-security-2","tag-sql-server"],"acf":[],"yoast_head":"\nHow to Restore a Certificate in SQL Server - sqlity.net<\/title>\n<meta name=\"description\" content=\"Do you know how to restore certificates in SQL Server? Read on to find out how, even though there is no RESTORE CERTIFICATE statement.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/sqlity.net\/en\/2384\/restore-certificate\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to Restore a Certificate in SQL Server - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"Do you know how to restore certificates in SQL Server? Read on to find out how, even though there is no RESTORE CERTIFICATE statement.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/2384\/restore-certificate\/\" \/>\n<meta property=\"og:site_name\" content=\"sqlity.net\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/sqlity.net\" \/>\n<meta property=\"article:published_time\" content=\"2014-05-05T18:00:59+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T17:27:30+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/05\/create_certificate_from_file.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"816\" \/>\n\t<meta property=\"og:image:height\" content=\"483\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Sebastian Meine\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@sqlity\" \/>\n<meta name=\"twitter:site\" content=\"@sqlity\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Sebastian Meine\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2384\\\/restore-certificate\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2384\\\/restore-certificate\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"How to Restore a Certificate in SQL Server\",\"datePublished\":\"2014-05-05T18:00:59+00:00\",\"dateModified\":\"2014-11-13T17:27:30+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2384\\\/restore-certificate\\\/\"},\"wordCount\":686,\"commentCount\":1,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2384\\\/restore-certificate\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/05\\\/create_certificate_from_file.jpg\",\"keywords\":[\"backup\",\"Certificates\",\"Cryptography\",\"restore\",\"security\",\"SQL Server\"],\"articleSection\":[\"Cryptography\",\"General\",\"Security\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2384\\\/restore-certificate\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2384\\\/restore-certificate\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2384\\\/restore-certificate\\\/\",\"name\":\"How to Restore a Certificate in SQL Server - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2384\\\/restore-certificate\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2384\\\/restore-certificate\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/05\\\/create_certificate_from_file.jpg\",\"datePublished\":\"2014-05-05T18:00:59+00:00\",\"dateModified\":\"2014-11-13T17:27:30+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"Do you know how to restore certificates in SQL Server? Read on to find out how, even though there is no RESTORE CERTIFICATE statement.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2384\\\/restore-certificate\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2384\\\/restore-certificate\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2384\\\/restore-certificate\\\/#primaryimage\",\"url\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/05\\\/create_certificate_from_file.jpg\",\"contentUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/05\\\/create_certificate_from_file.jpg\",\"width\":816,\"height\":483,\"caption\":\"CREATE CERTIFICATE FROM FILE in action.\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2384\\\/restore-certificate\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to Restore a Certificate in SQL Server\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\",\"name\":\"sqlity.net\",\"description\":\"Quality for SQL\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/sqlity.net\\\/en\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\",\"name\":\"Sebastian Meine\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"caption\":\"Sebastian Meine\"},\"sameAs\":[\"http:\\\/\\\/sqlity.net\",\"https:\\\/\\\/x.com\\\/sqlity\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to Restore a Certificate in SQL Server - sqlity.net","description":"Do you know how to restore certificates in SQL Server? Read on to find out how, even though there is no RESTORE CERTIFICATE statement.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/sqlity.net\/en\/2384\/restore-certificate\/","og_locale":"en_US","og_type":"article","og_title":"How to Restore a Certificate in SQL Server - sqlity.net","og_description":"Do you know how to restore certificates in SQL Server? Read on to find out how, even though there is no RESTORE CERTIFICATE statement.","og_url":"https:\/\/sqlity.net\/en\/2384\/restore-certificate\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-05-05T18:00:59+00:00","article_modified_time":"2014-11-13T17:27:30+00:00","og_image":[{"width":816,"height":483,"url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/05\/create_certificate_from_file.jpg","type":"image\/jpeg"}],"author":"Sebastian Meine","twitter_card":"summary_large_image","twitter_creator":"@sqlity","twitter_site":"@sqlity","twitter_misc":{"Written by":"Sebastian Meine","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/2384\/restore-certificate\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/2384\/restore-certificate\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"How to Restore a Certificate in SQL Server","datePublished":"2014-05-05T18:00:59+00:00","dateModified":"2014-11-13T17:27:30+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/2384\/restore-certificate\/"},"wordCount":686,"commentCount":1,"image":{"@id":"https:\/\/sqlity.net\/en\/2384\/restore-certificate\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/05\/create_certificate_from_file.jpg","keywords":["backup","Certificates","Cryptography","restore","security","SQL Server"],"articleSection":["Cryptography","General","Security"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/2384\/restore-certificate\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/2384\/restore-certificate\/","url":"https:\/\/sqlity.net\/en\/2384\/restore-certificate\/","name":"How to Restore a Certificate in SQL Server - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/2384\/restore-certificate\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/2384\/restore-certificate\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/05\/create_certificate_from_file.jpg","datePublished":"2014-05-05T18:00:59+00:00","dateModified":"2014-11-13T17:27:30+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"Do you know how to restore certificates in SQL Server? Read on to find out how, even though there is no RESTORE CERTIFICATE statement.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/2384\/restore-certificate\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/2384\/restore-certificate\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/2384\/restore-certificate\/#primaryimage","url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/05\/create_certificate_from_file.jpg","contentUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/05\/create_certificate_from_file.jpg","width":816,"height":483,"caption":"CREATE CERTIFICATE FROM FILE in action."},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/2384\/restore-certificate\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"How to Restore a Certificate in SQL Server"}]},{"@type":"WebSite","@id":"https:\/\/sqlity.net\/en\/#website","url":"https:\/\/sqlity.net\/en\/","name":"sqlity.net","description":"Quality for SQL","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/sqlity.net\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c","name":"Sebastian Meine","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","caption":"Sebastian Meine"},"sameAs":["http:\/\/sqlity.net","https:\/\/x.com\/sqlity"]}]}},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/05\/create_certificate_from_file.jpg","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-Cs","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2384","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/comments?post=2384"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2384\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media\/2386"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=2384"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=2384"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=2384"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}