Lock a SQL login account

Question

Lock a SQL login account

irvidesai

SSC Veteran

Points: 266
More actions
July 31, 2014 at 10:20 am

#297218

Hi,
I want to set up an automation process where the SQL account gets locked if the user does not change the password after a time frame. How do I proceed with this automation. We have an SSIS package that calculates the password age of all the sql accounts and if it is more than 180 days old I want to lock the account.
Is there a script that can work or I can apply to the SSIS package?.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Account lockout for SQL Server is enforced by the local machine or domain policy in effect. This applies to SQL logins as well.

So if you want an account to lockout after x tries, or to require password change, or to enforce password complexity, you can do that through policy management within AD or via GPEDIT.msc.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Lowell SSC Guru Points: 323504 More actions · Answer 2

there's a logonproperty function that lets you pull things like the last time the password was changed;

with this query, you could find items older than 180 days, and issue a alter login command to disable them or force a password change

/*

name type_desc PasswordLAstChanged (No column name) DaysSinceLastChanged

sa SQL_LOGIN 2014-02-20 00:21:48.370 2014-02-20 00:21:48.370 161

superman SQL_LOGIN 2014-02-21 12:59:07.187 2014-02-21 12:59:07.187 160

batman SQL_LOGIN 2014-02-21 12:59:07.207 2014-02-21 12:59:07.207 160

clarkent SQL_LOGIN 2014-02-22 09:22:56.840 2014-02-22 09:22:56.840 159

loislane SQL_LOGIN 2014-02-22 09:22:56.843 2014-02-22 09:22:56.843 159

*/

SELECT

name,

type_desc,

LOGINPROPERTY([name], 'PasswordLastSetTime') AS 'PasswordLAstChanged',

CONVERT(datetime,LOGINPROPERTY([name], 'PasswordLastSetTime') ),

CASE WHEN CONVERT(datetime,LOGINPROPERTY([name], 'PasswordLastSetTime') ) IS NULL THEN 9999

ELSE DATEDIFF(dd,CONVERT(datetime,LOGINPROPERTY([name], 'PasswordLastSetTime') ),getdate())

END AS DaysSinceLastChanged--,*

FROM master.sys.server_principals

WHERE type_desc IN('SQL_LOGIN')

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Lowell (7/31/2014)
there's a logonproperty function that lets you pull things like the last time the password was changed;
with this query, you could find items older than 180 days, and issue a alter login command to disable them or force a password change
/*
name type_desc PasswordLAstChanged (No column name) DaysSinceLastChanged
sa SQL_LOGIN 2014-02-20 00:21:48.370 2014-02-20 00:21:48.370 161
superman SQL_LOGIN 2014-02-21 12:59:07.187 2014-02-21 12:59:07.187 160
batman SQL_LOGIN 2014-02-21 12:59:07.207 2014-02-21 12:59:07.207 160
clarkent SQL_LOGIN 2014-02-22 09:22:56.840 2014-02-22 09:22:56.840 159
loislane SQL_LOGIN 2014-02-22 09:22:56.843 2014-02-22 09:22:56.843 159
*/
SELECT
name,
type_desc,
LOGINPROPERTY([name], 'PasswordLastSetTime') AS 'PasswordLAstChanged',
CONVERT(datetime,LOGINPROPERTY([name], 'PasswordLastSetTime') ),
CASE WHEN CONVERT(datetime,LOGINPROPERTY([name], 'PasswordLastSetTime') ) IS NULL THEN 9999
ELSE DATEDIFF(dd,CONVERT(datetime,LOGINPROPERTY([name], 'PasswordLastSetTime') ),getdate())
END AS DaysSinceLastChanged--,*
FROM master.sys.server_principals
WHERE type_desc IN('SQL_LOGIN')

Or of course you could do as Lowell said.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

irvidesai SSC Veteran Points: 266 More actions · Answer 4

Thanks for the reply. So the alter login has to be done manually on that accounts that are not in compliance correct? can that be automated? Is there a script that that can be run to lock the accounts that have password expired?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

irvidesai (8/4/2014)
Thanks for the reply. So the alter login has to be done manually on that accounts that are not in compliance correct? can that be automated? Is there a script that that can be run to lock the accounts that have password expired?

If you allow windows policies to manage it, it is automatic. No need to implement the process that Lowell showed. If you want to implement a SQL Solution, then it becomes an additional process to manage and keep in compliance with security policy. You can either automate his solution or you can make it happen manually.

Personally, I opt for just configuring via the Windows Policy. If there is a Domain Policy in effect, then your SQL Logins are already required to follow that policy via inheritance.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

irvidesai SSC Veteran Points: 266 More actions · Answer 6

Thank you for the quick reply and yes I agree with you that windows security will be much easier to apply as well as maintain. But just as a second option wanted to work on Lowell's suggestion and automate that if possible.