Hi there

Well well well, blow me down if I didnt get the error yesterday! after thinking that will all the instals i have done I never hit it before.

Your article is good, but really needed some screen shots, and more step by step processes to resolve the issue(s).

In my case I was using a domain user account to run the sql server and sql-agent services. During install, i removed named pipes from the provider list for my listener. All worked fine for a while, even after some reboots, I applied SP3 and things went haywire with the SSPI error.

On loging into the servdr as the sqlserveradmin user account i created, the instance would start, but attempting to connection via windows authentication to the instance resulted in the SSPI context error. I changed it back to local system and all worked fine. The agent service was also giving me an error:

SQL Agent Error:

[165] ODBC Error: 0, Cannot generate SSPI context [SQLSTATE HY000]

I download setspn from http://www.petri.co.il/download_free_reskit_tools.htm

and run the following:

C:\Program Files\Resource Kit>setspn -L royntsq02

Registered ServicePrincipalNames for CN=ROYNTSQ02,CN=Computers,DC=zzzzzz,DC=xxxxx,DC=wa,DC=xxx,DC=au:

MSSQLSvc/xxxxxxxx.zzzzz.xxxxxx.wa.xxx.au:2433

HOST/ROYNTSQ02

all looking good, but found the nslookup failed:

C:\Program Files\Resource Kit>nslookup

DNS request timed out.

timeout was 2 seconds.

*** Can't find server name for address 163.232.6.19: Timed out

DNS request timed out.

timeout was 2 seconds.

*** Can't find server name for address 163.232.6.22: Timed out

*** Default servers are not available

Default Server: UnKnown

not good. The system admins resolved the issue, only to get the error "there is a time difference between the client and the server" when changing the service account for sql server back to the sqlserveradmin user.

Again they resynced, I rebooted the box and all works a treat over TCP/IP and the sqlserveradmin user account.

Cheers

Ck