{"id":2455,"date":"2014-06-27T11:30:26","date_gmt":"2014-06-27T15:30:26","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=2455"},"modified":"2014-11-13T11:58:03","modified_gmt":"2014-11-13T16:58:03","slug":"pwdcompare","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/2455\/pwdcompare\/","title":{"rendered":"Auditing SQL Server Password Strength using PWDCOMPARE"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nAs a DBA, one of your responsibilities is to ensure that every password on the system is strong. We know already that SQL Server stores the passwords of SQL logins as salted hash values<\/a>. That however means that we cannot just go through the passwords and look at them to see if they follow best practices. What we can do however, is to try a simple brute-force attack against the passwords. That is exactly what a hacker would do, so it makes sense to regularly test the current system strength against that attack.\n<\/p>\n

Password Policies<\/h3>\n

\nSQL Server allows us to \"enforce\" that all passwords follow the Windows password complexity requirements. However, it is easy for elevated users to circumvent this requirement. What is worse, looking at the information that the catalog views provide, you might get the impression that the password for a given SQL login follows those requirements while it actually is not.\n<\/p>\n

\nThe only way to make sure that all passwords in SQL Server are strong is to try to attack them regularly.\n<\/p>\n

The PWDCOMPARE Function<\/h3>\n

\nInstead of having to write a (power-)shell script that tries one password after the other, SQL Server is nice enough to give us access to a built-in function that can compare a clear-text password to a hash value. This function is the PWDCOMPARE<\/span> function. To use it you can just pass in the password you would like to compare too followed by the password hash. Remember, to get to the password hash value, you can for example use the sys.sql_logins<\/a> catalog view:\n<\/p>\n

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

\nNow we can just pass that value directly to the PWDCOMPARE<\/span> function as second parameter:\n<\/p>\n

\n[sql]\nSELECT SL.name,PWDCOMPARE('********',SL.password_hash) password_match
\n FROM sys.sql_logins AS SL
\n WHERE SL.name = 'ALogin';
\n[\/sql]\n<\/div>\n

\nAnd if the guessed password matches the actual one, you will get this output:\n<\/p>\n

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

Brute Force Cracking<\/h3>\n

\nA Brute Force attack generally involves testing all possible combinations. However, in our case we are not actually interested in the password. We just want to know if the password withstands a reasonable attack. Given enough resources and time, you can crack any password. The hope is that if you make your passwords strong enough, that the opponent will give up and move on to an easier target. For that reason, I recommend using a word list instead.\n<\/p>\n

Word List Cracking<\/h3>\n

\nThe main difference between Brute-Force cracking and Word List cracking is that a word list greatly reduces the number of tries, and therefore required resources, by skipping unlikely (read: strong) passwords. Word lists can be downloaded from the internet. Before you use a wordlist, you probably want to apply common alterations to it, like replacing characters with common substitutes, e.g. \"@\" for \"a\", and concatenate up to three or four of the words together.\n<\/p>\n

\nOnce you have a wordlist that you like, store it in a table. Then you can just use a simple join<\/a> to test all combinations:\n<\/p>\n

\n[sql]\nSELECT SL.name,
\n WL.APassword
\n FROM sys.sql_logins AS SL
\n CROSS JOIN dbo.WordList AS WL
\n WHERE PWDCOMPARE(WL.APassword,SL.password_hash) = 1;
\n[\/sql]\n<\/div>\n

\nIf a weak password is found, it will be part of the result set:\n<\/p>\n

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

Other Use-Cases<\/h3>\n

\nThere are rumors that PWDCOMPARE<\/span> is an undocumented function and should not be used. That information is wrong as this PWDCOMPARE<\/span> entry in BOL clearly demonstrates: dd822792<\/a>.\n<\/p>\n

\nSQL Server even provides a PWDENCRYPT<\/span> function that can be used to calculate a hash value from a given clear-text password. That means, you could take those two functions and use them to store your own application passwords in a secure way. However, BOL discourages from that. While PWDENCRYPT<\/span> is not officially deprecated, its BOL entry<\/a> says that it will be soon: \" PWDENCRYPT is an older function and might not be supported in a future release of SQL Server.\"\n<\/p>\n

Summary<\/h3>\n

\nSQL Server stores SQL login passwords as salted hash values. Therefore, we cannot directly check if they follow password strength requirements. However, we can use the PWDCOMPARE<\/span> function to try to crack them. I encourage you to try this regularly, as only that way security holes base on weak passwords will be discovered.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

SQL Server stores passwords as salted hash values, preventing us from directly checking or enforcing their strength. However, using PWDCOMPARE we can try to crack them to identify weak ones.<\/p>\n

[more…]<\/a><\/p>\n","protected":false},"author":3,"featured_media":2458,"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":[118,5,203,34,119],"tags":[147,153,205,204,38,58,15],"class_list":["post-2455","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-dmvs-cvs","category-general","category-passwords","category-security","category-security-dmvs-cvs","tag-hash-value","tag-password","tag-pwdcompare","tag-pwdencrypt","tag-security-2","tag-security-management","tag-sql-server"],"acf":[],"yoast_head":"\nAuditing Password Strength using PWDCOMPARE - sqlity.net<\/title>\n<meta name=\"description\" content=\"SQL Server stores passwords as a salted hash, preventing us from enforcing their strength. Using PWDCOMPARE we can try to crack them to identify weak ones.\" \/>\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\/2455\/pwdcompare\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Auditing Password Strength using PWDCOMPARE - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"SQL Server stores passwords as a salted hash, preventing us from enforcing their strength. Using PWDCOMPARE we can try to crack them to identify weak ones.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/2455\/pwdcompare\/\" \/>\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-06-27T15:30:26+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T16:58:03+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/06\/PWDCOMPARE_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=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2455\\\/pwdcompare\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2455\\\/pwdcompare\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"Auditing SQL Server Password Strength using PWDCOMPARE\",\"datePublished\":\"2014-06-27T15:30:26+00:00\",\"dateModified\":\"2014-11-13T16:58:03+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2455\\\/pwdcompare\\\/\"},\"wordCount\":722,\"commentCount\":1,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2455\\\/pwdcompare\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/06\\\/PWDCOMPARE_in_action.jpg\",\"keywords\":[\"Hash Value\",\"password\",\"PWDCOMPARE\",\"PWDENCRYPT\",\"security\",\"security management\",\"SQL Server\"],\"articleSection\":[\"DMVs & CVs\",\"General\",\"Passwords\",\"Security\",\"Security\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2455\\\/pwdcompare\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2455\\\/pwdcompare\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2455\\\/pwdcompare\\\/\",\"name\":\"Auditing Password Strength using PWDCOMPARE - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2455\\\/pwdcompare\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2455\\\/pwdcompare\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/06\\\/PWDCOMPARE_in_action.jpg\",\"datePublished\":\"2014-06-27T15:30:26+00:00\",\"dateModified\":\"2014-11-13T16:58:03+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"SQL Server stores passwords as a salted hash, preventing us from enforcing their strength. Using PWDCOMPARE we can try to crack them to identify weak ones.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2455\\\/pwdcompare\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2455\\\/pwdcompare\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2455\\\/pwdcompare\\\/#primaryimage\",\"url\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/06\\\/PWDCOMPARE_in_action.jpg\",\"contentUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/06\\\/PWDCOMPARE_in_action.jpg\",\"width\":768,\"height\":468,\"caption\":\"PWDCOMPARE in Action\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2455\\\/pwdcompare\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Auditing SQL Server Password Strength using PWDCOMPARE\"}]},{\"@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":"Auditing Password Strength using PWDCOMPARE - sqlity.net","description":"SQL Server stores passwords as a salted hash, preventing us from enforcing their strength. Using PWDCOMPARE we can try to crack them to identify weak ones.","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\/2455\/pwdcompare\/","og_locale":"en_US","og_type":"article","og_title":"Auditing Password Strength using PWDCOMPARE - sqlity.net","og_description":"SQL Server stores passwords as a salted hash, preventing us from enforcing their strength. Using PWDCOMPARE we can try to crack them to identify weak ones.","og_url":"https:\/\/sqlity.net\/en\/2455\/pwdcompare\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-06-27T15:30:26+00:00","article_modified_time":"2014-11-13T16:58:03+00:00","og_image":[{"width":768,"height":468,"url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/06\/PWDCOMPARE_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":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/2455\/pwdcompare\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/2455\/pwdcompare\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"Auditing SQL Server Password Strength using PWDCOMPARE","datePublished":"2014-06-27T15:30:26+00:00","dateModified":"2014-11-13T16:58:03+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/2455\/pwdcompare\/"},"wordCount":722,"commentCount":1,"image":{"@id":"https:\/\/sqlity.net\/en\/2455\/pwdcompare\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/06\/PWDCOMPARE_in_action.jpg","keywords":["Hash Value","password","PWDCOMPARE","PWDENCRYPT","security","security management","SQL Server"],"articleSection":["DMVs & CVs","General","Passwords","Security","Security"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/2455\/pwdcompare\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/2455\/pwdcompare\/","url":"https:\/\/sqlity.net\/en\/2455\/pwdcompare\/","name":"Auditing Password Strength using PWDCOMPARE - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/2455\/pwdcompare\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/2455\/pwdcompare\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/06\/PWDCOMPARE_in_action.jpg","datePublished":"2014-06-27T15:30:26+00:00","dateModified":"2014-11-13T16:58:03+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"SQL Server stores passwords as a salted hash, preventing us from enforcing their strength. Using PWDCOMPARE we can try to crack them to identify weak ones.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/2455\/pwdcompare\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/2455\/pwdcompare\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/2455\/pwdcompare\/#primaryimage","url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/06\/PWDCOMPARE_in_action.jpg","contentUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/06\/PWDCOMPARE_in_action.jpg","width":768,"height":468,"caption":"PWDCOMPARE in Action"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/2455\/pwdcompare\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"Auditing SQL Server Password Strength using PWDCOMPARE"}]},{"@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\/06\/PWDCOMPARE_in_action.jpg","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-DB","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2455","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=2455"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2455\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media\/2458"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=2455"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=2455"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=2455"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}