September 28, 2011 at 3:36 am
Hi,
We have SQL server 2008 installed on window 2008. We have restored our production database on this server for testing.
Now we can connect to this DB from local machine using sa password or with one of the window domain user. But when I tried to connect to this DB server from other DB server (SSMS) using same window domain user then it is throwing following error -
TITLE: Connect to Server
------------------------------
Cannot connect to ERPsta1-prac.
------------------------------
ADDITIONAL INFORMATION:
Cannot generate SSPI context. (Microsoft SQL Server, Error: 0)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=0&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
It allows me to connect using 'sa' but i want to connect to this server using window user. Please let me know if anyone know solution for this...
- Harish
September 28, 2011 at 5:00 am
Hi,
This problem can have two root causes.
1. The server is not set up for using kerberos authentication and there for cannot complete double hopping.
2. The SPN is incorrect for the service account.
This is evident because the SQL authentication works (as you are not using domain based authentication).
The following article should shed more light on the subject for you:
http://support.microsoft.com/kb/811889
If this is an emergency, then a work around is to add the SQL Server Name and it's IP, in the host file of the server you are connecting from. This is not a solution but only a temporary workaround. It only tells the server to use NTLM authentication instead of kerberos.
Regards...
September 29, 2011 at 12:43 am
Hi,
thanks for reply.
dont know exact reason but sql server service was running under domain user which I have changed to local system account and now its accessible using domain user account 🙂
- Harish
September 29, 2011 at 1:32 am
Pleasure.
Well it is best practice in regards to security to run the SQL services with a domain account.
The reason it works now, is because it is running under NTLM authentication and not kerberos any more. I would advise that you look at using a domain account as it is best for security and manageability. Local authentication is just not safe.
Regards...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply