First, I would like to make a request of anyone reading this.  If you post in some forum somewhere about a problem you’ve been experiencing, please take a few moments to also post what the solution was after you’ve gotten your problem figured out.  I spent a good amount of time researching why I was getting the error in the title of this post and while I could easily find posts online describing the issue, it seemed like almost no one took the time to post the solution.  Those of you who do post your solutions, thank you.

This week we’ve been working on installing VMWare VDI/View so that we can do an evaluation of the product.  Part of that installation process involves setting up a SQL Server database for the vSphere and View pieces of the software and though we could have used MSDE or whatever MS is calling it these days, we wanted to mirror a production environment as closely as possible.  This means that we wanted to use SQL Server 2005 as the back end database and use an Active Directory service account to grant access to the database which is the best practice standard in our environment.

After configuring the account in the AD and setting group memberships, I proceeded to add the new service account to the database server, create a database for the user, make the service account the database owner, and grant further access to the msdb system database.  All of that seemed to work perfectly until it was time to create a data source so the vSphere could access the database.  Suddenly, the service account couldn’t connect and the above error was appearing in the activity log on our SQL Server.

Of course, the above error means that the server was able to verify the account as valid, but had denied the logon attempt.  (Note to the MS SQL Server Product Team: this error is useless without knowing WHY the logon was denied.  Makes me wonder who did the QA on this product…) I spent some time searching online forums for a solution and I actually found one post that mirrored my situation exactly.  The only problem was the original person who posted it didn’t mention if others had helped solve the problem.  Instead, they came to the forum, posted their problem, got some advice, and disappeared into the ether to never be heard from again.  I didn’t want to follow the advice in the thread because it seemed like I’d be going in circles.  After all, I just created the account, added it to the server, and granted rights to the account only moments prior to this.  How could anything be different?

In an attempt to isolate the issue, I added one of my test accounts to the server and made it the owner of the database.  I then went back to the vSphere server, added the test account to the local administrators group, and logged in under the test account.  When I then tested the connection to the database, it worked as expected. Since I couldn’t figure out what was different between the test account and the service account, I ended up deleting the credentials from the SQL Server management console, removing all of the rights I had granted, and then finally adding all of that information back to the server.  Strangely enough, that worked.

I have no explanation as to why that worked as a solution, but next time I run into it, this is the first thing I’m going to try.   Hope it helps.