{"id":2449,"date":"2014-06-23T12:00:52","date_gmt":"2014-06-23T16:00:52","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=2449"},"modified":"2014-11-13T11:58:22","modified_gmt":"2014-11-13T16:58:22","slug":"sys-key_encryptions","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/2449\/sys-key_encryptions\/","title":{"rendered":"The sys.key_encryptions Catalog View"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nAll encryption keys and certificates in SQL Server are encrypted by another key or a password. Which mechanism is used, you specify when creating the key using the ENCRYPTION BY<\/span> clause. Symmetric keys however are a little different. They can be altered after creation to be encrypted by more than one key or password at the same time. For that reason, there is a special catalog view that returns all encryptions for the symmetric keys in the current database.\n<\/p>\n

A sys.key_encryptions Example<\/h3>\n

\nThe sys.key_encryptions<\/span> catalog view has the following columns:\n<\/p>\n

\n[sql]\nSELECT KE.key_id,
\n KE.thumbprint,
\n KE.crypt_type,
\n KE.crypt_type_desc,
\n KE.crypt_property
\n FROM sys.key_encryptions AS KE;
\n[\/sql]\n<\/div>\n

\nThe key_id<\/span> column contains the id of the symmetric key and can be used to join to the sys.symmetric_keys<\/span><\/a> catalog view.\n<\/p>\n

\nThe two columns crypt_type<\/span> and crypt_type_desc<\/span> let us know if the encryption is based on a password, an asymmetric key or any of the other encryption options. In the later column you will accordingly find values like ENCRYPTION BY PASSWORD<\/span> or ENCRYPTION BY ASYMMETRIC KEY<\/span>.\n<\/p>\n

\nFor an encryption that is based on another key in the database, the thumbprint can be used to identify that key. If you for example look at the sys.asymmetric_keys<\/span><\/a> catalog view you will find that same thumbprint<\/span> value there too.\n<\/p>\n

\nThe crypt_property<\/span> column finally contains the actual encrypted key. If the key is encrypted with another symmetric or asymmetric key, you can actually use the SQL Server decryption functions to get the unencrypted key from this value.\n<\/p>\n

\nBelow is the output of above query on my system:\n<\/p>\n

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

\nThere are three different keys shown in the output with a total of six key encryptions.\n<\/p>\n

Summary<\/h3>\n

\nSymmetric keys in SQL Server can be encrypted by more than one key or password at the same time. The sys.key_encryptions<\/span> catalog view can be used to identify the different encryptions for all symmetric keys in the current database.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

Do you have a symmetric key in a SQL Server database but forgot how it is encrypted? Discover how you can use the sys.key_encryptions catalog view to find out which protection mechanisms were used to encrypt the symmetric keys in the current database.<\/p>\n

[more…]<\/a><\/p>\n","protected":false},"author":3,"featured_media":2450,"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,119],"tags":[273,122,38,15,140,141],"class_list":["post-2449","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-cryptography","category-dmvs-cvs","category-general","category-security","category-security-dmvs-cvs","tag-cryptography","tag-cv","tag-security-2","tag-sql-server","tag-symmetric-key","tag-symmetric-key-encryption"],"acf":[],"yoast_head":"\nThe sys.key_encryptions Catalog View - sqlity.net<\/title>\n<meta name=\"description\" content=\"Do you have a symmetric key in SQL Server but forgot how it is encrypted? Discover how you can use the sys.key_encryptions catalog view to find out.\" \/>\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\/2449\/sys-key_encryptions\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"The sys.key_encryptions Catalog View - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"Do you have a symmetric key in SQL Server but forgot how it is encrypted? Discover how you can use the sys.key_encryptions catalog view to find out.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/2449\/sys-key_encryptions\/\" \/>\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-06-23T16:00:52+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T16:58:22+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/06\/sys.key_encryptions_in_action.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"768\" \/>\n\t<meta property=\"og:image:height\" content=\"468\" \/>\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\\\/2449\\\/sys-key_encryptions\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2449\\\/sys-key_encryptions\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"The sys.key_encryptions Catalog View\",\"datePublished\":\"2014-06-23T16:00:52+00:00\",\"dateModified\":\"2014-11-13T16:58:22+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2449\\\/sys-key_encryptions\\\/\"},\"wordCount\":369,\"commentCount\":1,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2449\\\/sys-key_encryptions\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/06\\\/sys.key_encryptions_in_action.jpg\",\"keywords\":[\"Cryptography\",\"CV\",\"security\",\"SQL Server\",\"symmetric key\",\"symmetric key encryption\"],\"articleSection\":[\"Cryptography\",\"DMVs & CVs\",\"General\",\"Security\",\"Security\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2449\\\/sys-key_encryptions\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2449\\\/sys-key_encryptions\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2449\\\/sys-key_encryptions\\\/\",\"name\":\"The sys.key_encryptions Catalog View - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2449\\\/sys-key_encryptions\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2449\\\/sys-key_encryptions\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/06\\\/sys.key_encryptions_in_action.jpg\",\"datePublished\":\"2014-06-23T16:00:52+00:00\",\"dateModified\":\"2014-11-13T16:58:22+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"Do you have a symmetric key in SQL Server but forgot how it is encrypted? Discover how you can use the sys.key_encryptions catalog view to find out.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2449\\\/sys-key_encryptions\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2449\\\/sys-key_encryptions\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2449\\\/sys-key_encryptions\\\/#primaryimage\",\"url\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/06\\\/sys.key_encryptions_in_action.jpg\",\"contentUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/06\\\/sys.key_encryptions_in_action.jpg\",\"width\":768,\"height\":468,\"caption\":\"The sys.key_encryptions Catalog View in Action\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2449\\\/sys-key_encryptions\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"The sys.key_encryptions Catalog View\"}]},{\"@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":"The sys.key_encryptions Catalog View - sqlity.net","description":"Do you have a symmetric key in SQL Server but forgot how it is encrypted? Discover how you can use the sys.key_encryptions catalog view to find out.","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\/2449\/sys-key_encryptions\/","og_locale":"en_US","og_type":"article","og_title":"The sys.key_encryptions Catalog View - sqlity.net","og_description":"Do you have a symmetric key in SQL Server but forgot how it is encrypted? Discover how you can use the sys.key_encryptions catalog view to find out.","og_url":"https:\/\/sqlity.net\/en\/2449\/sys-key_encryptions\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-06-23T16:00:52+00:00","article_modified_time":"2014-11-13T16:58:22+00:00","og_image":[{"width":768,"height":468,"url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/06\/sys.key_encryptions_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\/2449\/sys-key_encryptions\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/2449\/sys-key_encryptions\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"The sys.key_encryptions Catalog View","datePublished":"2014-06-23T16:00:52+00:00","dateModified":"2014-11-13T16:58:22+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/2449\/sys-key_encryptions\/"},"wordCount":369,"commentCount":1,"image":{"@id":"https:\/\/sqlity.net\/en\/2449\/sys-key_encryptions\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/06\/sys.key_encryptions_in_action.jpg","keywords":["Cryptography","CV","security","SQL Server","symmetric key","symmetric key encryption"],"articleSection":["Cryptography","DMVs & CVs","General","Security","Security"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/2449\/sys-key_encryptions\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/2449\/sys-key_encryptions\/","url":"https:\/\/sqlity.net\/en\/2449\/sys-key_encryptions\/","name":"The sys.key_encryptions Catalog View - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/2449\/sys-key_encryptions\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/2449\/sys-key_encryptions\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/06\/sys.key_encryptions_in_action.jpg","datePublished":"2014-06-23T16:00:52+00:00","dateModified":"2014-11-13T16:58:22+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"Do you have a symmetric key in SQL Server but forgot how it is encrypted? Discover how you can use the sys.key_encryptions catalog view to find out.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/2449\/sys-key_encryptions\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/2449\/sys-key_encryptions\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/2449\/sys-key_encryptions\/#primaryimage","url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/06\/sys.key_encryptions_in_action.jpg","contentUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/06\/sys.key_encryptions_in_action.jpg","width":768,"height":468,"caption":"The sys.key_encryptions Catalog View in Action"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/2449\/sys-key_encryptions\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"The sys.key_encryptions Catalog View"}]},{"@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\/06\/sys.key_encryptions_in_action.jpg","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-Dv","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2449","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=2449"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2449\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media\/2450"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=2449"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=2449"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=2449"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}