check policy = on - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

check policy = on

Author

Topic

acollins74
Yak Posting Veteran

82 Posts

Posted - 2009-04-28 : 14:53:57

Why can i change my password to the same password when check policy is set to on

USE [master]
GO
CREATE LOGIN [usertest] WITH PASSWORD=N'Changemypass1', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
EXEC master..sp_addsrvrolemember @loginame = N'usertest', @rolename = N'securityadmin'
GO

-- Local policy password history enabled and set to 12 iterations
Alter login [usertest] with password='Changemypass1'
--shouldn't this error out

Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) Mar 29 2009 10:27:29 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2009-04-28 : 14:58:25

Because that is how you reset passwords. At least it was in SQL 2005. I doubt it has changed in SQL 2008, though.

EDIT: That should be how you unlock logins. Not reset passwords.

acollins74
Yak Posting Veteran

82 Posts

Posted - 2009-04-28 : 15:20:31

OK.
So my question is, I want to change my password. Why is it, i can change my password to the same password even though check policy is set to on and the local security password policy on the box is enforcing password history.

What am I missing. My goal is to disallow any users to change their password to the same password in order to satisfy the change password policy mandated by the company.

acollins74
Yak Posting Veteran

82 Posts

Posted - 2009-04-29 : 11:11:16

I made some progress on technet stating if the OLD_PASSWORD parameter is used then it will work which I have verified with the following error; 'Password validation failed. The password for the user is too recent to change.'

With that said; to by pass the 'local policy rule' then the user just has to use less of a statement.
ie.Alter login [usertest] with password='Changemypass1'
Is there a way to force the end user to use OLD_PASSWORD?
ie.Alter login [usertest] with password='Changemypass1' old_passord='myNewpassword05'

Otherwise isn't this a gaping hole in the model?

Also note, if changing password via SSMS; the Old_password parameter does not seem to be used.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources