{"id":2381,"date":"2014-05-04T11:00:27","date_gmt":"2014-05-04T15:00:27","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=2381"},"modified":"2014-11-13T12:27:50","modified_gmt":"2014-11-13T17:27:50","slug":"sys-certificates","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/2381\/sys-certificates\/","title":{"rendered":"sys.certificates: How to get Information about all Certificates in a Database"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nWhen working with certificates, every now and then you need to know which certificates are currently present in the database. Other times you might want to know additional information about a particular certificate, for example when it was last backed up. In those cases you can get answers from the sys.certificates<\/span> catalog view.\n<\/p>\n

The sys.certificates Catalog View<\/h3>\n

\nThe sys.certificates<\/span> catalog view returns one row per certificate in the current database. It has the following columns:\n<\/p>\n

\n[sql]\nSELECT C.name,
\n C.certificate_id,
\n C.principal_id,
\n C.pvt_key_encryption_type,
\n C.pvt_key_encryption_type_desc,
\n C.is_active_for_begin_dialog,
\n C.issuer_name,
\n C.cert_serial_number,
\n C.sid,
\n C.string_sid,
\n C.subject,
\n C.expiry_date,
\n C.start_date,
\n C.thumbprint,
\n C.attested_by,
\n C.pvt_key_last_backup_date
\n FROM sys.certificates AS C;
\n[\/sql]\n<\/div>\n

\nThe columns name<\/span>, certificate_id<\/span> and sid<\/span> fall into the category \"identifying information\". The sid<\/span> is used when a server principal<\/a> is created from the certificate. That principal's sid<\/span> will be set to this value. string_sid<\/span> is an alternative representation of the same value.\n<\/p>\n

\nThe columns issuer_name<\/span>, cert_serial_number<\/span>, subject<\/span>, expiry_date<\/span>, start_date<\/span> and thumbprint<\/span> are official fields defined in the X.509 certificate standard.\n<\/p>\n

\nThe principal_id<\/span> column indicates who owns<\/a> the certificate; and the two columns pvt_key_encryption_type<\/span> and pvt_key_encryption_type_desc<\/span> indicate how the private key is protected.\n<\/p>\n

\nThe below image shows a few of the columns of this CV in a database with two certificates:\n<\/p>\n

\n\"sys.certificates<\/a>\n<\/p>\n

\nIn addition to the columns described above, there are two more columns that are potentially of interest to you: is_active_for_begin_dialog<\/span> and pvt_key_last_backup_date<\/span>. is_active_for_begin_dialog<\/span> indicates if this certificate can be used to initiate a service broker dialog. pvt_key_last_backup_date<\/span> on the other hand contains the date and time of the last time the certificate's private key was backed up<\/a>.\n<\/p>\n

Summary<\/h3>\n

\nThe sys.certificates<\/span> catalog view returns one row per certificate in the current database. It returns general information about each certificate that includes properties like the name, the X.509 subject and the last backup date.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

sys.certificates: Find out where to turn when you need information like the name the X.509 subject or the last backup date of a certificate that is installed in the current database.<\/p>\n

[more…]<\/a><\/p>\n","protected":false},"author":3,"featured_media":2382,"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,118,5,34],"tags":[49,273,122,38,15],"class_list":["post-2381","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-cryptography","category-dmvs-cvs","category-general","category-security","tag-certificates","tag-cryptography","tag-cv","tag-security-2","tag-sql-server"],"acf":[],"yoast_head":"\nsys.certificates: How to get Information about all Certificates in a Database - sqlity.net<\/title>\n<meta name=\"description\" content=\"sys.certificates: Where to turn when you need information like the name or the last backup date of a certificate that is installed in the current database.\" \/>\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\/2381\/sys-certificates\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"sys.certificates: How to get Information about all Certificates in a Database - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"sys.certificates: Where to turn when you need information like the name or the last backup date of a certificate that is installed in the current database.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/2381\/sys-certificates\/\" \/>\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-04T15:00:27+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T17:27:50+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/05\/sys.certificates_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=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2381\\\/sys-certificates\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2381\\\/sys-certificates\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"sys.certificates: How to get Information about all Certificates in a Database\",\"datePublished\":\"2014-05-04T15:00:27+00:00\",\"dateModified\":\"2014-11-13T17:27:50+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2381\\\/sys-certificates\\\/\"},\"wordCount\":424,\"commentCount\":1,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2381\\\/sys-certificates\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/05\\\/sys.certificates_in_action.jpg\",\"keywords\":[\"Certificates\",\"Cryptography\",\"CV\",\"security\",\"SQL Server\"],\"articleSection\":[\"Cryptography\",\"DMVs & CVs\",\"General\",\"Security\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2381\\\/sys-certificates\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2381\\\/sys-certificates\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2381\\\/sys-certificates\\\/\",\"name\":\"sys.certificates: How to get Information about all Certificates in a Database - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2381\\\/sys-certificates\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2381\\\/sys-certificates\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/05\\\/sys.certificates_in_action.jpg\",\"datePublished\":\"2014-05-04T15:00:27+00:00\",\"dateModified\":\"2014-11-13T17:27:50+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"sys.certificates: Where to turn when you need information like the name or the last backup date of a certificate that is installed in the current database.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2381\\\/sys-certificates\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2381\\\/sys-certificates\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2381\\\/sys-certificates\\\/#primaryimage\",\"url\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/05\\\/sys.certificates_in_action.jpg\",\"contentUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/05\\\/sys.certificates_in_action.jpg\",\"width\":816,\"height\":483,\"caption\":\"sys.certificates in Action.\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2381\\\/sys-certificates\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"sys.certificates: How to get Information about all Certificates in a Database\"}]},{\"@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":"sys.certificates: How to get Information about all Certificates in a Database - sqlity.net","description":"sys.certificates: Where to turn when you need information like the name or the last backup date of a certificate that is installed in the current database.","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\/2381\/sys-certificates\/","og_locale":"en_US","og_type":"article","og_title":"sys.certificates: How to get Information about all Certificates in a Database - sqlity.net","og_description":"sys.certificates: Where to turn when you need information like the name or the last backup date of a certificate that is installed in the current database.","og_url":"https:\/\/sqlity.net\/en\/2381\/sys-certificates\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-05-04T15:00:27+00:00","article_modified_time":"2014-11-13T17:27:50+00:00","og_image":[{"width":816,"height":483,"url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/05\/sys.certificates_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\/2381\/sys-certificates\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/2381\/sys-certificates\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"sys.certificates: How to get Information about all Certificates in a Database","datePublished":"2014-05-04T15:00:27+00:00","dateModified":"2014-11-13T17:27:50+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/2381\/sys-certificates\/"},"wordCount":424,"commentCount":1,"image":{"@id":"https:\/\/sqlity.net\/en\/2381\/sys-certificates\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/05\/sys.certificates_in_action.jpg","keywords":["Certificates","Cryptography","CV","security","SQL Server"],"articleSection":["Cryptography","DMVs & CVs","General","Security"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/2381\/sys-certificates\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/2381\/sys-certificates\/","url":"https:\/\/sqlity.net\/en\/2381\/sys-certificates\/","name":"sys.certificates: How to get Information about all Certificates in a Database - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/2381\/sys-certificates\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/2381\/sys-certificates\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/05\/sys.certificates_in_action.jpg","datePublished":"2014-05-04T15:00:27+00:00","dateModified":"2014-11-13T17:27:50+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"sys.certificates: Where to turn when you need information like the name or the last backup date of a certificate that is installed in the current database.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/2381\/sys-certificates\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/2381\/sys-certificates\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/2381\/sys-certificates\/#primaryimage","url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/05\/sys.certificates_in_action.jpg","contentUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/05\/sys.certificates_in_action.jpg","width":816,"height":483,"caption":"sys.certificates in Action."},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/2381\/sys-certificates\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"sys.certificates: How to get Information about all Certificates in a Database"}]},{"@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-Cp","jetpack-related-posts":[],"jetpack_featured_media_url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/05\/sys.certificates_in_action.jpg","_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2381","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=2381"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2381\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media\/2382"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=2381"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=2381"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=2381"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}