My Mistake For Your Enjoyment

This morning when an aggravated user contacted me that they could not get into a new database that I setup on Friday, I discovered that I had made a mistake.  Well that’s never happened before, well not today anyway. 

Alright, here we go.  I created three new logins that would access development and acceptance copies of a production database that I had just setup on an acceptance server using Red Gate‘s SQL Compare and SQL Data Compare (which I will blog about soon, excellent product).  In my possession, actually a sharepoint list, I had the password for each of these three new logins.  Today I found out that the password I had listed was out of date.  No problem, easy fix!

Using SSMS I would easily change the passwords and then update my sharepoint list to the new passwords, this is DBA 101 stuff.  Not so fast my friend!  SSMS then presented an error stating that the password did not meet our ultra-rigid fort knox password policy (see posts on security).  No problem, I forgot to uncheck the ‘Enforce password policy’ check box.  That happens all the time when I get in a hurry.  Now it is unchecked and SSMS presents the following error:

The CHECK_POLICY and CHECK_EXPIRATION options cannot be turned OFF when MUST_CHANGE is ON.

There was my real mistake committed on Friday;  I forgot to uncheck the ‘User must change password at next login’ check box when I created the logins.  It was a rookie mistake, but I was in a hurry to get things done so I could go and get my root canal in the afternoon.  Luckily for me this was not a resume updating event.  In order to fix this we need to do the following:

USE MASTER
GO
ALTER LOGIN [userlogin] WITH PASSWORD ‘original password’
GO
ALTER LOGIN [userlogin] WITH CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF;

Then I was able to change the password, which I used ALTER LOGIN since I had it in the query window.  What a way to start the week off.  Enjoy!

Advertisements

About SQLGator

Florida Gator and Microsoft Data Platform MVP, Star Wars fanatic and is there anything else...oh yeah PS4! I am a geek and SQL Server DBA who dabbles with VMWare, there are other technologies greater than these? Not so fast my friend! I also love to travel to new and exotic places.

Posted on February 20, 2012, in Lessons Learned, Security, Troubleshooting and tagged , , , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: