{"id":2527,"date":"2014-08-08T15:10:26","date_gmt":"2014-08-08T19:10:26","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=2527"},"modified":"2014-11-13T12:17:04","modified_gmt":"2014-11-13T17:17:04","slug":"encryptbypassphrase","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/2527\/encryptbypassphrase\/","title":{"rendered":"How to Encrypt a Column with a Passphrase"},"content":{"rendered":"
\n

\nSometimes it is appropriate to go with the simplest solution, even if it is not the most secure. SQL Server provides a sophisticated key management solution. But as with most powerful tools, its use is not necessarily trivial. In addition, just for those cases where a full-fledged key-managed encryption solution seems overkill, SQL Server provides a simple encryption solution that is based on a passphrase (a long password) instead of keys.\n<\/p>\n

The ENCRYPTBYPASSPHRASE Function<\/h3>\n

\nThe ENCRYPTBYPASSPHRASE<\/span> function allows us to encrypt any string or varbinary value, up to 7975 bytes in length. It takes two parameters, a @passphrase<\/span> and the @cleartext<\/span> and returns a VARBINARY(8000)<\/span> value that contains the encrypted values.\n<\/p>\n

\nThere are a few problems with this function that you need to be aware of. The passphrase is run through a key derivation function to generate a 128-bit key. Which key derivation function is used is not published, so we cannot tell how secure it actually is. The key is then used to encrypt the provided data using the Triple-DES algorithm. How that is done exactly is also unclear, as the Triple DES algorithm<\/a> natively can only handle either 112-bit or 168-bit keys. Finally, the NIST ran a competition to create a new standard encryption algorithm because DES and its derivations including Triple DES were considered not secure enough anymore. That competition resulted in AES being announced and recommended as the new encryption standard. That would not be too bad, if it happened yesterday. However, the new AES algorithm was announced at the end of 2001. Since then computing power has increased by a >factor of 128<\/a> and that does not even calculate in the abilities that cloud computing now provides for little money.\n<\/p>\n

\nAll that does not mean that ENCRYPTBYPASSPHRASE<\/span> is not useful; it is still based on a fairly strong encryption method. However, I would not rely on it to encrypt my customer's credit card numbers or PHI anymore.\n<\/p>\n

\nSo, let us look at an example. I have the following table:\n<\/p>\n

\n[sql]\nCREATE TABLE dbo.encrypted
\n(
\n id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
\n data VARBINARY(8000)
\n);
\n[\/sql]\n<\/div>\n

\nIn real life, you probably would have additional columns, but for simplicity, I just went with two, the primary key and the data column. The id<\/span> column will stay unencrypted. (There is never a reason to encrypt a surrogate key column and if you do, the result will most likely be disastrous.) The data<\/span> column is of type VARBINARY(8000)<\/span>, which matches the output of the ENCRYPTBYPASSPHRASE<\/span> function.\n<\/p>\n

\nTo insert data into this column you can directly call the ENCRYPTBYPASSPHRASE<\/span> function in the INSERT<\/span> statement:\n<\/p>\n

\n[sql]\nINSERT INTO dbo.encrypted(data)
\n VALUES(ENCRYPTBYPASSPHRASE('**********','Hello World!'));
\n[\/sql]\n<\/div>\n

\nIn case that is not immediately obvious, the first parameter is the passphrase and the second provides the clear text value.\n<\/p>\n

\nThe result of the encryption can be seen with a straight forward SELECT<\/span>:\n<\/p>\n

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

\nThe twelve characters of clear text were transformed into 36 bytes of gobbledygook (that is a technical term).\n<\/p>\n

\nENCRYPTBYPASSPHRASE<\/span> only takes CHAR<\/span> and BINARY<\/span> data types as input (including their N-<\/span> and VAR-<\/span> variants). Any other data type like a DATE<\/span>, for example to encrypt a birthdate, you have to explicitly convert to a binary value first.\n<\/p>\n

Summary<\/h3>\n

\nENCRYPTBYPASSPHRASE<\/span> provides a quick and simple way to encrypt data columns in SQL Server. However, its use of the Triple DES algorithm as well as a few documentation gaps make it inadequate for important data like credit card information or social security numbers.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

If you are searching for a quick and dirty way to encrypt data in SQL Server, the ENCRYPTBYPASSPHRASE function might be just what you are looking for.<\/p>\n

[more…]<\/a><\/p>\n","protected":false},"author":3,"featured_media":2529,"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":[273,38,15,141],"class_list":["post-2527","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-cryptography","category-general","category-security","tag-cryptography","tag-security-2","tag-sql-server","tag-symmetric-key-encryption"],"acf":[],"yoast_head":"\nHow to Encrypt a Column with a Passphrase - sqlity.net<\/title>\n<meta name=\"description\" content=\"If you are searching for a quick and dirty way to encrypt data in SQL Server, the ENCRYPTBYPASSPHRASE function might be just what you are looking for.\" \/>\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\/2527\/encryptbypassphrase\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to Encrypt a Column with a Passphrase - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"If you are searching for a quick and dirty way to encrypt data in SQL Server, the ENCRYPTBYPASSPHRASE function might be just what you are looking for.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/2527\/encryptbypassphrase\/\" \/>\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-08T19:10:26+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T17:17:04+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/08\/ENCRYPTBYPASSPHRASE_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=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2527\\\/encryptbypassphrase\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2527\\\/encryptbypassphrase\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"How to Encrypt a Column with a Passphrase\",\"datePublished\":\"2014-08-08T19:10:26+00:00\",\"dateModified\":\"2014-11-13T17:17:04+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2527\\\/encryptbypassphrase\\\/\"},\"wordCount\":623,\"commentCount\":2,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2527\\\/encryptbypassphrase\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/08\\\/ENCRYPTBYPASSPHRASE_in_action.jpg\",\"keywords\":[\"Cryptography\",\"security\",\"SQL Server\",\"symmetric key encryption\"],\"articleSection\":[\"Cryptography\",\"General\",\"Security\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2527\\\/encryptbypassphrase\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2527\\\/encryptbypassphrase\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2527\\\/encryptbypassphrase\\\/\",\"name\":\"How to Encrypt a Column with a Passphrase - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2527\\\/encryptbypassphrase\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2527\\\/encryptbypassphrase\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/08\\\/ENCRYPTBYPASSPHRASE_in_action.jpg\",\"datePublished\":\"2014-08-08T19:10:26+00:00\",\"dateModified\":\"2014-11-13T17:17:04+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"If you are searching for a quick and dirty way to encrypt data in SQL Server, the ENCRYPTBYPASSPHRASE function might be just what you are looking for.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2527\\\/encryptbypassphrase\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2527\\\/encryptbypassphrase\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2527\\\/encryptbypassphrase\\\/#primaryimage\",\"url\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/08\\\/ENCRYPTBYPASSPHRASE_in_action.jpg\",\"contentUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/08\\\/ENCRYPTBYPASSPHRASE_in_action.jpg\",\"width\":768,\"height\":468,\"caption\":\"How to Encrypt a Column with a Passphrase http:\\\/\\\/bit.ly\\\/1sFDo6B A quick and dirty way of encryption in #SqlServer. #SqlBlog #security\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2527\\\/encryptbypassphrase\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to Encrypt a Column with a Passphrase\"}]},{\"@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 Encrypt a Column with a Passphrase - sqlity.net","description":"If you are searching for a quick and dirty way to encrypt data in SQL Server, the ENCRYPTBYPASSPHRASE function might be just what you are looking for.","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\/2527\/encryptbypassphrase\/","og_locale":"en_US","og_type":"article","og_title":"How to Encrypt a Column with a Passphrase - sqlity.net","og_description":"If you are searching for a quick and dirty way to encrypt data in SQL Server, the ENCRYPTBYPASSPHRASE function might be just what you are looking for.","og_url":"https:\/\/sqlity.net\/en\/2527\/encryptbypassphrase\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-08-08T19:10:26+00:00","article_modified_time":"2014-11-13T17:17:04+00:00","og_image":[{"width":768,"height":468,"url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/08\/ENCRYPTBYPASSPHRASE_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":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/2527\/encryptbypassphrase\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/2527\/encryptbypassphrase\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"How to Encrypt a Column with a Passphrase","datePublished":"2014-08-08T19:10:26+00:00","dateModified":"2014-11-13T17:17:04+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/2527\/encryptbypassphrase\/"},"wordCount":623,"commentCount":2,"image":{"@id":"https:\/\/sqlity.net\/en\/2527\/encryptbypassphrase\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/08\/ENCRYPTBYPASSPHRASE_in_action.jpg","keywords":["Cryptography","security","SQL Server","symmetric key encryption"],"articleSection":["Cryptography","General","Security"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/2527\/encryptbypassphrase\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/2527\/encryptbypassphrase\/","url":"https:\/\/sqlity.net\/en\/2527\/encryptbypassphrase\/","name":"How to Encrypt a Column with a Passphrase - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/2527\/encryptbypassphrase\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/2527\/encryptbypassphrase\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/08\/ENCRYPTBYPASSPHRASE_in_action.jpg","datePublished":"2014-08-08T19:10:26+00:00","dateModified":"2014-11-13T17:17:04+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"If you are searching for a quick and dirty way to encrypt data in SQL Server, the ENCRYPTBYPASSPHRASE function might be just what you are looking for.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/2527\/encryptbypassphrase\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/2527\/encryptbypassphrase\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/2527\/encryptbypassphrase\/#primaryimage","url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/08\/ENCRYPTBYPASSPHRASE_in_action.jpg","contentUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/08\/ENCRYPTBYPASSPHRASE_in_action.jpg","width":768,"height":468,"caption":"How to Encrypt a Column with a Passphrase http:\/\/bit.ly\/1sFDo6B A quick and dirty way of encryption in #SqlServer. #SqlBlog #security"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/2527\/encryptbypassphrase\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"How to Encrypt a Column with a Passphrase"}]},{"@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\/ENCRYPTBYPASSPHRASE_in_action.jpg","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-EL","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2527","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=2527"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2527\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media\/2529"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=2527"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=2527"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=2527"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}