Chapter 2 user authentication, authorization, and security (3): protects servers against brute force attacks.

Source: http://blog.csdn.net/dba_huangzj/article/details/38756693 , Topic directory: Http://blog.csdn.net/dba_huangzj/article/details/37906349

Without the consent of the author, no one shall be published in the form of "original" or used for commercial purposes. I am not responsible for any legal liability.

Previous Article: http://blog.csdn.net/dba_huangzj/article/details/38705965

 

Preface:

 

Brute-force attack attempts to crack the password by combining almost all possible characters, or uses a dictionary table containing almost all possible passwords to crack the password. If your password is simple, it will be cracked soon. Therefore, password testing is very important.

 

Implementation:

 

1. First, find the SQL password that does not use the mandatory password policy:

 

Select name, is_disabled from SYS. SQL _logins where is_policy_checked = 0 order by name;
 

 

2. Use a strong password policy for these logins:

 

 
Alter login Fred with check_policy = on, check_expiration = on;
 

 

This command does not change the existing password until the password expires. You can use the following function to check the password expiration time:

 
Select loginproperty ('fred ', 'daysuntilexpiration ');
 

 

3. you can also force a password change during login, but you need to provide a negotiated password and inform the user, for example, the following code is to force the Fred login name to change the password during login, then you used you need to change me! As the "initial password", You need to inform the user to use it during login. After successful login, you will be prompted to change the password.

 
Alter login Fred with Password = 'you need to change me! 'Must_change, check_policy = on, check_expiration = on;
 

Source:Http://blog.csdn.net/dba_huangzj/article/details/38756693

 

4. You can use a script to display all the login names to be modified:

Select 'alter login' + quotename (name) + 'with Password = ''You need to change me 11'' must_change, check_policy = on, check _ expiration = on;' from sys. SQL _logins where is_policy_checked = 0 order by name;
 

 

If you need to allow users to change their passwords in the application, refer to this article: http://msdn.microsoft.com/zh-cn/library/ms131024.aspx (programmatically changing the password)

 

Principle:

 

 

The best way to protect your password against brute force attacks is to use a Windows Password policy because it only allows you to use a strong password. In addition, the brute-force password will be kept in the SQL Server Error Log and Windows event log.

The password or key for SQL logon is not stored in any system table, but only the hash value of the password. That is to say, there is no way to decrypt the password. The hash value is stored in the system table so that it matches the hash value generated by the hash function with the transmitted password during subsequent logon.

 

 Source:Http://blog.csdn.net/dba_huangzj/article/details/38756693

More:

 

One of the components of the expiration Policy is lockout threshold. If you want to enable the attempt to lock the SQL logon failure, you need to set the check_policy option to on, and configure your account lock policy in Active Directory or local.

 

 

You can use the following statement to check whether a locked account exists:

 
Select name from SYS. SQL _logins where loginproperty (name, N 'islocked') = 1 order by name;
 

Source: Http://blog.csdn.net/dba_huangzj/article/details/38756693

Chapter 2 user authentication, authorization, and security (3): protects servers against brute force attacks.