SQL Server Login Errors

Hello Dear Reader.  Quick blog today.  If you are working in the SQL Server world eventually you will try to connect to a SQL Server and get an error.  A beautiful wonderful descriptive error that reads, pauses… looks around… stands straight…. In my best impersonation of a classic English poet proceeds to read, “Error: 18456, Severity:  14, State: X”.

Ahhh sweet music.  Okay Dear Reader I confess, it’s definitely not Lord Tennyson.  But the important part is the State.  In SQL Server 2005 they did a really great job of giving us more descriptive error descriptions when it came to the State.

SQL STATE OF MIND

When I first discovered this I made a flash card out of it.  I used to quiz myself on it.  Running production, QA, Test, Staging, and Dev DBA support this came in handy.  Whatever your environment when a user calls and says “I can’t log in”, you can use this as a guide.

ERROR STATE	ERROR DESCRIPTION
1	The Error information is not available.  This normally means that the user doesn’t have permissions to view the actual error state.  Check the SQL Server Error Log on the Instance they were trying to connect to.  If you are logging unsuccessful login attempts the real state will be in the error log.
2 and 5	The User ID is not valid.  Check for misspelled user name, or that the login has been created on the server.
6	Attempt to use a Windows login name with SQL Authentication
7	Login disabled and the password does not match what is on record.  A SQL DBA or someone with Administrative Permissions will need to Enable the Login.  If you get this for SA, then you may not have SQL Server Authentication Enabled.  Even after you enable it the SA may be disabled.
8	You typed in the password wrong, make sure you know what it is so you don’t lock it out.  If you do you’ll get error State 7
9	Invalid password
10	This was specific to SQL 2005 -When you try to use a SQL Server authenticated login to log onto the instance.  -The SQL Server service is configured to use a domain account -The SQL Authenticated logins that receive the "Logon Error: 18456" error message are configured to use Windows domain password policy enforcement More details here: http://support.microsoft.com/kb/925744
11	Login is valid, but server access failed.  From BOL: “One possible cause of this error is when the Windows user has access to SQL Server as a member of the local administrators group, but Windows is not providing administrator credentials. To connect, start the connecting program using the Run as administrator option, and then add the Windows user to SQL Server as a specific login.”
12	Login is valid, but server access failed.  When I have seen this in the past it is because a login is created with specific and restricted permissions.  Instead of setting the default database to the DB in question it is left on a default database that the user cannot access.  Double Check the Default DB for the user.
13	The SQL Server service paused, at this point you need to get the service running.  Any failed login will resolve itself at this point.
16	Occurs when logins do not have access to the target database or the database no longer exists or is offline.
18	Password change is required, and you are probably accessing in such a way that a change password prompt cannot appear
23	SQL Server is shutting down and new incomming connections are attempting to connect.  More Here: http://support.microsoft.com/kb/937745
38	Introduced in SQL 2008 for Login error where the database doesn't exist, Login doesn't have  access to the database
40	This like 16 and 38 occur when the login doesn't have access to the default database or it is offline.  HOWEVER what sets this apart is it accompanies an error 4064. (http://support.microsoft.com/kb/307864)

*NOTE

If you have errors that I haven’t listed let me know and I’ll add them to the list.  Leave your name, Twitter handle, blog address whatever and I’ll make sure to give you credit!

As always Dear Reader, Thanks for stopping by!

Thanks,

Brad

SQL Saturday 232 Schedule is LIVE!

Hello Dear Reader!  Just a quick blog post today.  The Schedule for SQL Saturday 232 is now live!  You probably have heard me tell the story about how the First SQL Saturday was 6 years ago in Orlando.  Many people from the community participated to make it a success, and that success continues because of community members all over the world.  This year as SQL Saturday comes home for its annual event we have a lineup that is unparalleled. 

Currently on the schedule we have 13 active SQL Server MVP’s, 4 Microsoft Employees, and 1 MCM.  Speakers from all over the country are coming to Orlando for this event and now all we need now is you.

Come make a little bit of history with us in Orlando.  All of this doesn't even cover the Lunch, served by speakers and always delicious, or the prizes.  Last year we gave away an X-Box, IPAD, gift cards, software, and many other prizes thanks to our great Sponsors.

SQL MVP Rodney Landrum (@SQLBeat|Blog), Pass Evangelist Karla Landrum (@KarlaKay22|Blog), and your friendly neighborhood SQLBalls worked tirelessly on the schedule.  We scrapped and scrimped to get every slot possible to bring you the BIGGEST SQL Saturday Orlando ever.

More blogs on this during the ramp up to the event, for today all I ask it go check out the schedule by clicking here.  Signup, get some SQL Community, and make sure to stop by and say Hi!

See you there!

Thanks,

Brad

T-SQL Tuesday #44 Wrap UP

Hello Dear Reader, what a busy week we’ve had last week!  I’ve got 3 big Thank You’s that I would like to give. 

First off I’d like to say Thank You to Adam Machanic for allowing me to host T-SQL Tuesday #44.  When I first started blogging, I participated in T-SQL Tuesday to keep myself writing at least once a month.  Hosting one seemed like a very far off goal at the time.  Thank you Adam for coming up with the concept and helping to promote growth for all of us bloggers out there!

Secondly my SQL Family, SQL Friends, and fellow Bloggers.  Without you writing there is no content.  You put your hard earned time into this effort and I Thank you.  Not gonna like I’m getting a little verklempt! 

Lastly Dear Reader, Thank You.  Without you all we may as well not put words to digital paper.

The subject was Second Chances and I was very impressed with the blogs, all around great job everyone.   18 spectacular blogs all about different types of Second Chances.

Boris Hristov (@BorisHristov | Blog) Wrote about the need to surround yourself with individuals that are open to change in their lives.

Mikey Stuewe (@SQLMickey | Blog) Discuses the opportunity to redesign a data model and correct some things via Normalization, which lead to a win all around!

Koen Verbeeck (@Ko_Ver | Blog) Wrote about free time on the job, and how he would approach it now vs. years ago.

Jeffrey Verheul (@DevJef | Blog)  Talks about frustrating co-workers and finding humility by being the co-worker that also makes mistakes.

Rob Farley (@rob_farley | Blog)  Discusses getting better over time, and takes a look back at some of his early blog posts.  Delivered in the way that only Rob Farley can!

Jes Schultz Borland (@grrl_geek | Blog) Blogs about her first experience with SQL Server Clustering and the lessions she’s learned along the way.  She also has the funniest picture of the day, I believe people stopped and looked at me as I laughed.

Kathi Kellenberger (@auntKathi | Blog) Tells us about all the different types of HA/DR that we can utilize for our SQL Server so we can give it a Second Chance!

Steve, our resident Aussie/DBA in Exile, (Blog) talks to us about some of the many different mistakes made.  I believe my groan and the words “ouch” may have audibly escaped my lips stirring my cube mates when I read about the SAN bullet point.  Most importantly he reminds us to celebrate failure as well as success, and how to do it with a sense of humor.

Joey D’Antoni (@jdanton | Blog) A mountain of a man, with a stare that could straighten out T-SQL at 200 paces, tells us all about the moment you realize that you’ve done something wrong, and a time where a QA task was run, but not against QA.  Probably the best quote of the day goes to him “the bead of sweat moment”.  Beautiful description of a feeling that all of us have either had, or will have.

Chris Yates (@YatesSQL | Blog)  had a funny blog about being a young developer and how to avoid those darn message boxes.  A little MacGyverism was in play here.

Oliver Asmus (@OliverAsmus|Blog) shares with us a story of woe when he was a Junior DBA.  It involves a Delete statement that has a where clause, but only the delete portion was highlighted.  The dreaded where clause, we know thee well.  Fortunately a Sr DBA was there to help out and Oliver adds some nice thoughts on explicit transactions.

The always excellent Robert Pearl (@PearlKnows|Blog) gives us some Pearls of Wisdom on sending emails.  In our youth we tend to fire them off rather quickly, sometimes that leads to trouble.  This is great advice for anyone in the business world with an email account!

Lance England (@LanceEngland|Blog) reminds us of all the things that can go wrong when we leave our cell phone at our desk during lunch….. and kick off a large update transaction on prod…… and do not execute a commit or rollback.  The most important part is once you make the mistake how you learn from it.

Stuart Moore (@napalmgram|Blog) reminds us that it isn’t just mistakes that we want a second shot at.  There are quite a few where you did a good or even great job but you know you could have done better with more time.

Martyn Jones (@MartynJones|Blog) Wrote his FIRST T-SQL TUESDAY Blog this week!  First off Thanks for Joining the Party Martyn!  Martyn takes us through an exercise in making sure you’re executing your code in the right environment.  Great Point!  I always like to check SSMS to validate my environment, once bitten twice shy.

Tracy Boggiano (@TracyBoggiano|Blog) tells us all about a mistake that taught her 3 important things that every DBA should have!

The Editor and Chief of SQLServerCentral.com Steve Jones(@way0utwest|Blog) weighs in on SQL Slammer and the havoc that can be done by not patching.  Ahhh 2002, we all remember you well!

Tamera Clark (@TameraClark|Blog) insightfully talks about how second chances are not just for SQL, finding a new career and a place that you are happy is a second chance worth seeking!

This guy named Bradley Ball (@SQLBalls| you know the blog) talked about how to crash production in 30 seconds, and how it was an opportunity to learn about a bug and warn the rest of his team.

WRAP IT UP 

When you look at the people that participated this past month you see MVP’s, an MCM, Consultants, DBA’s, and none of us are perfect.  Mistakes are a part of life, and it isn’t the mistake that is important but how you handle it.

The next time you do I hope you remember that, take a deep breath, and just keep going.  That Second Chance to do things differently will be just around the corner.

As always Thanks for stopping by.

Thanks,

Brad