Connection Error

[zgray]

Using access 2013/2010 as a frontend and sql 2012 on a virtual machine as a back end.

Every once in a while users are getting this error

Connection failed:
SQLState: 'S1000'
SQL Server Error:0
[Microsoft][ODBC SQL Server Driver]Cannot generate SSPI context

It's somewhat rare that they get this error and I just tell them to close the database and then reopen it and it usually fixes it. Sometimes they have to log out of their computer and log back in.

Well it happened to my boss so now it's a huge issue. Anything I can do to fix this for good? I'm pretty new to SQL

 

[SQL_Hell]

did you register an SPN to force SQL server to use Kerberos?

 

[zgray]

did you register an SPN to force SQL server to use Kerberos?

I ran this query

SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@spid ;

and it returned NTLM. So I guess im not using Kerberos.

Sometimes the connection fails when the database is started, other times after an hour of it being open. Just seems completely random on when the connection fails and who it fails for.

 

[SQL_Hell]

Ok, well you need to create the SPN (server principle name), you can do this in command prompt using the "SETSPN" function.

Just out of interest your database isn't set to auto_close = on is it?

 

[zgray]

I did the setspn and now the error pops up everytime. No one can log in.

 

[SQL_Hell]

Could you give more information?

what command did you run?

 

[zgray]

I ran

 setspn -a mssqlsvc/<server name>:1433 <domain name\administrator>

I had the database open while I did that and it was fine, no change. I got out and got back in and got the error and everyone else did as well.

I then did

 setspn -d mssqlsvc/<server name>:1433 <domain name\administrator>

And it unregistered it and everything worked. Do I need to restart the server after registering it?

Edit: I ran these commands on the command prompt on the sql server if that matters.