Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

K. Brian Kelley - Databases, Infrastructure, and Security

IT Security, MySQL, Perl, SQL Server, and Windows technologies.

Bad Default Database Leads to a Failed Login Attempt

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.

 

Comments

Posted by Hugo Shebbeare on 8 January 2010

Isn't this due to not setting up the user correctly with a specific database - or maybe this is for an other user that has lost the former default database do to purging/decommisionned applicaitons?

Posted by cmille19 on 10 January 2010

After getting burned by default database issues over the years, I've left master as the default database for all logins. So, instead of relying on a default database setting, the connection string should set the proper database context.

Posted by CodeHQ.net on 10 January 2010

I agree with @cmille19, especially in development shops where configurations change regularly.

Posted by K. Brian Kelley on 12 January 2010

Hugo,

 the user was a member of multiple Windows groups, several of which had default databases set to something other than master. One of these default databases said group didn't have access to. We don't create logins for Windows user accounts. That makes them way to hard to manage at the enterprise level.

Leave a Comment

Please register or log in to leave a comment.