Securing SQL Server: CHECK_POLICY for SQL Server logins

The CHECK_POLICY option is a security feature in SQL Server that is used to enforce password policy settings for SQL authenticated logins. When CHECK_POLICY is set to ON, SQL Server will check the password for a login against the password policy defined in the operating system. This can help ensure that passwords used for SQL authenticated logins are strong and secure, which can help protect against potential security threats.

Setting the CHECK_POLICY option to ON for all SQL authenticated logins is an important security measure because it helps to ensure that all users who are accessing the database server are using strong, secure passwords. This can help to reduce the risk of password-related attacks, such as brute force attacks or password cracking.

Here is an example of how to set CHECK_POLICY to ON for a SQL Server login:

ALTER LOGIN yourLoginName;
WITH CHECK_EXPIRATION = ON;

To check that this is on for all existing SQL Logins you can run this query, which will show all that it is not on for.

SELECT * 
  FROM sys.sql_logins 
 WHERE is_policy_checked = 0;

It’s important to note that CHECK_POLICY only applies to SQL authenticated logins, and does not affect Windows authenticated logins. In addition, CHECK_POLICY only enforces the password policy defined in the operating system, and does not enforce any additional password policies that you may have defined in your SQL Server instance.

To check that this is on for all existing SQL Logins you can run this query, which will show all that it is not on for.

Overall, setting CHECK_POLICY to ON for all SQL authenticated logins is an important security measure that can help ensure that passwords used for SQL authenticated logins are strong and secure. This can help protect against potential security threats and improve the overall security of your SQL Server instance.