{"id":2530,"date":"2014-08-11T13:00:03","date_gmt":"2014-08-11T17:00:03","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=2530"},"modified":"2014-11-13T12:16:46","modified_gmt":"2014-11-13T17:16:46","slug":"decryptbypassphrase","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/2530\/decryptbypassphrase\/","title":{"rendered":"How to Access Passphrase-Encrypted Data"},"content":{"rendered":"
\n

\nOn Friday we used the ENCRYPTBYPASSPHRASE<\/span> function<\/a> to encrypt the data in a column. Usually if you encrypt data, you need a way to later decrypt it too. That is where the DECRYPTBYPASSPHRASE<\/span> function comes in.\n<\/p>\n

A DECRYPTBYPASSPHRASE Example<\/h3>\n

\nIf you followed the example on Friday, you already have the table we are going to work with today. If not, you can create it with the following script:\n<\/p>\n

\n[sql]\nCREATE TABLE dbo.encrypted
\n(
\n id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
\n data VARBINARY(8000)
\n);
\nGO
\nINSERT INTO dbo.encrypted(data)
\n VALUES(ENCRYPTBYPASSPHRASE('**********','Hello World!'));
\n[\/sql]\n<\/div>\n

\nTo use the DECRYPTBYPASSPHRASE<\/span> function you need to supply the passphrase itself and the encrypted value. Selecting from our table therefore would look like this:\n<\/p>\n

\n[sql]\nSELECT id,DECRYPTBYPASSPHRASE('**********',data)
\n FROM dbo.encrypted;
\n[\/sql]\n<\/div>\n

\nThat does decrypt the value correctly; however, the result is probably not what you expected:\n<\/p>\n

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

\nRemember, ENCRYPTBYPASSPHRASE<\/span> returns the encrypted value as a VARBINARY(8000)<\/span> data type. That data type, other than for example SQL_VARIANT<\/span> does not carry any information about the originating data type. Therefore, the DECRYPTBYPASSPHRASE<\/span> also returns a VARBINARY(8000)<\/span> value.\n<\/p>\n

\nTo get that value back into the originating data type, you just have to cast it:\n<\/p>\n

\n[sql]\nSELECT id,CAST(DECRYPTBYPASSPHRASE('**********',data) AS VARCHAR(8000))
\n FROM dbo.encrypted;
\n[\/sql]\n<\/div>\n

\nWith that, the result is readable again:\n<\/p>\n

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

Always Use the Correct Password<\/h3>\n

\nThe DECRYPTBYPASSPHRASE<\/span> function is a little finicky. It requires the two values to match each other. If you for example use the wrong password, it just returns NULL<\/span>.\n<\/p>\n

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

\nDECRYPTBYPASSPHRASE<\/span> shows the same behavior, if the provided cipher text was not created by the ENCRYPTBYPASSPHRASE<\/span> function. That means you can use the returned NULL<\/span> as a reliable indicator that something went wrong. You will never just get garbage back.\n<\/p>\n

Summary<\/h3>\n

The pendant to the ENCRYPTBYPASSPHRASE<\/span> function is the aptly named DECRYPTBYPASSPHRASE<\/span> function. It decrypts cipher text that was created with the former. However, you yourself are responsible to cast the result back into the appropriate data type, as no information about the data type itself is stored with the encrypted value.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

What do you need to do to decrypt data that was encrypted with ENCRYPTBYPASSPHRASE? Read on to see the DECRYPTBYPASSPHRASE function in action.<\/p>\n

[more…]<\/a><\/p>\n","protected":false},"author":3,"featured_media":2533,"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":[],"class_list":["post-2530","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-cryptography","category-general","category-security"],"acf":[],"yoast_head":"\nHow to Access Passphrase-Encrypted Data - sqlity.net<\/title>\n<meta name=\"description\" content=\"What do you need to do to decrypt data that was encrypted with ENCRYPTBYPASSPHRASE? Read on to see the DECRYPTBYPASSPHRASE function in action.\" \/>\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\/2530\/decryptbypassphrase\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to Access Passphrase-Encrypted Data - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"What do you need to do to decrypt data that was encrypted with ENCRYPTBYPASSPHRASE? Read on to see the DECRYPTBYPASSPHRASE function in action.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/2530\/decryptbypassphrase\/\" \/>\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-11T17:00:03+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T17:16:46+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/08\/successful_DECRYPTBYPASSPHRASE_use_with_CAST_to_VARCHAR.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\\\/2530\\\/decryptbypassphrase\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2530\\\/decryptbypassphrase\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"How to Access Passphrase-Encrypted Data\",\"datePublished\":\"2014-08-11T17:00:03+00:00\",\"dateModified\":\"2014-11-13T17:16:46+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2530\\\/decryptbypassphrase\\\/\"},\"wordCount\":381,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2530\\\/decryptbypassphrase\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/08\\\/successful_DECRYPTBYPASSPHRASE_use_with_CAST_to_VARCHAR.jpg\",\"articleSection\":[\"Cryptography\",\"General\",\"Security\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2530\\\/decryptbypassphrase\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2530\\\/decryptbypassphrase\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2530\\\/decryptbypassphrase\\\/\",\"name\":\"How to Access Passphrase-Encrypted Data - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2530\\\/decryptbypassphrase\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2530\\\/decryptbypassphrase\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/08\\\/successful_DECRYPTBYPASSPHRASE_use_with_CAST_to_VARCHAR.jpg\",\"datePublished\":\"2014-08-11T17:00:03+00:00\",\"dateModified\":\"2014-11-13T17:16:46+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"What do you need to do to decrypt data that was encrypted with ENCRYPTBYPASSPHRASE? Read on to see the DECRYPTBYPASSPHRASE function in action.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2530\\\/decryptbypassphrase\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2530\\\/decryptbypassphrase\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2530\\\/decryptbypassphrase\\\/#primaryimage\",\"url\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/08\\\/successful_DECRYPTBYPASSPHRASE_use_with_CAST_to_VARCHAR.jpg\",\"contentUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/08\\\/successful_DECRYPTBYPASSPHRASE_use_with_CAST_to_VARCHAR.jpg\",\"width\":768,\"height\":468,\"caption\":\"Successful DECRYPTBYPASSPHRASE use with CAST to VARCHAR\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2530\\\/decryptbypassphrase\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to Access Passphrase-Encrypted Data\"}]},{\"@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 Access Passphrase-Encrypted Data - sqlity.net","description":"What do you need to do to decrypt data that was encrypted with ENCRYPTBYPASSPHRASE? Read on to see the DECRYPTBYPASSPHRASE function in action.","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\/2530\/decryptbypassphrase\/","og_locale":"en_US","og_type":"article","og_title":"How to Access Passphrase-Encrypted Data - sqlity.net","og_description":"What do you need to do to decrypt data that was encrypted with ENCRYPTBYPASSPHRASE? Read on to see the DECRYPTBYPASSPHRASE function in action.","og_url":"https:\/\/sqlity.net\/en\/2530\/decryptbypassphrase\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-08-11T17:00:03+00:00","article_modified_time":"2014-11-13T17:16:46+00:00","og_image":[{"width":768,"height":468,"url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/08\/successful_DECRYPTBYPASSPHRASE_use_with_CAST_to_VARCHAR.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\/2530\/decryptbypassphrase\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/2530\/decryptbypassphrase\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"How to Access Passphrase-Encrypted Data","datePublished":"2014-08-11T17:00:03+00:00","dateModified":"2014-11-13T17:16:46+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/2530\/decryptbypassphrase\/"},"wordCount":381,"commentCount":0,"image":{"@id":"https:\/\/sqlity.net\/en\/2530\/decryptbypassphrase\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/08\/successful_DECRYPTBYPASSPHRASE_use_with_CAST_to_VARCHAR.jpg","articleSection":["Cryptography","General","Security"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/2530\/decryptbypassphrase\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/2530\/decryptbypassphrase\/","url":"https:\/\/sqlity.net\/en\/2530\/decryptbypassphrase\/","name":"How to Access Passphrase-Encrypted Data - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/2530\/decryptbypassphrase\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/2530\/decryptbypassphrase\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/08\/successful_DECRYPTBYPASSPHRASE_use_with_CAST_to_VARCHAR.jpg","datePublished":"2014-08-11T17:00:03+00:00","dateModified":"2014-11-13T17:16:46+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"What do you need to do to decrypt data that was encrypted with ENCRYPTBYPASSPHRASE? Read on to see the DECRYPTBYPASSPHRASE function in action.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/2530\/decryptbypassphrase\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/2530\/decryptbypassphrase\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/2530\/decryptbypassphrase\/#primaryimage","url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/08\/successful_DECRYPTBYPASSPHRASE_use_with_CAST_to_VARCHAR.jpg","contentUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/08\/successful_DECRYPTBYPASSPHRASE_use_with_CAST_to_VARCHAR.jpg","width":768,"height":468,"caption":"Successful DECRYPTBYPASSPHRASE use with CAST to VARCHAR"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/2530\/decryptbypassphrase\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"How to Access Passphrase-Encrypted Data"}]},{"@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\/successful_DECRYPTBYPASSPHRASE_use_with_CAST_to_VARCHAR.jpg","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-EO","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2530","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=2530"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2530\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media\/2533"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=2530"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=2530"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=2530"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}