Cannot generate SSPI Context

• dwh-1095413

  Default port

  Points: 1415

  More actions

  March 22, 2010 at 1:33 pm

  #216900

  I have 2 servers:

  Server A is on Windows 2003 R2 with SQL Server 2005

  Server B is on Windows 2008 with SQL Server 2008

  Both were working perfectly fine. We wanted to swap the machines so I renamed Server A Server B and changed ip addresses and renamed Server B with Server A.

  Now Server B is the one that is Windows 2003 R2 with SQL 2005 and Windows Authentication works fine.

  Server A is now on Windows 2008 with SQL Server 2008. If you log onto the machine locally, windows authentication works. If you log into SQL from a remote machine, windows authentication gets the SSPI Context error.

  Both machines are part of a domain and they both were using before and after the name switch a domain account as the service to run SQL.

  What could be causing this?

• WayneS

  SSC Guru

  Points: 95460

  More actions

  March 22, 2010 at 1:37 pm

  #1137819

  dwh-1095413 (3/22/2010)

  ---

  I have 2 servers:

  Server A is on Windows 2003 R2 with SQL Server 2005

  Server B is on Windows 2008 with SQL Server 2008

  Both were working perfectly fine. We wanted to swap the machines so I renamed Server A Server B and changed ip addresses and renamed Server B with Server A.

  Now Server B is the one that is Windows 2003 R2 with SQL 2005 and Windows Authentication works fine.

  Server A is now on Windows 2008 with SQL Server 2008. If you log onto the machine locally, windows authentication works. If you log into SQL from a remote machine, windows authentication gets the SSPI Context error.

  Both machines are part of a domain and they both were using before and after the name switch a domain account as the service to run SQL.

  What could be causing this?

  If you run select * from master.sys.servers, do the results match the specified (new) computer names?

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• TheRedneckDBA

  SSChampion

  Points: 14001

  More actions

  March 22, 2010 at 1:47 pm

  #1137822

  The name change is probably your issue as Wayne said (or at least it's one issue you need to deal with if you haven't already).

  If it doesn't match when you do select * from sys.servers and/or select @@servername, do the following:

  sp_dropserver '<old_server_name>'

  GO

  sp_addserver '<new_server_name>', local

  GO

  and then restart the SQL server service.

  The Redneck DBA

• dwh-1095413

  Default port

  Points: 1415

  More actions

  March 22, 2010 at 1:48 pm

  #1137824

  This query returns 7 results:

  id 0 is the name that the local server should be. The is_linked column is 0.

  The rest are linked servers and the is_linked is set to 1.

  WayneS (3/22/2010)

  ---

  dwh-1095413 (3/22/2010)

  ---

  I have 2 servers:

  Server A is on Windows 2003 R2 with SQL Server 2005

  Server B is on Windows 2008 with SQL Server 2008

  Both were working perfectly fine. We wanted to swap the machines so I renamed Server A Server B and changed ip addresses and renamed Server B with Server A.

  Now Server B is the one that is Windows 2003 R2 with SQL 2005 and Windows Authentication works fine.

  Server A is now on Windows 2008 with SQL Server 2008. If you log onto the machine locally, windows authentication works. If you log into SQL from a remote machine, windows authentication gets the SSPI Context error.

  Both machines are part of a domain and they both were using before and after the name switch a domain account as the service to run SQL.

  What could be causing this?

  If you run select * from master.sys.servers, do the results match the specified (new) computer names?

• dwh-1095413

  Default port

  Points: 1415

  More actions

  March 22, 2010 at 1:51 pm

  #1137826

  The @@servername is not the same. I have run the 2 queries but I cannot restart sql server until after office hours.

  I will see if this is causing the issue.

  Jason Shadonix (3/22/2010)

  ---

  The name change is probably your issue as Wayne said (or at least it's one issue you need to deal with if you haven't already).

  If it doesn't match when you do select * from sys.servers and/or select @@servername, do the following:

  sp_dropserver '<old_server_name>'

  GO

  sp_addserver '<new_server_name>', local

  GO

  and then restart the SQL server service.

• WayneS

  SSC Guru

  Points: 95460

  More actions

  March 22, 2010 at 2:06 pm

  #1137830

  dwh-1095413 (3/22/2010)

  ---

  id 0 is the name that the local server should be. The is_linked column is 0.

  ...and...

  The @@servername is not the same. I have run the 2 queries but I cannot restart sql server until after office hours.

  I will see if this is causing the issue.

  Jason Shadonix (3/22/2010)

  ---

  The name change is probably your issue as Wayne said (or at least it's one issue you need to deal with if you haven't already).

  If it doesn't match when you do select * from sys.servers and/or select @@servername, do the following:

  sp_dropserver '<old_server_name>'

  GO

  sp_addserver '<new_server_name>', local

  GO

  and then restart the SQL server service.

  Sounds like the service needs restarted... @@servername loads it's info on startup; if you change it (via Jason's code), it does not change.

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• dwh-1095413

  Default port

  Points: 1415

  More actions

  March 22, 2010 at 2:14 pm

  #1137833

  Thanks I plan on restarting the services later on tonight and testing. I will post afterwards if this fixed the issue or not.

  However, if this does not fix the issue do you believe running a sql 2008 repair would help?

  WayneS (3/22/2010)

  ---

  dwh-1095413 (3/22/2010)

  ---

  id 0 is the name that the local server should be. The is_linked column is 0.

  ...and...

  The @@servername is not the same. I have run the 2 queries but I cannot restart sql server until after office hours.

  I will see if this is causing the issue.

  Jason Shadonix (3/22/2010)

  ---

  The name change is probably your issue as Wayne said (or at least it's one issue you need to deal with if you haven't already).

  If it doesn't match when you do select * from sys.servers and/or select @@servername, do the following:

  sp_dropserver '<old_server_name>'

  GO

  sp_addserver '<new_server_name>', local

  GO

  and then restart the SQL server service.

  Sounds like the service needs restarted... @@servername loads it's info on startup; if you change it (via Jason's code), it does not change.

• WayneS

  SSC Guru

  Points: 95460

  More actions

  March 22, 2010 at 2:40 pm

  #1137845

  No. If this isn't your issue, then I'd look into the network. This error message pops up a lot with Kerboros authentication failure messages, so I'd start by looking there.

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• Nicholas Cain

  SSC-Dedicated

  Points: 33009

  More actions

  March 22, 2010 at 8:02 pm

  #1137955

  Additionally, check to make sure that the SPN is registered correctly.

  ---

  
  Shamless self promotion - read my blog http://sirsql.net

• timr-902336

  SSC Enthusiast

  Points: 189

  More actions

  March 23, 2010 at 3:30 am

  #1138043

  We had this occur when the service account was changed & the server wasn't shut down cleanly so the the spn was no longer registered correctly.

  We had to shut down sql change the service account back to the previous account bring sql back up, then shut down the sql server cleanly then change the service account to the correct account & bring sql back up, this resolved this issue for us.

• dwh-1095413

  Default port

  Points: 1415

  More actions

  March 23, 2010 at 10:20 am

  #1138372

  Thanks this method fixed the issue we were having. Thanks again.

  dwh-1095413 (3/22/2010)

  ---

  Thanks I plan on restarting the services later on tonight and testing. I will post afterwards if this fixed the issue or not.

  However, if this does not fix the issue do you believe running a sql 2008 repair would help?

  WayneS (3/22/2010)

  ---

  dwh-1095413 (3/22/2010)

  ---

  id 0 is the name that the local server should be. The is_linked column is 0.

  ...and...

  The @@servername is not the same. I have run the 2 queries but I cannot restart sql server until after office hours.

  I will see if this is causing the issue.

  Jason Shadonix (3/22/2010)

  ---

  The name change is probably your issue as Wayne said (or at least it's one issue you need to deal with if you haven't already).

  If it doesn't match when you do select * from sys.servers and/or select @@servername, do the following:

  sp_dropserver '<old_server_name>'

  GO

  sp_addserver '<new_server_name>', local

  GO

  and then restart the SQL server service.

  Sounds like the service needs restarted... @@servername loads it's info on startup; if you change it (via Jason's code), it does not change.