Why can the SA account no longer access the master database?

Question

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.

Answer 1

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:

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:

• https://sqlblog.org/2011/01/14/troubleshooting-error-18456

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?

Answer 2

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.