New install of SQL Server 2005 (x64) Standard after uninstalling previous version SQL Server (x86) Standard.
After installation, client connections are receiving "Cannot generate SSI Context" error messages. TCP/IP Integrated Authentication is being used.
Kerboros authentication is failing when SQLService is running under a domain account and works when running under local system account by reverting back to NTLM authentication.
How do I get the Server to revert to NTLM authentication when running under a domain account so that the error will go away?
Mike
First, here are great blogs for troubleshooting this problem:
http://blogs.msdn.com/sql_protocols/archive/2005/10/15/481297.aspx
http://blogs.msdn.com/sql_protocols/archive/2005/10/19/482782.aspx
Secondly:
Are you making local connection or remote connection?
If local, do not use protocol prefix like "tcp:" in your connection string. BTW, by OS default, local authentication always use NTLM except on XP.
If remote, use "np:" explicitly in your connection string which requires rmote named pipe enabled.
Finally:
Use tool Setspn.exe -D <spn> "computername" to delete SPN you registered, without spn, kerbroes would not take effect.
HTH
Ming.
|||
I have read these two blogs. My question is how do I get the client to automatically revert to NTLM when using a domain account for the service without receiving the error.
I am making remote connections from SQL Server Studio with both XP and Server 2003 (both display the same behavior), and Remote Connections are enabled on the Server. There is not an actual connection string with a specific protocol prefix, it always uses the default I assume.
We are required to always use TCP/IP and integrated authentication here.
It appears that there is an orphan spn that is causing my problem. If I delete it, this should cause the system to revert to use NTLM and I should be able to change the service to run under an account that is not a Domain administrator, correct?
Thanks for your assistance.
Mike
|||
If a SPN is registered for the SQL Server service, client will find the SPN and Kerboros will be used. If SPN is not registered, client will automatically revert to NTLM. SPN registration/deregistration is done automatically by the SQL Server, a client cannot choose to use or not use it.
Are you sure NTLM is used when you run the server under local system account? please use the following command to confirm it: "select auth_scheme from sys.dm_exec_connections where session_id=@.@.spid "
Several questions:
1) did you gracefully shut down SQL Server (x86) before you uninstall it?
2) Can you stop you sql server and run "setspn -L yourmachinename"? Do you see "MSSQLSvc/....."? how about you run the command when sql server is running under local system and/or a domain account?
Most likely, you did not cleanly uninstall the old sql server and a SPN is left over. Please try to delete it as suggested by Ming LV.
Thanks,
|||
The Active Directory admin has deleted the SPN for this server.
I am still unable to connect to the Server from the SQL 2005 client. The samne error message appears. Next we will wipe out the machine and try a clean install I guess.
|||
1) On your server box, use "Setspn -L <machinename> " check whether SPN like" MSSQLSvc/<your machine FQDN>:1433" goes away.
2) Shutdown your sql server, change the service account to a non-admin account, restart your sql server.
3) Make remote tcp connection.
BTW, sql server does not force kerberoes, it is OS behavior, if OS can not find a SPN in KDC for the server, it would automatically fall back to NTLM.
Thanks!
Ming.
|||
Assuming that (1) your SPN is removed and (2) you are doing remote connection, (3) you were testing connection without rebooting the client machine after removing the SPN in domain controler/AD,
One possibility is that the cached kerberos "tickets" haven't expired yet in your client machine. You can either reboot your client machine or use "klist.exe -purge" to clean the kerberos cache. "klist.exe" comes with w2k3 resource kits.
Running under domain account is recommened. Reinstalling the sql server might help in other cases but certainly not in this situation.
|||
My System Administrator assured me that he deleted the SPN.
This server does not have a copy of setspn on it. Do you know where I can find this utility?
I rebooted the client but am still receiving the saame message.
Funny thing is the machine right next to this one is running SQL Server 2005 withouyt any problem running under the same domain account with identical permissions.
Reinstalling the operating system Windows Server 2003 X64 Standard and reinstalling SQL Server X64 Standard will not help either?
Here is the actual error I am receiving:
Server Name: CARTLABSVR2
Error Number: 0
Severity: 11
State: 0
Procedure: GenClientContext
Program Location:
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.SSPIError(String error, String procedure)
at System.Data.SqlClient.TdsParser.SNISSPIData(Byte[] receivedBuff, UInt32 receivedLength, Byte[] sendBuff, UInt32& sendLength)
at System.Data.SqlClient.TdsParser.ProcessSSPI(Int32 receivedLength)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()
|||
You can get setspn tool from w2k3 resource kit.
Can you test the following.
(1) change your sql serverb service to localsystem account.
(2) restart the service.
(3) test the connection and let me what you get.
(4) shutdown the service cleanly.
(5) restart the server under the domain account you are using now.
(6) test the conenction and let me know what you get.
Can you also post the errorlog info w.r.t to SPN?
|||
When I change the service to use Local System, it works.
When I change it back to use the domain account, it does not work.
Here is the error message that shows up in the error log when I chaged the account the service runs under:
The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
Version: Microsoft SQL Server 2005 - 9.00.1399.06 (X64)
Oct 14 2005 00:35:21
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
Authentication mode is MIXED.
On uninstall:
The SQL Network Interface library could not deregister the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098. Administrator should deregister this SPN manually to avoid client authentication errors.
|||
"The SQL Network Interface library could not deregister the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098. Administrator should deregister this SPN manually to avoid client authentication errors."
Do you also see this message showing up in the shutdown of the sql service under "localsystem" or "domain account"? You shouldn't normally.
So, I need to you have setspn.exe to to verity if you have a orphan spn registried. Klist.exe to verify what you have in your client ticket cache, You can get them from resource kit of sql server 2003.
|||
I downloaded setspn and ran it with the -l flag. The Server name is registered as expected but there are no other listings for the SQLServerSrvc. So an invalid SPN apparently does not exist currently.
|||can you try klist.exe purge and type yes for all. Try to connect. Let me know what you get?|||
The SPN error does not show up when I change form the Local System account.
KList shows this ticket from my client workstation:
Server: MSSQLSvc/cartlabsvr2.ad.garmin.com:1433@.AD.GARMIN.COM
KerbTicket Encryption Type: RSADSI RC4-HMAC(NT)
End Time: 5/1/2006 23:30:17
Renew Time: 5/8/2006 13:30:17
Is this what is causing the problem? How do I get rid if this?
II also asked a co-worker to try to connect that has not connected to this SQL Server previously, and he received the same SSIS Context error....
|||
"klist.exe purge" should clean up your local cache.
As you can see,
KerbTicket Encryption Type: RSADSI RC4-HMAC(NT)
End Time: 5/1/2006 23:30:17 <<< the cache is created just now.
Renew Time: 5/8/2006 13:30:17 <<<< the cache will only expire after a week. The AD policy does not expect you to change account very often.
Let me what you get after you purge off all the ticket. If it failed again, I hope not, let me know what shows up in setspn.exe and klist.exe.
