Bad Default Database Leads to a Failed Login Attempt

, 2010-01-07

Yesterday we had a user receiving the infamous failed login attempt error (no, that's not a valid IP):

Login failed for user 'MyDomain\SomeUser'. [CLIENT: 192.168.1.201]
Error: 18456, Severity: 14, State: 16.

Verifying the account in Active Directory, we saw no issues. Looking in the Security event log on the server where SQL Server was running, we saw that the operating system had authenticated the user properly. We knew the user was a member of a group that had access to the SQL Server. So at first, there was some head scratching as to what was going on. Now we make heavy use of domain security groups and it isn't unusual for a given user to have multiple security groups with access to a particular SQL Server. This user was no exception. When I saw the error and saw that everything else looked right, I suspected that one of the logins had a bad default database. My suspicions grew when we explicitly added the user's Windows account as a login to the SQL Server and the user was able to log in.

Now our policies say we're to use security groups, so we couldn't leave the user with access via this method (you don't really want to in a large environment because it makes clean-up difficult). I really suspected a bad default database configuration, so we removed the Windows user login and had the user retry. Sure enough, the error was back. At that point, I asked the user to specify the default database in the configuration. If you are using SQL Server Management Studio, you can do this by specifying the connection options, which you get to by clicking the Options button:

Then click on the Connection Properties tab and manually enter the database name:

If you click the drop down, the client will attempt to connect to the SQL Server with the default database and if there is an invalid default database, you'll get that login error. That's why the name has to be typed in by hand. Using master is a good choice, because every login should have access to the master database. Once the user manually specified the database, he was able to connect. My suspicions were confirmed.

So that raised the question, "Which login was the guilty one?" The user was a member of several security groups and with nesting, it was proving a little time consuming to determine exactly which one was causing him to break. A simple query reveals which logins are not set to use the master database as the default database:

SELECT [name]default_database_name
FROM 
sys.server_principals
WHERE default_database_name <> 'master'
;

And from there, it was a simple matter of checking a handful of logins. One such login, corresponding to a domain security group which the user happened to be a member of, was set to a default database which the login did not have access to. None of the user's other logins had access to that database, either. We had our login to fix. It was set where it had access to its default database, and the login problem was solved.

 

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

2009-02-13

360 reads