{"id":2397,"date":"2014-05-12T11:00:36","date_gmt":"2014-05-12T15:00:36","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=2397"},"modified":"2014-11-13T12:26:55","modified_gmt":"2014-11-13T17:26:55","slug":"certproperty","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/2397\/certproperty\/","title":{"rendered":"CERTPROPERTY \u2013 Digging Deeper into SQL Server Certificates"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nSQL Server certificates come with a variety of properties. To retrieve some of these properties, you can use the built-in CERTPROPERTY<\/span> function.\n<\/p>\n

CERTPROPERTY Example<\/h3>\n

\nThe CERTPROPERTY<\/span> function takes two parameters. The first parameter is the certificate_id<\/span> of the certificate that you are interested in. The certificate needs to exist in the current database. To get the certificate_id<\/span> for it, you can for example use the built-in CERT_ID<\/span> function<\/a>. The following example will make use of this option.\n<\/p>\n

The second parameter of the CERTPROPERTY<\/span> function is the name of the property you want to read. The following example select statements show all possible property names:\n<\/p>\n

\n[sql]\nSELECT CERTPROPERTY(CERT_ID('ACertificate'),'Expiry_Date') AS Expiry_Date,
\n CERTPROPERTY(CERT_ID('ACertificate'),'Start_Date') AS Start_Date,
\n CERTPROPERTY(CERT_ID('ACertificate'),'Issuer_Name') AS Issuer_Name,
\n CERTPROPERTY(CERT_ID('ACertificate'),'Cert_Serial_Number') AS Cert_Serial_Number
\nSELECT CERTPROPERTY(CERT_ID('ACertificate'),'Subject') AS Subject,
\n CERTPROPERTY(CERT_ID('ACertificate'),'SID') AS SID,
\n CERTPROPERTY(CERT_ID('ACertificate'),'String_SID') AS String_SID
\n [\/sql]\n<\/div>\n

\nThe only reason this example uses two separate select statements instead of one is to make the output fit on one screenshot. Well — almost:\n<\/p>\n

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

\nInterestingly, there is currently no way to retrieve the name of a certificate, neither with the CERTPROPERTY<\/span> function nor with a dedicated CERT_NAME<\/span> function. The Expiry_Date, Start_Date, Issuer_Name and Cert_Serial_Number properties return just those values. The Subject is worth an article on its own. For now, it should suffice to mention that it is the same value that you have to specify when you create a new certificate<\/a> in SQL Server. The SID is used by SQL Server to link the certificate to a server principal when the latter is created based on the certificate. The String_SID finally is that same SID value, expressed in a special string format.\n<\/p>\n

Why not sys.certificates?<\/h3>\n

\nYou might have noticed that all of these values are also part of the sys.certificates<\/span> catalog view<\/a>. Both were introduced together in SQL Server 2005 so it seems the SQL Server team just wanted to give us options on how to get to this information.\n<\/p>\n

Summary<\/h3>\n

\nThe built-in CERTPROPERTY<\/span> function provides an additional way to get to some of the properties of a particular certificate. All properties accessible through this function are also exposed in the sys.certificates<\/span> catalog view.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

Discover how to read the properties of a specific SQL Server certificate using its certificate_id and the built-in CERTPROPERTY function.<\/p>\n

[more…]<\/a><\/p>\n","protected":false},"author":3,"featured_media":2398,"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":[49,273,182,38,15],"class_list":["post-2397","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-cryptography","category-general","category-security","tag-certificates","tag-cryptography","tag-properties","tag-security-2","tag-sql-server"],"acf":[],"yoast_head":"\nCERTPROPERTY \u2013 Digging Deeper into SQL Server Certificates - sqlity.net<\/title>\n<meta name=\"description\" content=\"Discover how to read the properties of a specific SQL Server certificate using its certificate_id and the built-in CERTPROPERTY function.\" \/>\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\/2397\/certproperty\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"CERTPROPERTY \u2013 Digging Deeper into SQL Server Certificates - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"Discover how to read the properties of a specific SQL Server certificate using its certificate_id and the built-in CERTPROPERTY function.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/2397\/certproperty\/\" \/>\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-12T15:00:36+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T17:26:55+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/05\/CERTPROPERTY_in_action.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"816\" \/>\n\t<meta property=\"og:image:height\" content=\"532\" \/>\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=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2397\\\/certproperty\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2397\\\/certproperty\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"CERTPROPERTY \u2013 Digging Deeper into SQL Server Certificates\",\"datePublished\":\"2014-05-12T15:00:36+00:00\",\"dateModified\":\"2014-11-13T17:26:55+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2397\\\/certproperty\\\/\"},\"wordCount\":427,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2397\\\/certproperty\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/05\\\/CERTPROPERTY_in_action.jpg\",\"keywords\":[\"Certificates\",\"Cryptography\",\"properties\",\"security\",\"SQL Server\"],\"articleSection\":[\"Cryptography\",\"General\",\"Security\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2397\\\/certproperty\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2397\\\/certproperty\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2397\\\/certproperty\\\/\",\"name\":\"CERTPROPERTY \u2013 Digging Deeper into SQL Server Certificates - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2397\\\/certproperty\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2397\\\/certproperty\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/05\\\/CERTPROPERTY_in_action.jpg\",\"datePublished\":\"2014-05-12T15:00:36+00:00\",\"dateModified\":\"2014-11-13T17:26:55+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"Discover how to read the properties of a specific SQL Server certificate using its certificate_id and the built-in CERTPROPERTY function.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2397\\\/certproperty\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2397\\\/certproperty\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2397\\\/certproperty\\\/#primaryimage\",\"url\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/05\\\/CERTPROPERTY_in_action.jpg\",\"contentUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/05\\\/CERTPROPERTY_in_action.jpg\",\"width\":816,\"height\":532,\"caption\":\"CERTPROPERTY in Action.\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2397\\\/certproperty\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"CERTPROPERTY \u2013 Digging Deeper into SQL Server 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":"CERTPROPERTY \u2013 Digging Deeper into SQL Server Certificates - sqlity.net","description":"Discover how to read the properties of a specific SQL Server certificate using its certificate_id and the built-in CERTPROPERTY function.","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\/2397\/certproperty\/","og_locale":"en_US","og_type":"article","og_title":"CERTPROPERTY \u2013 Digging Deeper into SQL Server Certificates - sqlity.net","og_description":"Discover how to read the properties of a specific SQL Server certificate using its certificate_id and the built-in CERTPROPERTY function.","og_url":"https:\/\/sqlity.net\/en\/2397\/certproperty\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-05-12T15:00:36+00:00","article_modified_time":"2014-11-13T17:26:55+00:00","og_image":[{"width":816,"height":532,"url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/05\/CERTPROPERTY_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":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/2397\/certproperty\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/2397\/certproperty\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"CERTPROPERTY \u2013 Digging Deeper into SQL Server Certificates","datePublished":"2014-05-12T15:00:36+00:00","dateModified":"2014-11-13T17:26:55+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/2397\/certproperty\/"},"wordCount":427,"commentCount":0,"image":{"@id":"https:\/\/sqlity.net\/en\/2397\/certproperty\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/05\/CERTPROPERTY_in_action.jpg","keywords":["Certificates","Cryptography","properties","security","SQL Server"],"articleSection":["Cryptography","General","Security"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/2397\/certproperty\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/2397\/certproperty\/","url":"https:\/\/sqlity.net\/en\/2397\/certproperty\/","name":"CERTPROPERTY \u2013 Digging Deeper into SQL Server Certificates - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/2397\/certproperty\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/2397\/certproperty\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/05\/CERTPROPERTY_in_action.jpg","datePublished":"2014-05-12T15:00:36+00:00","dateModified":"2014-11-13T17:26:55+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"Discover how to read the properties of a specific SQL Server certificate using its certificate_id and the built-in CERTPROPERTY function.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/2397\/certproperty\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/2397\/certproperty\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/2397\/certproperty\/#primaryimage","url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/05\/CERTPROPERTY_in_action.jpg","contentUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/05\/CERTPROPERTY_in_action.jpg","width":816,"height":532,"caption":"CERTPROPERTY in Action."},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/2397\/certproperty\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"CERTPROPERTY \u2013 Digging Deeper into SQL Server 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_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-CF","jetpack-related-posts":[],"jetpack_featured_media_url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/05\/CERTPROPERTY_in_action.jpg","_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2397","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=2397"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2397\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media\/2398"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=2397"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=2397"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=2397"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}