This is the error I see:
"Cannot connect to database master at SQL Server xxxxx. The database might not exist, or the current user does not have permission to connect to it."
I was connecting to the SQL Server 2008 on a Win2k8 R2 machine using Windows Authentication.
I initially suspected it might be a Domain Controller issue so I tried checking the Firewall configuration and Inbound rules for the SQL server. In this case the Firewall was turned off and ports 1433, 1434 were allowing incoming traffic so that does not solve my problem.
I also tried enabling TCP/IP on the SQL Server using the SQL Server Configuration Manager. Still not working.
Next I tried remoting into another server as the same user, launching SQL Server Management Studio and connecting, another error came up:
"Cannot generate SSPI context."
Checking this Microsoft KB usually solves the issue for the majority of cases but it did not help.
By now I have a general idea that this is probably because of some settings on the SQL server itself.
After parsing dozens of articles I finally came across THE one that resolved my issue.
http://dotnettim.wordpress.com/2010/08/26/cannot-generate-sspi-context-error-from-sql-server-management-studio/
Basically the SPN (Service Principal Name) was somehow generated wrongly and could not be deleted. You could view a list of the SPNs by running this command:
setspn -L
I compared it with the SPNs of other SQL servers and there was a couple of extra SPNs like this:
MSSQLSvc/
MSSQLSvc/
I was unable to delete the SPN using setspn command due to lack of account privileges.
Resolution:
1. Stop SQL Server Agent. Modify the SQL Server service to run under the Local System account.
2. Start SQL Server.
3. Stop SQL Server.
4. Restore the SQL Server service to run under the account that it was set up with initially.
5. Start SQL Server Agent.
6. Run setspn –L
*Poof* problem solved!
