How to Enforce Password Policy of Windows to SQL Server? – Interview Question of the Week #142

Question: How to Enforce Password Policy of Windows to SQL Server?

Answer: Before we answer this question, let us assume that your windows server is joined with domain of your organization and have you a very strict password policy. Now when you install a new SQL Server if you do not change any default settings, SQL Server always have enabled enforce password policy settings for login, which enforces your password policy of windows to SQL Server.

However, let us assume that due to any reasons, for some login this particular policy is turned off. In that case, here is the simple script which can enforce password policy for that particular login.

ALTER LOGIN [sqlauthority] WITH PASSWORD='sql', CHECK_POLICY = ON

That’s it. After enabling this setting, SQL Server will now enforce the password policy for ‘sqlauthority’ login.

However, if you have no password policy in your domain, this setting will not be effective.

Here are few blog articles which will help you to understand the SQL Server security concept in detail:

• SQL SERVER – Beginning of SQL Server Security
• SQL SERVER – Introduction to SQL Server Security – A Primer
• SQL SERVER – Security Considerations for Contained Databases
• SQL SERVER – Security Conversations and Notes with a DBA

Have you ever faced such situation? If yes, please leave a comment here.

Reference: Pinal Dave (https://blog.sqlauthority.com)