We help IT Professionals succeed at work.

SQL server password policy

Dear all,

right now what to know what is the detail of the SQL server 2008 R2 with SP2 and CU4  password policy we can tick from within SSMS:

password policy
1)  can we change the SQL password policy?
2) what is the policy defined for that?
3) Any alert if the SQL server someone just break the policy ?

is it only for SQL account but not Windows domain account ?
Comment
Watch Question

Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
SQL Server password policy follows the LOCAL Server password policy. So depending on the policy it's set for the local server you'll have the same policy for SQL Server.
You can check the current policies in the Local Security Policy:
LocalPasswordPolicy.PNG
is it only for SQL account but not Windows domain account ?
Correct. Domain accounts follow the domain security policy.
marrowyungSenior Technical architecture (Data)

Author

Commented:
OK, so SQL server policy only follow local server policy ?
marrowyungSenior Technical architecture (Data)

Author

Commented:
any URL to proof that ? I trust you but this is good for presentation.
IT Engineer
Distinguished Expert 2017
Commented:
OK, so SQL server policy only follow local server policy ?
Yes.

any URL to proof that ?
To proof you can test but for reinforce what I said you can check this MSDN article. Here's the transcription from that article:
In SQL Server 2005 and later, SQL Server logins can also adhere to the windows login policies if the operating system version is Windows Server 2003 and later. The parameters specified in the "CREATE LOGIN" T-SQL command dictate if the login policy is enforced. The CHECK_POLICY parameter specifies that the SQL Login must abide by the Windows Login policy and Account Lockout policy which includes the password strength.
marrowyungSenior Technical architecture (Data)

Author

Commented:
you know I googled this one :

http://blogs.msdn.com/b/sqlsecurity/archive/2009/03/25/enforce-password-policy-on-sql-server-logins.aspx

but that one is just too simple !

we undergoing ISO20000 and ISO270001, we need to document this down.

tks,
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
I don't know what those ISO are saying but for security reasons we usually disable the SQL Server Logins and work only with Windows Authentication so all the login security would be managed in AD instead of SQL Server.
marrowyungSenior Technical architecture (Data)

Author

Commented:
I think this is also because for Windows authentication, login information pass through the network is encrypted, but not SQL-only login, right?

Auditor just ask password policy question ! this is just the basic of security.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
I think this is also because for Windows authentication, login information pass through the network is encrypted, but not SQL-only login, right?
It's encrypted but the credentials will be always travelling in the network so there's a risk to be captured. Windows authentication uses Kerberos protocol so theoretically more secure.

Auditor just ask password policy question ! this is just the basic of security.
Why use a password policy for SQL Login and another policy for AD user? Why a person needs to remember two passwords? Why DBA's need to manage user credentials?
marrowyungSenior Technical architecture (Data)

Author

Commented:
"It's encrypted but the credentials will be always travelling in the network so there's a risk to be captured. "

but they have to decrypt it before reading, right?

"Why use a password policy for SQL Login and another policy for AD user? Why a person needs to remember two passwords? Why DBA's need to manage user credentials?"

I don't understand what is that mean !
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
but they have to decrypt it before reading, right?
Sure.

I don't understand what is that mean !
Means that everyone already has a credential to connect to the network (AD user) so why create for them a SQL Server login when they can use their AD login?
marrowyungSenior Technical architecture (Data)

Author

Commented:
"Sure."

then it will be the same as usual network connection and I have no concern on this.

"Means that everyone already has a credential to connect to the network (AD user) so why create for them a SQL Server login when they can use their AD login?"

yeah, I get it now, probably for monitoring tools access.

So SQL only login is not encrypted ?
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
So SQL only login is not encrypted ?
Yes, it is. But the protocol is different. Microsoft recommends Windows Authentication whenever is possible since it uses Kerberos and SQL Server login doesn't. They say it's more secure and I'm trying to believe on them (even today's April's fools day) :)
marrowyungSenior Technical architecture (Data)

Author

Commented:
" I'm trying to believe on them (even today's April's fools day) :)"
I like, wait SQL only also encrypted ? how many bits encryption do you know ?

April fools is not a magazine, right ?    :) :)
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
how many bits encryption do you know ?
Sorry but I don't know. To be honest I never investigated that.
marrowyungSenior Technical architecture (Data)

Author

Commented:
ok, I just know SQL only login no not encrypted.

just like local Windows account, not encrytped at all.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Where did you get the information about that?
marrowyungSenior Technical architecture (Data)

Author

Commented:
long time ago, I forget it.

just like web account, local server account when compare with NT login account.