Protect SQL Server from Brute Force attacksA brute force attack is a trial-and-error method used to obtain information such as a user password or personal identification number (PIN). In a brute force attack, automated software is used to generate a large number of consecutive guesses as to the value of the desired data. Brute force attacks may be used by criminals to crack encrypted data, or by security analysts to test an organization's network security.
A brute force attack may also be referred to as brute force cracking.
In SQL Server brute force is a way to crack SQL login passwords by trying every possible letter combination of letters without knowing the password
- Identify the SQL passwords that are not enforced by policies
SELECT NAME ,is_disabled FROM sys.sql_logins WHERE is_policy_checked = 0 ORDER BY NAME;All the logins listed in the output might have a weak password.
- Enforce policy to those logins
ALTER LOGIN MyLogin WITH CHECK_POLICY = ON ,CHECK_EXPIRATION = ON;
This code modifies the MyLogin login to enforce password policy and expiration.
Check when the password is set to expire with the following script :
SELECT LOGINPROPERTY('MyLogin', 'DaysUntilExpiration');
If you want to enforce it immediately change the password and communicate the password to
- Force the login to change password with the MUST_CHANGE option
ALTER LOGIN MyLogin WITH PASSWORD = 'ch@nge y0ur Pa$$word !' MUST_ CHANGE ,CHECK_POLICY = ON ,CHECK_EXPIRATION = ON;
- Generate the script for all the necessary logins:
SELECT 'ALTER LOGIN ' + QUOTENAME(NAME) + ' WITH PASSWORD = ''ch@nge y0ur Pa$$word !'' MUST_CHANGE, CHECK_POLICY = ON, CHECK_ EXPIRATION = ON; ' FROM sys.sql_logins WHERE is_policy_checked = 0 ORDER BY NAME;
Note:The best way to protect passwords against brute-force attacks is to enforce Windows password policies and expiration, because this will guarantee passwords are strong enough from being guessed. This attacks leaves the traces in SQL Server Error log.