How to check Enforce a Password policy is enabled on SQL Server

Question: Using t-sql , How can  check I Enforce a password policy is enabled on SQL Server ? I want to add this check for a regular SQL Server security configuration scan. 

This information would be viewed via a report , which DBAs could fix the issue 

Answer: This view will display the current status of Enforce password policy on a SQL Server login.

select * from sys.sql_logins where is_policy_checked = 0 

This query checks if a SQL Server using SQL Authentication has the Enforce Password Policy enabled 

If the Enforce a Password policy is not set , the password does not to apply the password complexity - typically set by the OS rules.   

If you then change the flag to Enforce a Password policy, WITHOUT changing the password ,  this will not force you to change the password - if the password doesn't follow the password complexity rules. .  But if you attempt to change the password after you have clicked OK for Enforce a Password policy, the "Password validation failed" message will appear - if the password complexity rules are not met. 

Powershell script to execute the query across multiple servers

The requirement may be to run this query across multiple SQL Servers , either as a one-off or as a regular  execute on multiple SQL Servers. Powershell is a good solution to execute this script across multiple SQL Servers. 

This powershell script returns the sql logins with is_policy_checked = 0 from the  SQL Server Instances listed in the instances_all.txt file

e.g     SERVER1\MY_INSTANCE1

         SERVER2\MY_INSTANCE2 

The output is written to the host.  Read How to covert Powershell to HTML

$instancepath="C:\myscripts\SQL_Server\sql_server_instances.txt"


$dt = new-object "System.Data.DataTable"
foreach ($instance in get-content $instancepath)
{
$instance
$cn = new-object System.Data.SqlClient.SqlConnection "server=$instance;database=master;Integrated Security=sspi"
$cn.Open()
$sql = $cn.CreateCommand()
$sql.CommandText = "select @@servername as server_name, [name] as sql_login, type_desc,is_policy_checked from sys.sql_logins where is_policy_checked = 0 order by @@servername"
$rdr = $sql.ExecuteReader()
$dt.Load($rdr)
$cn.Close()
 
}


$dt |select * -ExcludeProperty RowError, RowState, HasErrors, Name, Table, ItemArray  | out-host

Read more about password management

How to check Enforce password expiration is set for SQL Login

How to view SQL Server Password Configurations

How to check SQL Server password policy

Powershell sql server security audit