{"id":2379,"date":"2014-05-02T11:00:20","date_gmt":"2014-05-02T15:00:20","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=2379"},"modified":"2014-11-13T12:28:01","modified_gmt":"2014-11-13T17:28:01","slug":"backup-certificate","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/2379\/backup-certificate\/","title":{"rendered":"How to Back Up Your Certificates"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nIn SQL Server, certificates are stored within the database in which they were created. That means that they are backed up together with the database. However, as a certificate might hold the \"key\" to a lot of your data, it is often advisable to have a separate backup. In fact, I recommend that you create a separate backup of every certificate as soon as you have created it.\n<\/p>\n

\nCertificate backups also provide a simple way to move or copy a certificate from one database to another.\n<\/p>\n

The BACKUP CERTIFICATE Statement<\/h3>\n

\nTo back up a certificate you can use the BACKUP CERTIFICATE<\/span> statement. In its simplest form, it looks like this:\n<\/p>\n

\n[sql]\nBACKUP CERTIFICATE ACertificate
\n TO FILE ='C:\\temp\\ACertificate.cert';
\n[\/sql]\n<\/div>\n

\nThis statement writes the public portion of the certificate to the specified file. Other than the BACKUP DATABASE<\/span> statement, BACKUP CERTIFICATE<\/span> has no option to overwrite existing files, so the file must not exist.\n<\/p>\n

But what about the Private Key?<\/h3>\n

\nThe above statement creates a backup of the public portion of the key only. That is however not the important part. The important part is the private key of the certificate. To create a backup of the private key too, we have to add the WITH PRIVATE KEY<\/span> clause to the BACKUP CERTIFICATE<\/span> statement:\n<\/p>\n

\n[sql]\nBACKUP CERTIFICATE ACertificate
\n TO FILE ='C:\\temp\\ACertificate.cert'
\n WITH PRIVATE KEY(
\n FILE = 'C:\\temp\\ACertificate.prvk',
\n ENCRYPTION BY PASSWORD = '**********'
\n );
\n[\/sql]\n<\/div>\n

\nThe BACKUP CERTIFICATE<\/span> statement does not produce any output by itself. To be able to demonstrate it anyway, I added a call to xp_cmdshell<\/span> that shows the created files:\n<\/p>\n

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

\nThere are two things to note here. First, the private key is always written to a separate file. There is no way to create a single-file backup of a certificate in SQL Server. Second, in SQL Server all keys are at all times protected by either a password or by another key in the encryption hierarchy<\/a>. That extends to the backup files too. As backup files are intended to be stored of site, the SQL Server encryption hierarchy is not available to protect them. Therefore, we have to provide a password to protect the key. This password needs to comply with the SQL Server password policy.\n<\/p>\n

\nAnybody who can guess the password has free reign on your data. However, without the password there is no way to restore the certificate. So, select a complex, long and random password and then store it somewhere secure, independent of the backup files.\n<\/p>\n

Backing Up a Password Protected Certificate<\/h3>\n

\nThe above example works for certificates that are protected by the database master key<\/a>. If you want to back up a certificate that is protected by a password, you need to provide that password in the BACKUP CERTIFICATE<\/span> statement. As the password is only required to open the private key, it is provided in the WITH PRIVATE KEY<\/span> section of the CREATE CERTIFICATE<\/span> statement, using the DECRYPTION BY PASSWORD<\/span> clause:\n<\/p>\n

\n[sql]\nBACKUP CERTIFICATE ACertificate
\n TO FILE ='C:\\temp\\ACertificate.cert'
\n WITH PRIVATE KEY(
\n FILE = 'C:\\temp\\ACertificate.prvk',
\n DECRYPTION BY PASSWORD = '%%%%%%%%%%',
\n ENCRYPTION BY PASSWORD = '**********'
\n );
\n[\/sql]\n<\/div>\n

\nThe two passwords do not have to be the same. In fact, I recommend that you do not reuse the certificate password to protect the backup.\n<\/p>\n

Summary<\/h3>\n

\nCertificates are part of the database and as such are protected by your database backup strategy. However, there are many situations, in which it is advisable to create a separate backup for all your certificates. To do that, the BACKUP CERTIFICATE<\/span> statement can be used.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

Certificates are an important part of you encryption strategy and as such need to be backed up. Discover how to achieve that, by using the BACKUP CERTIFICATE statement.<\/p>\n

[more…]<\/a><\/p>\n","protected":false},"author":3,"featured_media":2380,"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":[69,32,5,34],"tags":[70,74,18,49,273,38,15],"class_list":["post-2379","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-backup","category-cryptography","category-general","category-security","tag-backup-2","tag-backup-strategy","tag-best-practice","tag-certificates","tag-cryptography","tag-security-2","tag-sql-server"],"acf":[],"yoast_head":"\nHow to Back Up Your Certificates - sqlity.net<\/title>\n<meta name=\"description\" content=\"Certificates are an important part of you encryption strategy and as such need to be backed up. Discover how to, by using the BACKUP 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\/2379\/backup-certificate\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to Back Up Your Certificates - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"Certificates are an important part of you encryption strategy and as such need to be backed up. Discover how to, by using the BACKUP CERTIFICATE statement.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/2379\/backup-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-02T15:00:20+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T17:28:01+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/04\/BACKUP_CERTIFICATE_in_action.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=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2379\\\/backup-certificate\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2379\\\/backup-certificate\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"How to Back Up Your Certificates\",\"datePublished\":\"2014-05-02T15:00:20+00:00\",\"dateModified\":\"2014-11-13T17:28:01+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2379\\\/backup-certificate\\\/\"},\"wordCount\":290,\"commentCount\":3,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2379\\\/backup-certificate\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/04\\\/BACKUP_CERTIFICATE_in_action.jpg\",\"keywords\":[\"backup\",\"backup strategy\",\"Best Practice\",\"Certificates\",\"Cryptography\",\"security\",\"SQL Server\"],\"articleSection\":[\"Backup\",\"Cryptography\",\"General\",\"Security\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2379\\\/backup-certificate\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2379\\\/backup-certificate\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2379\\\/backup-certificate\\\/\",\"name\":\"How to Back Up Your Certificates - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2379\\\/backup-certificate\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2379\\\/backup-certificate\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/04\\\/BACKUP_CERTIFICATE_in_action.jpg\",\"datePublished\":\"2014-05-02T15:00:20+00:00\",\"dateModified\":\"2014-11-13T17:28:01+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"Certificates are an important part of you encryption strategy and as such need to be backed up. Discover how to, by using the BACKUP CERTIFICATE statement.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2379\\\/backup-certificate\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2379\\\/backup-certificate\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2379\\\/backup-certificate\\\/#primaryimage\",\"url\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/04\\\/BACKUP_CERTIFICATE_in_action.jpg\",\"contentUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/04\\\/BACKUP_CERTIFICATE_in_action.jpg\",\"width\":816,\"height\":483,\"caption\":\"The BACKUP CERTIFICATE statement in Action.\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2379\\\/backup-certificate\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to Back Up Your Certificates\"}]},{\"@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 Back Up Your Certificates - sqlity.net","description":"Certificates are an important part of you encryption strategy and as such need to be backed up. Discover how to, by using the BACKUP 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\/2379\/backup-certificate\/","og_locale":"en_US","og_type":"article","og_title":"How to Back Up Your Certificates - sqlity.net","og_description":"Certificates are an important part of you encryption strategy and as such need to be backed up. Discover how to, by using the BACKUP CERTIFICATE statement.","og_url":"https:\/\/sqlity.net\/en\/2379\/backup-certificate\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-05-02T15:00:20+00:00","article_modified_time":"2014-11-13T17:28:01+00:00","og_image":[{"width":816,"height":483,"url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/04\/BACKUP_CERTIFICATE_in_action.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":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/2379\/backup-certificate\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/2379\/backup-certificate\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"How to Back Up Your Certificates","datePublished":"2014-05-02T15:00:20+00:00","dateModified":"2014-11-13T17:28:01+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/2379\/backup-certificate\/"},"wordCount":290,"commentCount":3,"image":{"@id":"https:\/\/sqlity.net\/en\/2379\/backup-certificate\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/04\/BACKUP_CERTIFICATE_in_action.jpg","keywords":["backup","backup strategy","Best Practice","Certificates","Cryptography","security","SQL Server"],"articleSection":["Backup","Cryptography","General","Security"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/2379\/backup-certificate\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/2379\/backup-certificate\/","url":"https:\/\/sqlity.net\/en\/2379\/backup-certificate\/","name":"How to Back Up Your Certificates - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/2379\/backup-certificate\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/2379\/backup-certificate\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/04\/BACKUP_CERTIFICATE_in_action.jpg","datePublished":"2014-05-02T15:00:20+00:00","dateModified":"2014-11-13T17:28:01+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"Certificates are an important part of you encryption strategy and as such need to be backed up. Discover how to, by using the BACKUP CERTIFICATE statement.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/2379\/backup-certificate\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/2379\/backup-certificate\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/2379\/backup-certificate\/#primaryimage","url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/04\/BACKUP_CERTIFICATE_in_action.jpg","contentUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/04\/BACKUP_CERTIFICATE_in_action.jpg","width":816,"height":483,"caption":"The BACKUP CERTIFICATE statement in Action."},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/2379\/backup-certificate\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"How to Back Up Your Certificates"}]},{"@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\/04\/BACKUP_CERTIFICATE_in_action.jpg","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-Cn","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2379","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=2379"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2379\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media\/2380"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=2379"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=2379"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=2379"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}