The Case of the Failed SQL Login

I was asked to investigate a problem with SQL Server 2005, whereby Administrators logging on to the SQL Server couldn’t login to the SQL Server Management Studio. An attempt to login to the Management Studio (or connect by SQLCMD for that matter) would get an Error 4064: Cannot Open Default User Database.

I Bing Searched the Error 4064 and found lots of information on it, but nothing really pointed to a cause or resolution to the problem, however I knew that the problem was related to the master system database.

The vast majority of sites suggested things but none of these worked for me. Being that my skill with SQLCMD is somewhat slim, I decided to aim for the Management Studio.

I knew that there was a problem with the master system database, and being that the master database is the database used to generate user connections, I thought I would try to login with a different database.

SQLLogin1

As per the image above, expanding the available options in SQL Server 2005 gives you the Connect to Database option. I set this to model being that I knew the system database model would exist and I didn’t know the names of any other databases on the server.

Excellent, it got me in. So the first place I looked was to make sure that the the master database was present. I figured it would be pretty difficult for SQL to start without a master database attached but anything is possible I guess.

Sure enough the database was there, but upon trying to view it’s properties, I got the same Error 4064.

I then tried to take a look at the server properties and I got the same Error 4064 again.

So I was poking around MSDN when I found the sp_defaultdb command, and I ran the following:

use master
go
sp_defaultdb [DOMAINUSER], master
go

 

I logged out of the Management Studio and back in, but this time without the Connect to Database statement selected. I was able to login correctly to he normal master database because I had set my own.

This works for one user, but you don’t want to be going around setting the master as the default database for every user in the company otherwise your going to end up with a very long and complicated SQL configuration on your hands.

With the master database now correctly loaded for my user, I was able to view the server and database properties. First up was the master database and everything here looked good, so I moved on to the server. Whilst browsing through the server permissions, I saw this:

SQLLogin2

Notice how the default database location fields are blank? This is all under the Configured Values mode, so I switched to Running Values and they where blank too.

I entered the correct values into these paths (the SQL Server defaults in this case) and it’s working perfectly – Users are able to login (permission granted of course) without needing to specify a default database.

And that closes the case of the failed SQL Server login.

richardjgreen

Richard works as a Cloud Consultant for Fordway Solution where his primary focus is to help customers understand, adopt and develop with Microsoft Azure, Office 365 and System Center. Richard Green is an IT Pro with over 15 years' of experience in all things Microsoft including System Center and Office 365. He has previously worked as a System Center consultant and as an internal solutions architect across many verticals. Outside of work, he loves motorbikes and is part of the orange army, marshaling for NGRRC, British Superbikes and MotoGP. He is also an Assistant Cub Scout Leader.

One thought on “The Case of the Failed SQL Login

  1. Hi, Congratulations to the site owner for this marvelous work you’ve done. It has lots of useful and interesting data.

Comments are closed.