{"id":2375,"date":"2014-04-28T11:00:45","date_gmt":"2014-04-28T15:00:45","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=2375"},"modified":"2014-11-13T12:28:22","modified_gmt":"2014-11-13T17:28:22","slug":"create-certificate","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/2375\/create-certificate\/","title":{"rendered":"CREATE CERTIFICATE – How to create a Self-Signed Certificate in SQL Server"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nWith the CREATE CERTIFICATE<\/span> T-SQL statement you can create a self-signed certificate. Certificates<\/a> are asymmetric encryption keys that are signed by a certificate authority. SQL Server does not provide a way to create or request such an externally signed certificate (but they can be imported). However, when working with certificates in SQL Server we often do not need any outside validation. In those cases, it is appropriate to create a self-signed certificate.\n<\/p>\n

CREATE CERTIFICATE Example<\/h3>\n

\nThe CREATE CERTIFICATE<\/span> statement in its simplest form has the following format:\n<\/p>\n

\n[sql]\nCREATE CERTIFICATE ATestCertificate
\n WITH SUBJECT = 'A Test Certificate';
\n[\/sql]\n<\/div>\n

\nATestCertificate<\/span> is the name for the certificate. . Subject is a field defined by the X.509 standard and certificates created by this command follow that standard. In SQL Server, the subject can be up to 128 characters long. This is a SQL Server restriction, the X.509 standard itself allows for longer subjects.\n<\/p>\n

\nThe private key for SQL Server generated certificates is always a 1024-bit key for the RSA encryption algorithm. SQL Server supports imported certificate key lengths from 384 to 4096 bits but can only generate this one key length for certificates.\n<\/p>\n

Database Master Key vs. Password Protected Certificates<\/h3>\n

\nCertificates created with the CREATE CERTIFICATE<\/span> statement as described in the previous section are protected by the database master key<\/a>. To be able to execute that statement, the database master key has to exist. If the database master key does not exist or if it cannot be opened, the CREATE CERTIFICATE<\/span> statement will fail.\n<\/p>\n

\nAlternatively, if you do not want the private key to be protected by the database master key, you can provide a password using the ENCRYPTION BY PASSWORD<\/span> clause:\n<\/p>\n

\n[sql]\nCREATE CERTIFICATE ATestCertificate
\n ENCRYPTION BY PASSWORD = '**********'
\n WITH SUBJECT = 'A Test Certificate';
\n[\/sql]\n<\/div>\n

\nA certificate can only be protected by either a password or the database master key. If a password is specified during creation, that same password is required every time the private key needs to be accessed.\n<\/p>\n

Certificate Expiration Dates<\/h3>\n

\nThe CREATE CERTIFICATE<\/span> statement allows you to specify a date range during which the new certificate is valid. However, only the Service Broker checks those dates. Any other SQL Server functionality that uses certificates simply ignores these fields.\n<\/p>\n

\nThe expanded CREATE CERTIFICATE<\/span> syntax that includes those dates is:\n<\/p>\n

\n[sql]\nCREATE CERTIFICATE ATestCertificate
\n ENCRYPTION BY PASSWORD = '**********'
\n WITH SUBJECT = 'A Test Certificate',
\n START_DATE = '2014-04-24',
\n EXPIRY_DATE = '2015-04-23';
\n[\/sql]\n<\/div>\n

Checking if a Certificate Exists<\/h3>\n

\nYou can check if a certificate exists already for example by using the sys.certificates<\/span> catalog view:\n<\/p>\n

\n[sql]\nSELECT C.name,C.certificate_id,C.pvt_key_encryption_type_desc,C.subject,C.start_date,C.expiry_date
\n FROM sys.certificates AS C
\n WHERE C.name = 'ATestCertificate';
\n[\/sql]\n<\/div>\n

\nThe output of this statement includes the valid date range as well as the certificate's subject:\n<\/p>\n

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

Summary<\/h3>\n

\nCREATE CERTIFICATE<\/span> can be used to create a self-signed certificate in SQL Server. The private key of such a certificate can be protected either by a password or by the database master key.\n<\/p>\n

Encryption Hierarchy Series<\/h3>\n

\nThis article is part of the Encryption Hierarchy<\/a> series. Check out The SQL Server Encryption Hierarchy<\/a> for an overview of the hierarchy and a list of all posts that are part of the series.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

Discover how the CREATE CERTIFICATE T-SQL statement can be used to create a self-signed certificate, and what your options are to protect its private key.<\/p>\n

[more…]<\/a><\/p>\n","protected":false},"author":3,"featured_media":2376,"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,167,5,34,27],"tags":[49,273,172,173,38,15],"class_list":["post-2375","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-cryptography","category-encryption-hierarchy","category-general","category-security","category-series","tag-certificates","tag-cryptography","tag-database-master-key","tag-private-key","tag-security-2","tag-sql-server"],"acf":[],"yoast_head":"\nCREATE CERTIFICATE - How to create a Self-Signed Certificate in SQL Server - sqlity.net<\/title>\n<meta name=\"description\" content=\"Discover how the CREATE CERTIFICATE T-SQL statement can be used to create a self-signed certificate, and what your options are to protect its private key.\" \/>\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\/2375\/create-certificate\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"CREATE CERTIFICATE - How to create a Self-Signed Certificate in SQL Server - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"Discover how the CREATE CERTIFICATE T-SQL statement can be used to create a self-signed certificate, and what your options are to protect its private key.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/2375\/create-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-04-28T15:00:45+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T17:28:22+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/04\/CREATE_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=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2375\\\/create-certificate\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2375\\\/create-certificate\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"CREATE CERTIFICATE – How to create a Self-Signed Certificate in SQL Server\",\"datePublished\":\"2014-04-28T15:00:45+00:00\",\"dateModified\":\"2014-11-13T17:28:22+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2375\\\/create-certificate\\\/\"},\"wordCount\":576,\"commentCount\":5,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2375\\\/create-certificate\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/04\\\/CREATE_CERTIFICATE_in_action.jpg\",\"keywords\":[\"Certificates\",\"Cryptography\",\"Database Master Key\",\"private key\",\"security\",\"SQL Server\"],\"articleSection\":[\"Cryptography\",\"Encryption Hierarchy\",\"General\",\"Security\",\"Series\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2375\\\/create-certificate\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2375\\\/create-certificate\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2375\\\/create-certificate\\\/\",\"name\":\"CREATE CERTIFICATE - How to create a Self-Signed Certificate in SQL Server - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2375\\\/create-certificate\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2375\\\/create-certificate\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/04\\\/CREATE_CERTIFICATE_in_action.jpg\",\"datePublished\":\"2014-04-28T15:00:45+00:00\",\"dateModified\":\"2014-11-13T17:28:22+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"Discover how the CREATE CERTIFICATE T-SQL statement can be used to create a self-signed certificate, and what your options are to protect its private key.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2375\\\/create-certificate\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2375\\\/create-certificate\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2375\\\/create-certificate\\\/#primaryimage\",\"url\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/04\\\/CREATE_CERTIFICATE_in_action.jpg\",\"contentUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/04\\\/CREATE_CERTIFICATE_in_action.jpg\",\"width\":816,\"height\":483,\"caption\":\"CREATE CERTIFICATE in action.\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2375\\\/create-certificate\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"CREATE CERTIFICATE – How to create a Self-Signed 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":"CREATE CERTIFICATE - How to create a Self-Signed Certificate in SQL Server - sqlity.net","description":"Discover how the CREATE CERTIFICATE T-SQL statement can be used to create a self-signed certificate, and what your options are to protect its private key.","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\/2375\/create-certificate\/","og_locale":"en_US","og_type":"article","og_title":"CREATE CERTIFICATE - How to create a Self-Signed Certificate in SQL Server - sqlity.net","og_description":"Discover how the CREATE CERTIFICATE T-SQL statement can be used to create a self-signed certificate, and what your options are to protect its private key.","og_url":"https:\/\/sqlity.net\/en\/2375\/create-certificate\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-04-28T15:00:45+00:00","article_modified_time":"2014-11-13T17:28:22+00:00","og_image":[{"width":816,"height":483,"url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/04\/CREATE_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":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/2375\/create-certificate\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/2375\/create-certificate\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"CREATE CERTIFICATE – How to create a Self-Signed Certificate in SQL Server","datePublished":"2014-04-28T15:00:45+00:00","dateModified":"2014-11-13T17:28:22+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/2375\/create-certificate\/"},"wordCount":576,"commentCount":5,"image":{"@id":"https:\/\/sqlity.net\/en\/2375\/create-certificate\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/04\/CREATE_CERTIFICATE_in_action.jpg","keywords":["Certificates","Cryptography","Database Master Key","private key","security","SQL Server"],"articleSection":["Cryptography","Encryption Hierarchy","General","Security","Series"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/2375\/create-certificate\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/2375\/create-certificate\/","url":"https:\/\/sqlity.net\/en\/2375\/create-certificate\/","name":"CREATE CERTIFICATE - How to create a Self-Signed Certificate in SQL Server - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/2375\/create-certificate\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/2375\/create-certificate\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/04\/CREATE_CERTIFICATE_in_action.jpg","datePublished":"2014-04-28T15:00:45+00:00","dateModified":"2014-11-13T17:28:22+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"Discover how the CREATE CERTIFICATE T-SQL statement can be used to create a self-signed certificate, and what your options are to protect its private key.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/2375\/create-certificate\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/2375\/create-certificate\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/2375\/create-certificate\/#primaryimage","url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/04\/CREATE_CERTIFICATE_in_action.jpg","contentUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/04\/CREATE_CERTIFICATE_in_action.jpg","width":816,"height":483,"caption":"CREATE CERTIFICATE in action."},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/2375\/create-certificate\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"CREATE CERTIFICATE – How to create a Self-Signed 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_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-Cj","jetpack-related-posts":[],"jetpack_featured_media_url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/04\/CREATE_CERTIFICATE_in_action.jpg","_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2375","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=2375"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2375\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media\/2376"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=2375"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=2375"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=2375"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}