Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm somewhat stuck. We have a database server which was set up and configured by a third party. It's for a third party product so unfortunately we're stuck with the way they've configured it.

Their brilliant configuration includes using the sa account to make a number of connections. For about a week now we've had connection failures filling up all our event logs, two every ten seconds. I have managed to identify that it is the master database this login failure is for.

It's definitely the sa account accessing the master database. It is not an incorrect password (which is good, because we don't know the sa password - and neither does the vendor, it seems. Yay...) because we're getting error state 16 so I believe the sa account has some problems with the master DB.

It's connecting to everything else without problem - I can see the connections made to all other databases successfully. I have checked the sa account permissions and I really can't see anything wrong. I'm at the point of restoring the master database from a backup two weeks old and wanted to open this up to you awesome people out there in case any of you have come across this before and have any alternative suggestions...

Yours hopefully...

Summer


Edit: Aaron kindly took a look as per his answer so I was going to post up a screen print, but I've been on stackoverflow mostly, and only recently come to the dba site...therefore I don't have 10 reputation yet, and can't post an image. However I've added it to stackoverflow so please check it out here.

share|improve this question
    
What are the specific sa account permissions you are seeing? "Error state 16" - what error code are you seeing? –  jl01 May 14 '12 at 14:48
    
If it is the vendor's funky configuration I would tell the vendor to fix it...just my .02 –  Shawn Melton May 14 '12 at 14:55
    
The good old 18456 (Login failed for user 'sa'. [CLIENT: the host server])...it only fails against the master database. All the other databases are fine. We cannot see that any database restore operations have taken place in the last month, and this incident started at the beginning of last week. –  Elatesummer May 14 '12 at 14:55
    
I would love to tell the vendor to fix it...sadly they have said that they won't fix this, because the only database it has a problem with is the master one, which doesn't impact the running of the application. Even more sadly, our internal support team agree...it's just the database team (me) who has a problem with this. Grrr!!! –  Elatesummer May 14 '12 at 14:56
    
Presumably this application requires connection string information that is used to make a database connection. Based upon what I am reading I'm not seeing how the same connection string is being used for all of the database connections the application makes. –  jl01 May 14 '12 at 15:13
show 5 more comments

2 Answers

We know you are getting 18456 errors. The message (other than the 'Login failed' part) and the error state are much more important for diagnosing. In the SQL Server error log, there will be more information than you get from the application or the dialog in SSMS. In SSMS open Object Explorer for the server in question, expand Management, expand SQL Server Logs, right-click "Current - ..." and choose View SQL Server Log. You should be able to find events like this:

enter image description here

This number and the extended reason are not reflected elsewhere because it's meant to obscure the actual failure from the end user, in case that user is malicious. (For example, the state for wrong password might help them see that they are on the right track.)

You can see a list of all the states I know here, which should help you resolve the issue:

http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx

For state 16 it doesn't make sense that the database is offline or inaccessible, unless someone has somehow demoted sa from the sysadmin role and/or explicitly denied access to master, since master can't be offline. I suspect it is much more likely that their default database has changed, or that the database they tried to connect to explicitly is not online. What happens if you run:

ALTER LOGIN sa WITH DEFAULT_DATABASE = master;

Also, why is your application using the sa login? Have you considered creating a separate, less-privileged account to dedicate to your application?

share|improve this answer
    
Thank you - I already know the error state codes, which is why I specified state 16 originally. As explained previously, I cannot amend this application or view the code for the connection string as it's supplied by a third party vendor. I would dearly love to create an alternative account for this as I would never use the sa account myself but am unable to do this. Re: the exact error I will add the screen shot to my original post but it doesn't have extra detail...thank you for your help!! I found your blog posts helpful for other issues so fingers crossed for this one! –  Elatesummer May 15 '12 at 8:28
    
Couldn't add a screen shot so have linked to the original stackExchange post where I have added it - thank you again, @aaron-bertrand –  Elatesummer May 15 '12 at 10:05
    
What is the default database for the sa account? –  Aaron Bertrand May 15 '12 at 10:36
    
The default database for the sa account is the master database. –  Elatesummer May 15 '12 at 11:33
add comment

This particular error state is NOT related to connection to master database. It means user was trying to connect to a database which was not accessible - probably doesn't exist or is offline.In this case error is logged as if it originated in master db.

It's common error when you provide explicit database name in connection string, and db doesn't exist. Check connection string if you can.

share|improve this answer
    
Ahh, this will explain why it says master. Drat. Sadly I can't see the connection string but I'm due to shout at the vendor again later today so hopefully they will get into gear...thank you! –  Elatesummer May 15 '12 at 8:38
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.