pwdcompare

In this article, we’ll look at using the built-in PWDCOMPARE function in SQL Server for security testing passwords. While this tool may seem like it exposes a weakness in Microsoft SQL Server because we can test for passwords, it should be of note that an attacker could do the same attack by attempting to login to our database server assuming the attacker was able to access a connection to it. Therefore, this function does not increase the risk of an attack on SQL Server but does help us identify possible weaknesses in our environment so that we can quickly mitigate these risks. In addition, we’ll also combine this with other related tools in SQL Server to help us with logins.

在本文中，我们将研究如何使用SQL Server中的内置PWDCOMPARE函数进行安全性测试密码。 尽管此工具似乎可以揭露Microsoft SQL Server的弱点，因为我们可以测试密码，但应注意，攻击者可以通过尝试登录我们的数据库服务器来进行相同的攻击，前提是假定攻击者能够访问连接到它。 因此，此功能不会增加对SQL Server进行攻击的风险，但是可以帮助我们确定环境中可能存在的弱点，以便我们可以快速缓解这些风险。 此外，我们还将把它与SQL Server中的其他相关工具结合起来，以帮助我们进行登录。

了解功能 (Understanding the function)

For our security testing, we’ll look at the first two parameters – the actual password (“clear_text_password”) we want to test and the password hash (“password_hash”). Microsoft declared that they will be deprecating the third optional parameter of version, so we will not use this and it should be avoided since it will be removed in a later version of SQL Server. If the two parameters match, our output will result in a 1 whereas if they do not match, our output will result in a 0.

对于我们的安全性测试，我们将查看前两个参数–我们要测试的实际密码（“ clear_text_password”）和密码哈希（“ password_hash”）。 Microsoft声明他们将弃用version的第三个可选参数，因此我们将不使用它，并且应避免使用它，因为它将在更高版本SQL Server中删除。 如果两个参数匹配，则输出将为1，而如果它们不匹配，则输出将为0。

创建测试登录 (Creating logins for testing)

Before security testing common passwords, we’ll create six logins with common password forms – some of these use name with numbers and some use the “password” with a number combination. Both of these are unfortunately common because they’re easy to memorize. First, we’ll check to ensure that none of these six logins exist – the below query should return 0 records:

在对通用密码进行安全性测试之前，我们将创建六个具有通用密码形式的登录名-其中一些使用带数字的名称，有些使用带数字组合的“密码”。 不幸的是，这两个都是很常见的，因为它们很容易记住。 首先，我们将检查以确保这六个登录名都不存在–以下查询应返回0条记录：

USE [master] SELECT     [name]    , [create_date]    , [modify_date]FROM sys.sql_loginsWHERE [name] LIKE 'zmyLogin%'

If any records return, we’ll want to use another name combination. Provided that no records return, we’ll create the six logins – notice that I’ve commented out the drop login commands, which we’ll want to run later.

如果有记录返回，我们将使用其他名称组合。 假设没有记录返回，我们将创建六个登录名–请注意，我已经注释掉drop login命令，稍后将要运行该命令。

CREATE LOGIN [zmyLogin] WITH PASSWORD = N'zmyLogin1', CHECK_POLICY = OFFCREATE LOGIN [zmyLogin1] WITH PASSWORD = N'zmyLogin11', CHECK_POLICY = OFFCREATE LOGIN [zmyLogin2] WITH PASSWORD = N'zmyLogin21', CHECK_POLICY = OFFCREATE LOGIN [zmyLogin3] WITH PASSWORD = N'password1', CHECK_POLICY = OFFCREATE LOGIN [zmyLogin4] WITH PASSWORD = N'password2', CHECK_POLICY = OFFCREATE LOGIN [zmyLogin5] WITH PASSWORD = N'password3', CHECK_POLICY = OFF---- Drop all created logins for testing:/*DROP LOGIN [zmyLogin]DROP LOGIN [zmyLogin1]DROP LOGIN [zmyLogin2]DROP LOGIN [zmyLogin3]DROP LOGIN [zmyLogin4]DROP LOGIN [zmyLogin5]*/

As a note, we are intentionally not checking the password for security strength by using the option CHECK_POLICY = OFF. As a general rule with security testing, enforcing this with all logins would ensure that no one can use their login name in the password, along with requiring a minimum of 8 characters and a character combination of non-alphanumeric characters, and upper and lower case character combinations.

注意，我们不打算通过使用选项CHECK_POLICY = OFF来检查密码的安全性。 作为安全性测试的一般规则，对所有登录名都强制执行此操作将确保没有人可以在密码中使用其登录名，并要求至少8个字符以及非字母数字字符，大写和小写字母的字符组合字符组合。

We can see the option to enforce password policy when we manually create a login through the interface or selecting this option as ON when scripting logins for creation.

当我们通过界面手动创建登录名或在编写用于创建登录名的脚本时选择此选项为ON时，可以看到执行密码策略的选项。

As an initial audit, we can check our password complexity and see these six logins fail this requirement:

作为初步审核，我们可以检查密码的复杂性，并查看这六次登录未能满足此要求：

SELECT 	[name]FROM sys.sql_loginsWHERE is_policy_checked = 0

Our six logins already failed an initial audit of password complexity.

我们的六次登录均未通过密码复杂度的初步审核 。

针对保存的值进行安全测试的示例 (Example of Security Testing against saved values)

With our six logins, we’ll perform security testing with an example by checking the password hashes from the logins we created against a table with commonly used password techniques. In the first part of our example, we’ll create a table that we’ll store these commonly used passwords – in this case, combinations with login names and 1 along with the text “password” plus a numeric combination (for space, I am only iterating to 3, but it’s a good idea to check combinations of 4 characters involving ending digits – 123, 1234, etc. are very common).

使用我们的六个登录名，我们将通过一个示例来执行安全性测试，方法是针对使用常用密码技术的表检查我们创建的登录名中的密码哈希。 在示例的第一部分中，我们将创建一个表，该表将存储这些常用密码-在这种情况下，将使用登录名和1以及文本“ password”的组合加上数字组合（对于空格，只能迭代到3，但最好检查4个字符的组合，其中包含结束数字-123、1234等非常常见）。

CREATE TABLE tbCheck(	CheckValue NVARCHAR(128))----Drop table for testing/*DROP TABLE tbCheck*/INSERT INTO tbCheck VALUES ('password1')INSERT INTO tbCheck VALUES ('password2')INSERT INTO tbCheck VALUES ('password3') INSERT INTO tbCheck SELECT CAST([name] + '1' AS NVARCHAR(128)) FROM sys.sql_logins

Keep in mind that we want to ensure that none of our logins are using these passwords. We would use this table to store passwords that have been compromised, so as new passwords are identified as compromised, our security testing would involve loading this table with these newly identified values. Next, we’ll use the T-SQL PWDCOMPARE function to return all values where the password_hash from sql_logins matches the column CheckValue from our tbCheck table. We accomplish this by cross applying tbCheck with sql_logins and comparing the password_hash column with the CheckValue column:

请记住，我们要确保没有任何登录使用这些密码。 我们将使用该表来存储已被泄露的密码，因此当新密码被标识为已泄露时，我们的安全性测试将涉及使用这些新标识的值来加载该表。 接下来，我们将使用T-SQL PWDCOMPARE函数返回所有值，其中sql_logins中的password_hash与tbCheck表中的CheckValue列匹配。 我们通过将tbCheck与sql_logins交叉应用，并将password_hash列与CheckValue列进行比较来实现此目的：

;WITH ReturnUsedLogins AS(	SELECT 		[name] LoginName		, password_hash PasswordHash		, CheckValue		, PWDCOMPARE(CheckValue, password_hash) Compare	FROM sys.sql_logins 		CROSS APPLY tbCheck)SELECT LoginName	, CheckValueFROM ReturnUsedLoginsWHERE Compare = 1

Our results identify the login name along with the password that failed our check:

我们的结果将标识登录名以及未能通过检查的密码：

Our security testing with PWDCOMPARE invalidated these logins as using common password combinations.

我们使用PWDCOMPARE进行的安全测试使这些登录无效，因为它们使用的是通用密码组合 。

In some cases, following a cyberattack, passwords are leaked and we can get a list of these passwords to check against our logins’ passwords. Following the structure that we use in the above code, we can save these passwords to a table and run a check against our logins’ password hashes. We want to ensure that no leaked password or common password combination exists, as these are known or common.

在某些情况下，在发生网络攻击后，密码会泄漏，我们可以获取这些密码的列表以检查登录密码。 按照上面代码中使用的结构，我们可以将这些密码保存到表中，并检查登录密码的哈希值。 我们要确保不存在泄露的密码或常用密码组合，因为它们是已知的或常用的。

Keep in mind that even if we ensure password strength to pass this validation, there are still some combinations of passwords that are commonly used that an attacker can check periodically against our database. This is because attackers can attack at any time and can attack over long periods of time. As an example of a password that passes both security testing through enforced policy validation, in the below script, we try to create an uncomplex password that fails, but then succeeds at creating a complex password that is unfortunately very common:

请记住，即使我们确保密码强度能够通过此验证，攻击者仍可以定期使用某些密码组合来定期检查我们的数据库。 这是因为攻击者可以随时进行攻击，并且可以长时间进行攻击。 作为通过强制策略验证通过安全性测试的密码的示例，在下面的脚本中，我们尝试创建一个失败的非复杂密码，但是不幸的是成功创建了一个非常常见的复杂密码：

CREATE LOGIN [zDropThisLogin] WITH PASSWORD = N'password', CHECK_POLICY = ONCREATE LOGIN [zDropThisLogin] WITH PASSWORD = N'P@ssword1', CHECK_POLICY = ONDROP LOGIN [zDropThisLogin]

The second password passes the check policy because it meets the complexity requirements, yet it is a common password that is checked against. We shouldn’t assume that complexity only is enough to ensure the strengths of our logins. Running checks against common password combinations or against compromised passwords that are known can help us strengthen our security even above enforcing complex passwords.

第二个密码通过了检查策略，因为它符合复杂性要求，但是它是一个常用密码，需要进行检查。 我们不应该认为仅凭复杂性就足以确保我们登录的优势。 对常见密码组合或已知的泄露密码进行检查可以帮助我们增强安全性，甚至不执行复杂的密码。

摘要 (Summary)

We’ve looked at using the built-in pwdcompare function in Microsoft SQL Server for security testing our login passwords. This helps reduce unauthorized access that may compromise our information, intellectual property, or our design from a destructive attack. Keep in mind that using other forms of security for logging in, such as 2-factor authentication assist in reducing risks, as these work well with strong passwords to avoid unauthorized access. Password strength help, as does other methods of security.

我们已经研究过使用Microsoft SQL Server中的内置pwdcompare函数对我们的登录密码进行安全性测试。 这有助于减少未经授权的访问，这些访问可能破坏我们的信息，知识产权或设计，使其免受破坏性攻击。 请记住，使用其他形式的安全性登录（例如2要素身份验证）有助于降低风险，因为它们可以与强密码一起使用，从而避免未经授权的访问。 密码强度和其他安全方法一样有帮助。

翻译自:

pwdcompare