{"id":440,"date":"2017-06-06T15:35:38","date_gmt":"2017-06-06T20:35:38","guid":{"rendered":"http:\/\/www.sqlnuggets.com\/?p=440"},"modified":"2017-06-23T15:24:27","modified_gmt":"2017-06-23T20:24:27","slug":"encrypting-passwords-using-encryptbypassphrase","status":"publish","type":"post","link":"https:\/\/sqlnuggets.com\/encrypting-passwords-using-encryptbypassphrase\/","title":{"rendered":"Encrypting Passwords Using EncryptByPassPhrase"},"content":{"rendered":"<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-427\" src=\"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2017\/05\/cyber-security-e1494949546575.jpg\" alt=\"\" width=\"1280\" height=\"592\" srcset=\"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2017\/05\/cyber-security-e1494949546575.jpg 1280w, https:\/\/sqlnuggets.com\/wp-content\/uploads\/2017\/05\/cyber-security-e1494949546575-300x139.jpg 300w, https:\/\/sqlnuggets.com\/wp-content\/uploads\/2017\/05\/cyber-security-e1494949546575-768x355.jpg 768w, https:\/\/sqlnuggets.com\/wp-content\/uploads\/2017\/05\/cyber-security-e1494949546575-1024x474.jpg 1024w\" sizes=\"auto, (max-width: 1280px) 100vw, 1280px\" \/>While storing passwords in a database may be a common practice, storing them properly usually isn&#8217;t so common. \u00a0This is part of a\u00a0<a href=\"https:\/\/sqlnuggets.com\/blog\/tag\/storing-passwords\/\">storing passwords blog series<\/a> where we will examine some\u00a0of the options available for\u00a0storing passwords in a SQL Server database.<\/p>\n<p>To recap the <a href=\"https:\/\/sqlnuggets.com\/blog\/storing-passwords-sql-server-database\/\">introduction to this series<\/a>, when you store a password in a database you basically have 3 choices as to how you are going to do it. \u00a0You can save the password as:<\/p>\n<ul>\n<li>Unencrypted clear text that can be viewed by anyone with read access to the table<\/li>\n<li>Encrypted text that has a need to be decrypted<\/li>\n<li>Strongly encrypted text that will never need to be decrypted<\/li>\n<\/ul>\n<p>In the introduction, we established that storing passwords as an unencrypted clear text string is a really, really, <em>really<\/em> bad idea. \u00a0In this post of our series, we&#8217;re going to address one of the ways you can accomplish the second choice:<\/p>\n<h4>Encrypting text that has a need to be decrypted<\/h4>\n<p>Now, some will argue that you should always store your passwords as strongly encrypted text that will never need to be decrypted, and in most cases I would agree with that. \u00a0However, I have run across situations where being able to decrypt the password is necessary. \u00a0For example, I once worked on an application that connected to dozens of vendor FTP sites to upload\/download files nightly. \u00a0These FTP passwords were stored encrypted in our database, but would have to be retrieved and decrypted and used for authentication to the vendor&#8217;s FTP site.<\/p>\n<p>In cases like that,\u00a0 <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/functions\/encryptbypassphrase-transact-sql\" target=\"_blank\" rel=\"noopener noreferrer\">ENCRYPTBYPASSPHRASE<\/a> (available in SQL Server 2008 and up) offers one of the simplest ways for you to encrypt your passwords in a way that can also be decrypted (by using <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/functions\/decryptbypassphrase-transact-sql\" target=\"_blank\" rel=\"noopener noreferrer\">DECRYPTBYPASSPHRASE<\/a>).<\/p>\n<p>At the very basic, ENCRYPTBYPASSPHRASE requires two mandatory arguments: a passphrase used to generate the encryption key, and the text to be encrypted. \u00a0Notice that it specifies a\u00a0pass<strong>phrase<\/strong>, not pass<strong>word<\/strong>. \u00a0As described in the ENCRYPTBYPASSPHRASE documentation: <em>A passphrase is a password that includes spaces. The advantage of using a passphrase is that it is easier to remember a meaningful phrase or sentence than to remember a comparably long string of characters.<\/em><\/p>\n<p>Many people don&#8217;t realize that you can use a space as a legitimate special character in most passwords. \u00a0By doing this, you can generate a much more secure password sentence (or phrase) instead of a single word. \u00a0So, an example of a passphrase may be something like &#8220;My dog has fleas&#8221;, or &#8220;I would love some ice cream&#8221;, or for us forgetful guys out there &#8220;My wedding anniversary is June 31st&#8221;. \u00a0(although, as explained in this <a href=\"https:\/\/xkcd.com\/936\/\" target=\"_blank\" rel=\"noopener noreferrer\">XKCD\u00a0comic<\/a>, your passphrase really needs to be something a little more random). Just to be clear, a space is not required in your passphrase for ENCRYPTBYPASSPHRASE. \u00a0If you wanted to use a GUID for your passphrase, or a random string such as &#8220;RZgt9$ExYunZO8Zf{9aaef31dc5e5b7f5049f8f082b0a92ca8701bd6baf49d5704ff4650a929dabe3}s8hxKV@lHPxz85CJ&#8221;, you could.<\/p>\n<p>For the examples in this post, I&#8217;m going to use a passphrase given to me by my six year old. When I asked him what his password was, he replied &#8220;I am not going to tell you what my password is!&#8221;. \u00a0That is the passphrase we will use.<\/p>\n<h4>Using\u00a0ENCRYPTBYPASSPHRASE<\/h4>\n<p>The basic syntax is:<br \/>\nENCRYPTBYPASSPHRASE(&#8216;encryption passphrase&#8217;, &#8216;text to encrypt&#8217;)<\/p>\n<p>There are other arguments that can be used with\u00a0ENCRYPTBYPASSPHRASE (see <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/functions\/encryptbypassphrase-transact-sql\" target=\"_blank\" rel=\"noopener noreferrer\">MSDN Doc<\/a>), but for this simple example we are just using the\u00a0two mandatory arguments.<\/p>\n<p>To view the encrypted value of the password &#8220;ABC123&#8221;:<\/p>\n<pre class=\"lang:tsql decode:true \">SELECT ENCRYPTBYPASSPHRASE(N'I am not going to tell you what my password is!', N'ABC123');<\/pre>\n<p>That SELECT statement will return a\u00a0VARBINARY value such as: <em>0x0100000093EEC20B790EF208B1FB631F0AB3028E3A8C196643C4BD578528A0DFAE7AB45B<\/em><\/p>\n<p>It is important to note that the\u00a0VARBINARY value returned from\u00a0ENCRYPTBYPASSPHRASE is <a href=\"https:\/\/en.wikipedia.org\/wiki\/Nondeterministic_algorithm\" target=\"_blank\" rel=\"noopener noreferrer\">nondeterministic<\/a>, meaning that even with the same input it will not generate the same output every time. \u00a0So you can run the exact same SELECT statement 3 times, and get 3 different results. \u00a0Try it:<\/p>\n<pre class=\"lang:tsql decode:true \">SELECT ENCRYPTBYPASSPHRASE(N'I am not going to tell you what my password is!', N'ABC123');\r\nSELECT ENCRYPTBYPASSPHRASE(N'I am not going to tell you what my password is!', N'ABC123');\r\nSELECT ENCRYPTBYPASSPHRASE(N'I am not going to tell you what my password is!', N'ABC123');<\/pre>\n<p>Thankfully, this output has no bearing on using the\u00a0DECRYPTBYPASSPHRASE function. \u00a0As long as you have the correct passphrase,\u00a0DECRYPTBYPASSPHRASE will successfully decrypt any of those\u00a0VARBINARY results to its orignal value. \u00a0I&#8217;m not exactly sure why\/how this works, it&#8217;s just part of the secret sauce and it is what it is.<\/p>\n<h4>Storing An Encrypted Value In A Table<\/h4>\n<p>Now that we know how to encrypt a password string, let&#8217;s take a look at how to store that encrypted value in a table. \u00a0Since the value returned from\u00a0ENCRYPTBYPASSPHRASE is a\u00a0VARBINARY data type, that is how we want to store it since this is also the data type required by DECRYPTBYPASSPHRASE.<\/p>\n<p>The first thing we need to do is determine the size of our encrypted password column. \u00a0The VARBINARY values returned by\u00a0ENCRYPTBYPASSPHRASE can vary in size, with maximum size of 8,000 bytes. \u00a0The size of the returned value is going to depend on the size of the actual password being encrypted. \u00a0For example, if we check the\u00a0<a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/functions\/datalength-transact-sql\" target=\"_blank\" rel=\"noopener noreferrer\">DATALENGTH<\/a> of our above SELECT statement, we will see that the password of\u00a0 &#8216;ABC123&#8217; has a length of 36. \u00a0However, if we change the password to &#8216;ABC123456&#8217; the size of our returned value is 44. \u00a0And, with a password of &#8216;supercalifragilisticexpialidocious&#8217; (it could happen!) we get a size of 92.<\/p>\n<p>Since we already know that our test value has a length of 36 this is the value I am going to use, because I really don&#8217;t want to use VARBINARY(8000) if I don&#8217;t have to. \u00a0If you know what the maximum allowed length of your users passwords are (and you should!), then you can use the answer in this <a href=\"https:\/\/dba.stackexchange.com\/questions\/120929\/how-long-is-the-output-of-encryptbypassphrase-relative-to-the-input\" target=\"_blank\" rel=\"noopener noreferrer\">DBA StackExchange<\/a> post\u00a0to help calculate the size of your\u00a0 ENCRYPTBYPASSPHRASE output, and set your password table column size accordingly.<\/p>\n<p>Here is a simple example of storing our encrypted password:<\/p>\n<pre class=\"lang:tsql decode:true \">CREATE TABLE dbo.Users ([UserName] VARCHAR(50), [Password] VARBINARY(36))\r\n\r\nINSERT INTO dbo.Users ([UserName], [Password])\r\nVALUES ('Homer.Simpson', ENCRYPTBYPASSPHRASE(N'I am not going to tell you what my password is!', N'ABC123'))\r\n\r\nSELECT [UserName], [Password]\r\nFROM dbo.Users<\/pre>\n<h4>Using DECRYPTBYPASSPHRASE<\/h4>\n<p>Now that we have our password encrypted, we need to be able to decrypt it as well. \u00a0This is easily done by using the\u00a0DECRYPTBYPASSPHRASE function with the same passphrase we encrypted our password with.<\/p>\n<pre class=\"lang:tsql decode:true \">SELECT [UserName],\u00a0DECRYPTBYPASSPHRASE(N'I am not going to tell you what my password is!', [Password])\r\nFROM dbo.Users<\/pre>\n<p>And you will see that you get a returned password value of something along the lines of <em>&#8216;0x4100420043003100320033000x410042004300310032003300&#8217;<\/em><\/p>\n<p>Wait..what? \u00a0That&#8217;s not our password!<br \/>\nJust like its\u00a0ENCRYPTBYPASSPHRASE counterpart, DECRYPTBYPASSPHRASE returns a VARBINARY value, which we will have to convert to a usable string. \u00a0This can be done easily by adding a CONVERT function to our SELECT statement.<\/p>\n<pre class=\"lang:tsql decode:true \">SELECT [UserName],\u00a0CONVERT(NVARCHAR, DECRYPTBYPASSPHRASE(N'I am not going to tell you what my password is!', [Password]))\r\nFROM dbo.Users<\/pre>\n<p>Now you should see your decrypted password returned correctly in clear text.<\/p>\n<p>So, at this point, you&#8217;re probably asking the following question: If using ENCRYPTBYPASSPHRASE for password encryption, \u00a0should I use the same passphrase for all of the passwords in my database? \u00a0Probably not. (That really means &#8220;NO!&#8221;) Every encrypted password should have its own unique passphrase, which will need to be stored with (or somehow associated to) that record. \u00a0This is where being able to use a UNIQUEIDENTIFIER comes in handy, because it is much easier to automatically generate a GUID for each password than generating a phrase.<\/p>\n<p><b>The Bottom Line:<\/b><br \/>\nENCRYPTBYPASSPHRASE offers a quick and easy way for you to encrypt text in SQL Server, and can be useful for encrypting passwords if you need to be able to decrypt the passwords later. \u00a0However, if you do not need to decrypt the passwords, you will be much better off using a hash, which we will discuss in a later post in <a href=\"https:\/\/sqlnuggets.com\/blog\/tag\/storing-passwords\/\">this series<\/a>. \u00a0So, while using\u00a0ENCRYPTBYPASSPHRASE to encrypt your passwords may not be the best option, it is still better than <a href=\"https:\/\/sqlnuggets.com\/blog\/storing-passwords-sql-server-database\/\">doing nothing<\/a>!<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>While storing passwords in a database may be a common practice, storing them properly usually isn&#8217;t so common. \u00a0This is part of a\u00a0storing passwords blog series where we will examine &#8230;<\/p>\n","protected":false},"author":2,"featured_media":427,"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":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[34],"tags":[33,51,22],"class_list":["post-440","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-security","tag-encryption","tag-storing-passwords","tag-top-developer-tips"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.5 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Encrypting Passwords Using EncryptByPassPhrase - SQL Nuggets<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"http:\/\/www.sqlnuggets.com\/encrypting-passwords-using-encryptbypassphrase\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Encrypting Passwords Using EncryptByPassPhrase - SQL Nuggets\" \/>\n<meta property=\"og:description\" content=\"While storing passwords in a database may be a common practice, storing them properly usually isn&#8217;t so common. \u00a0This is part of a\u00a0storing passwords blog series where we will examine ...\" \/>\n<meta property=\"og:url\" content=\"http:\/\/www.sqlnuggets.com\/encrypting-passwords-using-encryptbypassphrase\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL Nuggets\" \/>\n<meta property=\"article:published_time\" content=\"2017-06-06T20:35:38+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-06-23T20:24:27+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/www.sqlnuggets.com\/wp-content\/uploads\/2017\/05\/cyber-security-e1494949546575.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1280\" \/>\n\t<meta property=\"og:image:height\" content=\"592\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Eric Cobb\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@cfgears\" \/>\n<meta name=\"twitter:site\" content=\"@sqlnugg\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Eric Cobb\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"7 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/encrypting-passwords-using-encryptbypassphrase\\\/#article\",\"isPartOf\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/encrypting-passwords-using-encryptbypassphrase\\\/\"},\"author\":{\"name\":\"Eric Cobb\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#\\\/schema\\\/person\\\/210536254addbc1b9d2d95dc1448b38a\"},\"headline\":\"Encrypting Passwords Using EncryptByPassPhrase\",\"datePublished\":\"2017-06-06T20:35:38+00:00\",\"dateModified\":\"2017-06-23T20:24:27+00:00\",\"mainEntityOfPage\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/encrypting-passwords-using-encryptbypassphrase\\\/\"},\"wordCount\":1268,\"commentCount\":0,\"publisher\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#organization\"},\"image\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/encrypting-passwords-using-encryptbypassphrase\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2017\\\/05\\\/cyber-security-e1494949546575.jpg\",\"keywords\":[\"Encryption\",\"Storing Passwords\",\"Top Developer Tips\"],\"articleSection\":[\"Security\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"http:\\\/\\\/www.sqlnuggets.com\\\/encrypting-passwords-using-encryptbypassphrase\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/encrypting-passwords-using-encryptbypassphrase\\\/\",\"url\":\"http:\\\/\\\/www.sqlnuggets.com\\\/encrypting-passwords-using-encryptbypassphrase\\\/\",\"name\":\"Encrypting Passwords Using EncryptByPassPhrase - SQL Nuggets\",\"isPartOf\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/encrypting-passwords-using-encryptbypassphrase\\\/#primaryimage\"},\"image\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/encrypting-passwords-using-encryptbypassphrase\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2017\\\/05\\\/cyber-security-e1494949546575.jpg\",\"datePublished\":\"2017-06-06T20:35:38+00:00\",\"dateModified\":\"2017-06-23T20:24:27+00:00\",\"breadcrumb\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/encrypting-passwords-using-encryptbypassphrase\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"http:\\\/\\\/www.sqlnuggets.com\\\/encrypting-passwords-using-encryptbypassphrase\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/encrypting-passwords-using-encryptbypassphrase\\\/#primaryimage\",\"url\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2017\\\/05\\\/cyber-security-e1494949546575.jpg\",\"contentUrl\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2017\\\/05\\\/cyber-security-e1494949546575.jpg\",\"width\":1280,\"height\":592},{\"@type\":\"BreadcrumbList\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/encrypting-passwords-using-encryptbypassphrase\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"http:\\\/\\\/www.sqlnuggets.com\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Encrypting Passwords Using EncryptByPassPhrase\"}]},{\"@type\":\"WebSite\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#website\",\"url\":\"http:\\\/\\\/www.sqlnuggets.com\\\/\",\"name\":\"SQL Nuggets\",\"description\":\"Nuggets Of SQL Server Knowledge\",\"publisher\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"http:\\\/\\\/www.sqlnuggets.com\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#organization\",\"name\":\"SQL Nuggets\",\"url\":\"http:\\\/\\\/www.sqlnuggets.com\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2023\\\/11\\\/website-logo.jpg\",\"contentUrl\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2023\\\/11\\\/website-logo.jpg\",\"width\":320,\"height\":54,\"caption\":\"SQL Nuggets\"},\"image\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#\\\/schema\\\/logo\\\/image\\\/\"},\"sameAs\":[\"https:\\\/\\\/x.com\\\/sqlnugg\",\"https:\\\/\\\/www.linkedin.com\\\/in\\\/ericcobb\\\/\"]},{\"@type\":\"Person\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#\\\/schema\\\/person\\\/210536254addbc1b9d2d95dc1448b38a\",\"name\":\"Eric Cobb\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/3ca1fc0c7054a668e048f09d412cd4ebf89833c4630fbbfccca78a0678a6bdc2?s=96&d=mm&r=pg\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/3ca1fc0c7054a668e048f09d412cd4ebf89833c4630fbbfccca78a0678a6bdc2?s=96&d=mm&r=pg\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/3ca1fc0c7054a668e048f09d412cd4ebf89833c4630fbbfccca78a0678a6bdc2?s=96&d=mm&r=pg\",\"caption\":\"Eric Cobb\"},\"sameAs\":[\"https:\\\/\\\/x.com\\\/cfgears\"],\"url\":\"https:\\\/\\\/sqlnuggets.com\\\/author\\\/eric-cobb\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Encrypting Passwords Using EncryptByPassPhrase - SQL Nuggets","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":"http:\/\/www.sqlnuggets.com\/encrypting-passwords-using-encryptbypassphrase\/","og_locale":"en_US","og_type":"article","og_title":"Encrypting Passwords Using EncryptByPassPhrase - SQL Nuggets","og_description":"While storing passwords in a database may be a common practice, storing them properly usually isn&#8217;t so common. \u00a0This is part of a\u00a0storing passwords blog series where we will examine ...","og_url":"http:\/\/www.sqlnuggets.com\/encrypting-passwords-using-encryptbypassphrase\/","og_site_name":"SQL Nuggets","article_published_time":"2017-06-06T20:35:38+00:00","article_modified_time":"2017-06-23T20:24:27+00:00","og_image":[{"width":1280,"height":592,"url":"http:\/\/www.sqlnuggets.com\/wp-content\/uploads\/2017\/05\/cyber-security-e1494949546575.jpg","type":"image\/jpeg"}],"author":"Eric Cobb","twitter_card":"summary_large_image","twitter_creator":"@cfgears","twitter_site":"@sqlnugg","twitter_misc":{"Written by":"Eric Cobb","Est. reading time":"7 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"http:\/\/www.sqlnuggets.com\/encrypting-passwords-using-encryptbypassphrase\/#article","isPartOf":{"@id":"http:\/\/www.sqlnuggets.com\/encrypting-passwords-using-encryptbypassphrase\/"},"author":{"name":"Eric Cobb","@id":"http:\/\/www.sqlnuggets.com\/#\/schema\/person\/210536254addbc1b9d2d95dc1448b38a"},"headline":"Encrypting Passwords Using EncryptByPassPhrase","datePublished":"2017-06-06T20:35:38+00:00","dateModified":"2017-06-23T20:24:27+00:00","mainEntityOfPage":{"@id":"http:\/\/www.sqlnuggets.com\/encrypting-passwords-using-encryptbypassphrase\/"},"wordCount":1268,"commentCount":0,"publisher":{"@id":"http:\/\/www.sqlnuggets.com\/#organization"},"image":{"@id":"http:\/\/www.sqlnuggets.com\/encrypting-passwords-using-encryptbypassphrase\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2017\/05\/cyber-security-e1494949546575.jpg","keywords":["Encryption","Storing Passwords","Top Developer Tips"],"articleSection":["Security"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["http:\/\/www.sqlnuggets.com\/encrypting-passwords-using-encryptbypassphrase\/#respond"]}]},{"@type":"WebPage","@id":"http:\/\/www.sqlnuggets.com\/encrypting-passwords-using-encryptbypassphrase\/","url":"http:\/\/www.sqlnuggets.com\/encrypting-passwords-using-encryptbypassphrase\/","name":"Encrypting Passwords Using EncryptByPassPhrase - SQL Nuggets","isPartOf":{"@id":"http:\/\/www.sqlnuggets.com\/#website"},"primaryImageOfPage":{"@id":"http:\/\/www.sqlnuggets.com\/encrypting-passwords-using-encryptbypassphrase\/#primaryimage"},"image":{"@id":"http:\/\/www.sqlnuggets.com\/encrypting-passwords-using-encryptbypassphrase\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2017\/05\/cyber-security-e1494949546575.jpg","datePublished":"2017-06-06T20:35:38+00:00","dateModified":"2017-06-23T20:24:27+00:00","breadcrumb":{"@id":"http:\/\/www.sqlnuggets.com\/encrypting-passwords-using-encryptbypassphrase\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["http:\/\/www.sqlnuggets.com\/encrypting-passwords-using-encryptbypassphrase\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/www.sqlnuggets.com\/encrypting-passwords-using-encryptbypassphrase\/#primaryimage","url":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2017\/05\/cyber-security-e1494949546575.jpg","contentUrl":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2017\/05\/cyber-security-e1494949546575.jpg","width":1280,"height":592},{"@type":"BreadcrumbList","@id":"http:\/\/www.sqlnuggets.com\/encrypting-passwords-using-encryptbypassphrase\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"http:\/\/www.sqlnuggets.com\/"},{"@type":"ListItem","position":2,"name":"Encrypting Passwords Using EncryptByPassPhrase"}]},{"@type":"WebSite","@id":"http:\/\/www.sqlnuggets.com\/#website","url":"http:\/\/www.sqlnuggets.com\/","name":"SQL Nuggets","description":"Nuggets Of SQL Server Knowledge","publisher":{"@id":"http:\/\/www.sqlnuggets.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"http:\/\/www.sqlnuggets.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"http:\/\/www.sqlnuggets.com\/#organization","name":"SQL Nuggets","url":"http:\/\/www.sqlnuggets.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/www.sqlnuggets.com\/#\/schema\/logo\/image\/","url":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2023\/11\/website-logo.jpg","contentUrl":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2023\/11\/website-logo.jpg","width":320,"height":54,"caption":"SQL Nuggets"},"image":{"@id":"http:\/\/www.sqlnuggets.com\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/x.com\/sqlnugg","https:\/\/www.linkedin.com\/in\/ericcobb\/"]},{"@type":"Person","@id":"http:\/\/www.sqlnuggets.com\/#\/schema\/person\/210536254addbc1b9d2d95dc1448b38a","name":"Eric Cobb","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/3ca1fc0c7054a668e048f09d412cd4ebf89833c4630fbbfccca78a0678a6bdc2?s=96&d=mm&r=pg","url":"https:\/\/secure.gravatar.com\/avatar\/3ca1fc0c7054a668e048f09d412cd4ebf89833c4630fbbfccca78a0678a6bdc2?s=96&d=mm&r=pg","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/3ca1fc0c7054a668e048f09d412cd4ebf89833c4630fbbfccca78a0678a6bdc2?s=96&d=mm&r=pg","caption":"Eric Cobb"},"sameAs":["https:\/\/x.com\/cfgears"],"url":"https:\/\/sqlnuggets.com\/author\/eric-cobb\/"}]}},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2017\/05\/cyber-security-e1494949546575.jpg","jetpack_shortlink":"https:\/\/wp.me\/pdyDvE-76","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":418,"url":"https:\/\/sqlnuggets.com\/storing-passwords-sql-server-database\/","url_meta":{"origin":440,"position":0},"title":"Storing Passwords In A SQL Server Database","author":"Eric Cobb","date":"May 16, 2017","format":false,"excerpt":"While storing passwords in a database may be a common practice, storing them properly usually isn't so common. This is the first of a series of posts where we will examine some of the options available for storing passwords in a SQL Server database. When you store a password in\u2026","rel":"","context":"In &quot;Security&quot;","block_context":{"text":"Security","link":"https:\/\/sqlnuggets.com\/category\/security\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/05\/cyber-security-e1494949546575.jpg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/05\/cyber-security-e1494949546575.jpg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/05\/cyber-security-e1494949546575.jpg?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/05\/cyber-security-e1494949546575.jpg?resize=700%2C400&ssl=1 2x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/05\/cyber-security-e1494949546575.jpg?resize=1050%2C600&ssl=1 3x"},"classes":[]},{"id":641,"url":"https:\/\/sqlnuggets.com\/sql-server-permissions-manager\/","url_meta":{"origin":440,"position":1},"title":"SQL Server Permissions Manager","author":"Eric Cobb","date":"November 21, 2017","format":false,"excerpt":"Last week I announced\u00a03 new open source SQL Server projects that I have on GitHub.\u00a0 One of those projects is SQL Server Permissions Manager, and today I would like to discuss it\u00a0in more detail. SQL Server Permissions Manager is a suite of scripts that allows you to take \"snapshots\" of\u2026","rel":"","context":"In &quot;Open Source Projects&quot;","block_context":{"text":"Open Source Projects","link":"https:\/\/sqlnuggets.com\/category\/open-source-projects\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/11\/arrows-1577983_1280-e1511283424596.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/11\/arrows-1577983_1280-e1511283424596.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/11\/arrows-1577983_1280-e1511283424596.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/11\/arrows-1577983_1280-e1511283424596.png?resize=700%2C400&ssl=1 2x"},"classes":[]},{"id":600,"url":"https:\/\/sqlnuggets.com\/notes-sql-2017-azure-install-using-group-managed-service-accounts\/","url_meta":{"origin":440,"position":2},"title":"Notes From A SQL 2017 Azure Install &#8211; Using Group Managed Service Accounts","author":"Eric Cobb","date":"November 7, 2017","format":false,"excerpt":"One of the new features that I'm excited about implementing on my Windows 2016 SQL Server 2017 servers is the ability to use\u00a0Group Managed Service Accounts, or \"gMSA\" for short.\u00a0 This isn't really a new feature, I believe it was first made available for SQL Server 2014 running on Windows\u2026","rel":"","context":"In &quot;Availability Groups&quot;","block_context":{"text":"Availability Groups","link":"https:\/\/sqlnuggets.com\/category\/availability-groups\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/10\/Cloud-Azure.jpg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/10\/Cloud-Azure.jpg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/10\/Cloud-Azure.jpg?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/10\/Cloud-Azure.jpg?resize=700%2C400&ssl=1 2x"},"classes":[]},{"id":333,"url":"https:\/\/sqlnuggets.com\/persist-and-aggregate-index-stats-across-server-restarts\/","url_meta":{"origin":440,"position":3},"title":"Persist And Aggregate Index Stats Across Server Restarts","author":"Eric Cobb","date":"March 10, 2017","format":false,"excerpt":"(This script is part of my\u00a0SQL Server Metrics Pack\u00a0project that is now available on GitHub.\u00a0 If you like the code below and find it useful, I encourage you to check out the full project.) Maintaining indexes can be an important part of a DBA's job. \u00a0Researching an index to determine\u2026","rel":"","context":"In &quot;Indexes&quot;","block_context":{"text":"Indexes","link":"https:\/\/sqlnuggets.com\/category\/indexes\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.scarydba.com\/wp-content\/uploads\/2017\/03\/tumblr_lumv86RDOd1qdoit4-1.gif?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":1604,"url":"https:\/\/sqlnuggets.com\/powershell-compare-users-ad-groups\/","url_meta":{"origin":440,"position":4},"title":"PowerShell: Compare Users&#8217; AD Groups","author":"Eric Cobb","date":"April 15, 2021","format":false,"excerpt":"When it comes to user logins and permissions in SQL Server, I typically prefer to use Active Directory groups. The problem with that is that you don't always know what groups a given user is in. I recently had a case where I needed to compare the Active Directory Groups\u2026","rel":"","context":"In &quot;PowerShell&quot;","block_context":{"text":"PowerShell","link":"https:\/\/sqlnuggets.com\/category\/powershell\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2021\/04\/patrick-fore-ZmH0g1ievTg-unsplash.jpg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2021\/04\/patrick-fore-ZmH0g1ievTg-unsplash.jpg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2021\/04\/patrick-fore-ZmH0g1ievTg-unsplash.jpg?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2021\/04\/patrick-fore-ZmH0g1ievTg-unsplash.jpg?resize=700%2C400&ssl=1 2x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2021\/04\/patrick-fore-ZmH0g1ievTg-unsplash.jpg?resize=1050%2C600&ssl=1 3x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2021\/04\/patrick-fore-ZmH0g1ievTg-unsplash.jpg?resize=1400%2C800&ssl=1 4x"},"classes":[]},{"id":790,"url":"https:\/\/sqlnuggets.com\/sql-scripts-how-to-find-filtered-indexes\/","url_meta":{"origin":440,"position":5},"title":"SQL Scripts: How To Find Filtered Indexes","author":"Eric Cobb","date":"May 9, 2018","format":false,"excerpt":"Filtered indexes are one of those neat little SQL Server features that a lot of people don't seem to know about, or if they do they don't use them very much.\u00a0 While I agree that putting a filter on every index probably isn't a good idea, there are cases where\u2026","rel":"","context":"In &quot;Indexes&quot;","block_context":{"text":"Indexes","link":"https:\/\/sqlnuggets.com\/category\/indexes\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/06\/board-electronics-computer-data-processing-50711-e1498160567831.jpeg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/06\/board-electronics-computer-data-processing-50711-e1498160567831.jpeg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/06\/board-electronics-computer-data-processing-50711-e1498160567831.jpeg?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/06\/board-electronics-computer-data-processing-50711-e1498160567831.jpeg?resize=700%2C400&ssl=1 2x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/06\/board-electronics-computer-data-processing-50711-e1498160567831.jpeg?resize=1050%2C600&ssl=1 3x"},"classes":[]}],"_links":{"self":[{"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/posts\/440","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/comments?post=440"}],"version-history":[{"count":0,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/posts\/440\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/media\/427"}],"wp:attachment":[{"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/media?parent=440"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/categories?post=440"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/tags?post=440"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}