Showing posts with label SQL 2012. Show all posts
Showing posts with label SQL 2012. Show all posts

Friday, July 26, 2013

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.


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.

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.
Attempt to use a Windows login name with SQL Authentication
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.
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
Invalid password
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:
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.
The SQL Server service paused, at this point you need to get the service running.  Any failed login will resolve itself at this point.
Occurs when logins do not have access to the target database or the database no longer exists or is offline.
Password change is required, and you are probably accessing in such a way that a change password prompt cannot appear
SQL Server is shutting down and new incomming connections are attempting to connect.  More Here:
Introduced in SQL 2008 for Login error where the database doesn't exist, Login doesn't have  access to the database
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. (


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!



Tuesday, January 22, 2013

Pro SQL Server 2012 Practices Chapter 18 Review Tuning for Peak Load

I'm on a Book!

Hello Dear Reader, early last year I was asked if I would like to contribute to a book.  The concept was get a lot of really great SQL People together and let them write a chapter on a subject that they were passionate about.  Eighteen different top SQL professional’s, at least two MCM’s, many MVP’s, Shake Stir and you get our book, Pro SQL Server 2012 Practices.

Having only written once chapter I hadn’t read the others.  I had an idea I’d read the chapters and then blog reviews.  I shared this idea with Mr. Grant Fritchey (@GrantFritchey|Blog), check out his review of Chapter 12 yesterday,  and he suggested that we get all of the authors to blog reviews.  A lot of people signed on and we’re releasing our reviews one at a time. 

My first review is on Ben Debow’s (@BBQSQL | Website) chapter Tuning for Peak Load.  Ben is a co-founder of SQLHA with MVP Alan Hirt(@SQLHA | Blog).  Ben is a speaker, very active in the SQL Community, and an all around expert.

“So Balls,” you say, “Get to the review all ready!”

Alright Dear Reader, away we go!


Ben does an amazing job of stepping through many different tools that you can use to assess your environment.  First Ben identifies what Peak Load is.  He talks about the people that should be involved in the process of identifying and tuning for this period, and really steps through the business logic of why these different people should be involved.  Your mileage may vary based on how large or small your shop is, but in bigger shops he is spot on.  He goes on to talk about how you identify where you are today.

This is an important concept.  You cannot measure improvements, or if changes were detrimental, without first knowing as much as possible about your current environment.  We start at a 10,000 foot view of a setting up a topology diagram, Ben also lists a detailed table of Attributes to gather on your servers.  Next up we begin doing a performance assessment.

Ben weaves his way through Perfmon, gives you counters to monitor, and reasons for why you would want to collect them.  We move into a discussion of how to gather profiler data and recommendations what counters you would want to collect.

We move next into Observations.  Ben walks through metric’s he has collected and what they tell him.  This is invaluable to a DBA.  You often hear professionals say “Collect this data” if you’re lucky you hear them say “You want these numbers”, in this case he tells you how he interprets the numbers and what they could mean.  I’m stressing could, because this will help you in diagnosing your server, but each environment will be different.

We move into using PAL,, to interpret and report on our Perfmon counters we’ve been collecting.  A quick aside if you want to set up PAL and get it working  read the documentation, there are two add ins.  One is tools for Office 2013 Web Components, another is Microsoft Log Parser, not listed but required as well is Microsoft Chart Controls for Microsoft.NET Framework 3.5.  If you do not have that last one you’ll get a nice little .NET error when trying to generate the report.

From there we move onto DMV’s and gathering index statistics.  Ben discusses gathering Index Usage Statistics from sys.dm_db_index_operational_stats, no script is listed to verify, but the columns discussed are from sys.dm_db_index_usage_stats.  As long as you get the right DMV the content is solid and I found the Costly Indexes description very interesting!

Finally Ben helps you devise a plan to actually implement the analysis into a plan you can implement.  The thing I love is that you can ask 20 brilliant SQL minds to do the same thing, and you’ll get 20 different variations of the same thing.  Ben looks for some things that I had not considered.  I enjoyed the chapter immensely and look forward to implementing what I’ve learned.

If you pick up the book please feel free to drop me a line and tell me what you think!

As always Thank You for stopping by!



Tuesday, January 15, 2013

Pro SQL Server 2012 Practices

Hello Dear Reader.  Every now and then professionally you get to be a part of something really cool.  Back in the spring of 2012 Jonathan Gennick of Apress contacted me about participating in a book.  His idea?  Get together a lot of really amazing SQL Server Professionals in order to write on subject they were passionate about.

The list of professionals involved was one that made me immediately want to own the book.  When I got the chance to help I jumped!  I wasn't alone.

The companies whose employees collaborated on this book come from Red Gate SoftwareSQL Skills,Brent Ozar Unlimited, Pragmatic Works, and many others.  They are MVP's, MCM's, and regular old SQL Community folk like myself.

We all decided to write blogs reviewing/describing each others chapters.  I will be listing and updating them here.

If you're interested here is a link for the book on Amazon.  Big Thanks to Apress, Jonathan, and my fellow Authors!


Jes Schultz Borland @grrl_geek

1/14/2013   Book Review: Pro SQL Server 2012 Practices Indexing Outside the Bubble

Herve Roggero  @hroggero

1/15/2013 Chapter Review:  The Utility Database (By Chris Shaw) and how it applies in Cloud Computing 

I'll be updating this page on my blog daily as we crank out new reviews.  All the authors have pitched in and we'll be writting them out one day at a time.  Tomorrow SQL MVP Chris Shaw (@SQLShaw | Blog) all around awesome guy will have his review  on Compliance and Auditing. Thursday the Dr.  of Database Design himself SQL MVP Louis Davidson(@drsql | Blog) is up on Release Management.

Hope to see you then!