{"id":2520,"date":"2014-08-04T13:00:06","date_gmt":"2014-08-04T17:00:06","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=2520"},"modified":"2014-11-13T12:17:26","modified_gmt":"2014-11-13T17:17:26","slug":"temporary-symmetric-key-conflicts","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/2520\/temporary-symmetric-key-conflicts\/","title":{"rendered":"Temporary Symmetric Key Conflicts"},"content":{"rendered":"
\n

\nOne of the big advantages of temporary tables is that each process has its own private copy of it. A procedure can create a temporary table and store data in it and you do not have to worry about this data being visible or being tainted with by other processes, not even processes that execute that exact same procedure at the same time. Temporary tables are therefore a great tool to deal with larger amounts of transient data in concurrent environments.\n<\/p>\n

\nTemporary symmetric keys are set up in a very similar way and you would expect the same concurrency advantages from them. But, as you will soon see, there is a minor problem.\n<\/p>\n

Temporary Symmetric Keys and Re-Creatable Keys<\/h3>\n

\nSQL Server allow symmetric keys to be created from a KEY_SOURCE<\/span> parameter. Every time the CREATE SYMMETRIC KEY<\/span> statement is called with the same KEY_SOURCE<\/span> parameter, the same symmetric key emerges. While I have a few questions about the algorithms involved<\/a>, this is a great way to encrypt data based on a passphrase while still being able to choose the algorithm used. The ENCRYPTBYPASSPHRASE<\/span> function on the other hand always uses the by now a little dated Triple-DES algorithm.\n<\/p>\n

\nNow, it would be a great workflow for a CRUD procedure to create a temporary symmetric key based on the provided @passphrase parameter, encrypt or decrypt the data and then drop the key. That way, no information required to decrypt the stored data is persisted within the database (not even in encrypted form), which is a requirement in certain circumstances.\n<\/p>\n

The Problematic Identity Value<\/h3>\n

\nSQL Server does not provide a method to specify the symmetric key on decryption. Instead, the key GUID is stored within every encrypted value. When an encrypted value is passed to the DECRYPTBYKEY<\/span> function, that embedded GUID is used to find the required key. One bit in the GUID value is reserved to indicate that the key is a temporary key, so SQL Server know to look in tempdb<\/span> for those keys.\n<\/p>\n

\nTo make that lookup work, the GUID needs to be unique. That uniqueness is enforced within the SQL Server Key Storage on a per database basis. And that particular unique constraint is now providing a problem to us.\n<\/p>\n

\nTo make the temporary key scenario described above work, the temporary key has to not only stem from the same passphrase, but also has to have the same GUID. The GUID is calculated based on the supplied IDENTITY_VALUE<\/span> in the CREATE SYMMETRIC KEY<\/span> statement and as long as you pass the same value there, the created key will have the same GUID. That works all as expected as long as only one of those temporary keys is created at any given time. But if you try to execute the same CREATE SYMMETRIC KEY<\/span> statement from within two separate connections at the same time, one of them will error.\n<\/p>\n

\nTo see that happening, you can use this example statement:\n<\/p>\n

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

\nWhen executed in two query windows at the same time, you will get a result like this:\n<\/p>\n

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

\nThe exact error message is below:\n<\/p>\n

\n[sourcecode]\nMsg 15282, Level 16, State 1, Line 8
\nA key with name '#temp' or user defined unique identifier already exists or you do not have permissions to create it.
\n[\/sourcecode]\n<\/div>\n

\nThat this problem is in fact caused by the identity value, which is not immediately obvious from the error, you can see when you run the same test again with different IDENTITY_VALUE<\/span> parameters:\n<\/p>\n

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

Summary<\/h3>\n

\nWhile temporary symmetric keys are in theory a great tool for keys that cannot be permanently stored in the database, the requirement to have matching GUIDs makes them unusable in concurrent environments.\n<\/p>\n

\nLet me know you thoughts on this in the comments below.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

Temporary symmetric keys are a great idea. However, SQL Server’s current implementation falls apart based on a requirement that makes them all but usable.<\/p>\n

[more…]<\/a><\/p>\n","protected":false},"author":3,"featured_media":2521,"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_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,212],"tags":[273,38,15,140,141,213],"class_list":["post-2520","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":"\nTemporary Symmetric Key Conflicts - sqlity.net<\/title>\n<meta name=\"description\" content=\"Temporary symmetric keys are a great idea. However, SQL Server's current implementation falls apart based on a requirement that makes them all but usable.\" \/>\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\/2520\/temporary-symmetric-key-conflicts\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Temporary Symmetric Key Conflicts - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"Temporary symmetric keys are a great idea. However, SQL Server's current implementation falls apart based on a requirement that makes them all but usable.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/2520\/temporary-symmetric-key-conflicts\/\" \/>\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-08-04T17:00:06+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T17:17:26+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/08\/conflicting_temporary_symmetric_keys.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\\\/2520\\\/temporary-symmetric-key-conflicts\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2520\\\/temporary-symmetric-key-conflicts\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"Temporary Symmetric Key Conflicts\",\"datePublished\":\"2014-08-04T17:00:06+00:00\",\"dateModified\":\"2014-11-13T17:17:26+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2520\\\/temporary-symmetric-key-conflicts\\\/\"},\"wordCount\":668,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2520\\\/temporary-symmetric-key-conflicts\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/08\\\/conflicting_temporary_symmetric_keys.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\\\/2520\\\/temporary-symmetric-key-conflicts\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2520\\\/temporary-symmetric-key-conflicts\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2520\\\/temporary-symmetric-key-conflicts\\\/\",\"name\":\"Temporary Symmetric Key Conflicts - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2520\\\/temporary-symmetric-key-conflicts\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2520\\\/temporary-symmetric-key-conflicts\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/08\\\/conflicting_temporary_symmetric_keys.jpg\",\"datePublished\":\"2014-08-04T17:00:06+00:00\",\"dateModified\":\"2014-11-13T17:17:26+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"Temporary symmetric keys are a great idea. However, SQL Server's current implementation falls apart based on a requirement that makes them all but usable.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2520\\\/temporary-symmetric-key-conflicts\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2520\\\/temporary-symmetric-key-conflicts\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2520\\\/temporary-symmetric-key-conflicts\\\/#primaryimage\",\"url\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/08\\\/conflicting_temporary_symmetric_keys.jpg\",\"contentUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/08\\\/conflicting_temporary_symmetric_keys.jpg\",\"width\":768,\"height\":468,\"caption\":\"two conflicting temporary symmetric keys\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2520\\\/temporary-symmetric-key-conflicts\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Temporary Symmetric Key Conflicts\"}]},{\"@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":"Temporary Symmetric Key Conflicts - sqlity.net","description":"Temporary symmetric keys are a great idea. However, SQL Server's current implementation falls apart based on a requirement that makes them all but usable.","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\/2520\/temporary-symmetric-key-conflicts\/","og_locale":"en_US","og_type":"article","og_title":"Temporary Symmetric Key Conflicts - sqlity.net","og_description":"Temporary symmetric keys are a great idea. However, SQL Server's current implementation falls apart based on a requirement that makes them all but usable.","og_url":"https:\/\/sqlity.net\/en\/2520\/temporary-symmetric-key-conflicts\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-08-04T17:00:06+00:00","article_modified_time":"2014-11-13T17:17:26+00:00","og_image":[{"width":768,"height":468,"url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/08\/conflicting_temporary_symmetric_keys.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\/2520\/temporary-symmetric-key-conflicts\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/2520\/temporary-symmetric-key-conflicts\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"Temporary Symmetric Key Conflicts","datePublished":"2014-08-04T17:00:06+00:00","dateModified":"2014-11-13T17:17:26+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/2520\/temporary-symmetric-key-conflicts\/"},"wordCount":668,"commentCount":0,"image":{"@id":"https:\/\/sqlity.net\/en\/2520\/temporary-symmetric-key-conflicts\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/08\/conflicting_temporary_symmetric_keys.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\/2520\/temporary-symmetric-key-conflicts\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/2520\/temporary-symmetric-key-conflicts\/","url":"https:\/\/sqlity.net\/en\/2520\/temporary-symmetric-key-conflicts\/","name":"Temporary Symmetric Key Conflicts - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/2520\/temporary-symmetric-key-conflicts\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/2520\/temporary-symmetric-key-conflicts\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/08\/conflicting_temporary_symmetric_keys.jpg","datePublished":"2014-08-04T17:00:06+00:00","dateModified":"2014-11-13T17:17:26+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"Temporary symmetric keys are a great idea. However, SQL Server's current implementation falls apart based on a requirement that makes them all but usable.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/2520\/temporary-symmetric-key-conflicts\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/2520\/temporary-symmetric-key-conflicts\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/2520\/temporary-symmetric-key-conflicts\/#primaryimage","url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/08\/conflicting_temporary_symmetric_keys.jpg","contentUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/08\/conflicting_temporary_symmetric_keys.jpg","width":768,"height":468,"caption":"two conflicting temporary symmetric keys"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/2520\/temporary-symmetric-key-conflicts\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"Temporary Symmetric Key Conflicts"}]},{"@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\/08\/conflicting_temporary_symmetric_keys.jpg","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-EE","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2520","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=2520"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2520\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media\/2521"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=2520"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=2520"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=2520"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}