{"id":2500,"date":"2014-07-25T11:30:23","date_gmt":"2014-07-25T15:30:23","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=2500"},"modified":"2014-11-13T12:18:49","modified_gmt":"2014-11-13T17:18:49","slug":"temporary-symmetric-key","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/2500\/temporary-symmetric-key\/","title":{"rendered":"How to Create Temporary Symmetric Keys"},"content":{"rendered":"
\n

\nIn some situations, you might not want to keep the key to your encrypted data in your database, but rather re-create it every time you need access to the data. For that very purpose, SQL Server offers temporary keys.\n<\/p>\n

A Temporary Symmetric Key Example<\/h3>\n

\nTemporary symmetric keys are created the same way you would create any other temporary object. You just designate the key as temporary by prefixing its name with a #<\/span> sign:\n<\/p>\n

\n[sql]\nCREATE SYMMETRIC KEY #temp WITH KEY_SOURCE = '%%%%%%%%%%', IDENTITY_VALUE = '1', ALGORITHM = AES_256 ENCRYPTION BY PASSWORD='**********';
\n[\/sql]\n<\/div>\n

\nTemporary keys, like to temporary tables, are not stored in the current database but instead in tempdb<\/span>. To confirm that, the following script creates two similar keys, one temporary and one non-temporary. It then queries the sys.symmetric_keys<\/span> catalog view<\/a> in both the current database and tempdb<\/span> for all keys that contain the four letters \"temp\" in their name:\n<\/p>\n

\n[sql]\nIF(KEY_ID('#temp') IS NOT NULL)DROP SYMMETRIC KEY #temp;
\nIF(KEY_ID('temp') IS NOT NULL)DROP SYMMETRIC KEY temp;
\nGO
\nCREATE SYMMETRIC KEY #temp WITH KEY_SOURCE = '%%%%%%%%%%', IDENTITY_VALUE = '1', ALGORITHM = AES_256 ENCRYPTION BY PASSWORD='**********';
\nGO
\nCREATE SYMMETRIC KEY temp WITH KEY_SOURCE = '%%%%%%%%%%', IDENTITY_VALUE = '1', ALGORITHM = AES_256 ENCRYPTION BY PASSWORD='**********';
\nGO
\nSELECT 'tempdb' AS database_name,SK.symmetric_key_id,SK.name,SK.key_guid FROM tempdb.sys.symmetric_keys AS SK WHERE SK.name LIKE '%temp%'
\n UNION ALL
\nSELECT DB_NAME() AS database_name,SK.symmetric_key_id,SK.name,SK.key_guid FROM sys.symmetric_keys AS SK WHERE SK.name LIKE '%temp%';
\n[\/sql]\n<\/div>\n

\nWhen you execute this script, you will get a result like this:\n<\/p>\n

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

\nAs predicted, the temporary symmetric key is stored in tempdb<\/span> whereas the \"normal\" symmetric key lives in the current database. You can also see that the name of the temporary key is augmented with some form of session information. The full name of the temporary key in this example is below.\n<\/p>\n

\n[sourcecode]\n#temp___________________________________________________________________________________________________________000000010000F736
\n[\/sourcecode]\n<\/div>\n

\nHow that number at the end of the name is generated in not documented and unclear to me. It is neither the SPID, the symmetric_key_id<\/span> nor a part of the key_guid<\/span>, but it might be something like a checksum over some or all of these values.\n<\/p>\n

A Temporary Warning<\/h3>\n

\nLike temporary tables, a temporary key goes away when the connection is closed. That means you need to make sure that you can recreate the key, for example by using the KEY_SOURCE<\/span> and IDENTITY_VALUE<\/span> parameters<\/a>, as in the example above.\n<\/p>\n

\nSQL Server does not stop you from creating an auto-generated temporary symmetric key like this:\n<\/p>\n

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

\nBecause you can neither back that key up nor reliably recreate it, encrypting data with this key is about as good as deleting the data. You will never be able to get back to it as soon as the current connection closes.\n<\/p>\n

Summary<\/h3>\n

\nWe can create temporary symmetric keys in SQL Server. Temporary keys behave similar to temporary tables: They are stored in tempdb<\/span> instead of the current database and they are automatically dropped as soon as the connection in which they were created closes. For that reason, care needs to be taken to only generate re-creatable temporary keys.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

Temporary symmetric keys in SQL Server behave similar to temp tables. In particular, they are stored in tempdb and automatically go away at the end of the session. Make sure however that you do not get caught creating a non-recreatable temporary symmetric key…<\/p>\n

[more…]<\/a><\/p>\n","protected":false},"author":3,"featured_media":2501,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_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}},"categories":[32,5,34,212],"tags":[273,38,15,140,141,213],"class_list":["post-2500","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-cryptography","category-general","category-security","category-temporary-objects","tag-cryptography","tag-security-2","tag-sql-server","tag-symmetric-key","tag-symmetric-key-encryption","tag-temporary-symmetric-key"],"acf":[],"yoast_head":"\nHow to Create Temporary Symmetric Keys - sqlity.net<\/title>\n<meta name=\"description\" content=\"Temporary symmetric keys in SQL Server behave similar to temp tables. However, do not get caught creating a non-recreatable temporary symmetric 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\/2500\/temporary-symmetric-key\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to Create Temporary Symmetric Keys - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"Temporary symmetric keys in SQL Server behave similar to temp tables. However, do not get caught creating a non-recreatable temporary symmetric key...\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/2500\/temporary-symmetric-key\/\" \/>\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-07-25T15:30:23+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T17:18:49+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/07\/an_example_temporary_symmetric_key.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=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2500\\\/temporary-symmetric-key\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2500\\\/temporary-symmetric-key\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"How to Create Temporary Symmetric Keys\",\"datePublished\":\"2014-07-25T15:30:23+00:00\",\"dateModified\":\"2014-11-13T17:18:49+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2500\\\/temporary-symmetric-key\\\/\"},\"wordCount\":582,\"commentCount\":1,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2500\\\/temporary-symmetric-key\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/07\\\/an_example_temporary_symmetric_key.jpg\",\"keywords\":[\"Cryptography\",\"security\",\"SQL Server\",\"symmetric key\",\"symmetric key encryption\",\"temporary symmetric key\"],\"articleSection\":[\"Cryptography\",\"General\",\"Security\",\"Temporary Objects\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2500\\\/temporary-symmetric-key\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2500\\\/temporary-symmetric-key\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2500\\\/temporary-symmetric-key\\\/\",\"name\":\"How to Create Temporary Symmetric Keys - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2500\\\/temporary-symmetric-key\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2500\\\/temporary-symmetric-key\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/07\\\/an_example_temporary_symmetric_key.jpg\",\"datePublished\":\"2014-07-25T15:30:23+00:00\",\"dateModified\":\"2014-11-13T17:18:49+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"Temporary symmetric keys in SQL Server behave similar to temp tables. However, do not get caught creating a non-recreatable temporary symmetric key...\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2500\\\/temporary-symmetric-key\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2500\\\/temporary-symmetric-key\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2500\\\/temporary-symmetric-key\\\/#primaryimage\",\"url\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/07\\\/an_example_temporary_symmetric_key.jpg\",\"contentUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/07\\\/an_example_temporary_symmetric_key.jpg\",\"width\":768,\"height\":468,\"caption\":\"an example temporary symmetric key\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2500\\\/temporary-symmetric-key\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to Create Temporary Symmetric Keys\"}]},{\"@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 Create Temporary Symmetric Keys - sqlity.net","description":"Temporary symmetric keys in SQL Server behave similar to temp tables. However, do not get caught creating a non-recreatable temporary symmetric 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\/2500\/temporary-symmetric-key\/","og_locale":"en_US","og_type":"article","og_title":"How to Create Temporary Symmetric Keys - sqlity.net","og_description":"Temporary symmetric keys in SQL Server behave similar to temp tables. However, do not get caught creating a non-recreatable temporary symmetric key...","og_url":"https:\/\/sqlity.net\/en\/2500\/temporary-symmetric-key\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-07-25T15:30:23+00:00","article_modified_time":"2014-11-13T17:18:49+00:00","og_image":[{"width":768,"height":468,"url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/07\/an_example_temporary_symmetric_key.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\/2500\/temporary-symmetric-key\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/2500\/temporary-symmetric-key\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"How to Create Temporary Symmetric Keys","datePublished":"2014-07-25T15:30:23+00:00","dateModified":"2014-11-13T17:18:49+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/2500\/temporary-symmetric-key\/"},"wordCount":582,"commentCount":1,"image":{"@id":"https:\/\/sqlity.net\/en\/2500\/temporary-symmetric-key\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/07\/an_example_temporary_symmetric_key.jpg","keywords":["Cryptography","security","SQL Server","symmetric key","symmetric key encryption","temporary symmetric key"],"articleSection":["Cryptography","General","Security","Temporary Objects"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/2500\/temporary-symmetric-key\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/2500\/temporary-symmetric-key\/","url":"https:\/\/sqlity.net\/en\/2500\/temporary-symmetric-key\/","name":"How to Create Temporary Symmetric Keys - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/2500\/temporary-symmetric-key\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/2500\/temporary-symmetric-key\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/07\/an_example_temporary_symmetric_key.jpg","datePublished":"2014-07-25T15:30:23+00:00","dateModified":"2014-11-13T17:18:49+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"Temporary symmetric keys in SQL Server behave similar to temp tables. However, do not get caught creating a non-recreatable temporary symmetric key...","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/2500\/temporary-symmetric-key\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/2500\/temporary-symmetric-key\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/2500\/temporary-symmetric-key\/#primaryimage","url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/07\/an_example_temporary_symmetric_key.jpg","contentUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/07\/an_example_temporary_symmetric_key.jpg","width":768,"height":468,"caption":"an example temporary symmetric key"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/2500\/temporary-symmetric-key\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"How to Create Temporary Symmetric Keys"}]},{"@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\/07\/an_example_temporary_symmetric_key.jpg","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-Ek","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2500","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=2500"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2500\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media\/2501"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=2500"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=2500"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=2500"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}