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 ?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Vitor MontalvãoMSSQL Senior EngineerCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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ãoMSSQL Senior EngineerCommented:
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ãoMSSQL Senior EngineerCommented:
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ãoMSSQL Senior EngineerCommented:
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ãoMSSQL Senior EngineerCommented:
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ãoMSSQL Senior EngineerCommented:
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ãoMSSQL Senior EngineerCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.