April 3, 2010 at 11:45 pm
Every now and then, we keep getting this error on our SQL 2005 database:
"SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed"
The error is followed by the error below:
"login failed for user ". the user is not associated with a trusted SQL server connection"
There havent been any changes made on the SQL server at all.
Someone please advise. I have no idea why this is happening
Thanks & Regards
April 4, 2010 at 5:02 am
I seem to recall that this might be caused by having a space after the server name in the connection string, but there might be many possible causes.
There are answers to most connectivity problems on the SQL Server Protocol Team's blog:
http://blogs.msdn.com/sql_protocols/archive/2005/09/28/474698.aspx
http://blogs.msdn.com/sql_protocols/archive/2005/12/22/506607.aspx
http://blogs.msdn.com/sql_protocols/archive/2005/10/29/486861.aspx
...etc.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 4, 2010 at 5:58 am
SPNs can be a problem here too
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 4, 2010 at 6:36 am
Perry Whittle (4/4/2010)
SPNs can be a problem here too
I wonder if the server is clustered?
I seem to remember another possible cause related to domain membership, but can't pretend to be sure about the details. Shame this is not a 2008 server - the OP could use the excellent connection problem debugging tool featured on the blog I referenced.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 4, 2010 at 12:06 pm
Note to self...read before posting :hehe:
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
April 4, 2010 at 12:17 pm
MeltonDBA (4/4/2010)
I have not had a chance to use it but would be interested to hear if it works for you or not. The app was published on codeplex.com under Microsoft's public licensing.
I guess you missed my post immediately prior to yours, then? 😛 😉 😀
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 4, 2010 at 12:30 pm
Paul White NZ (4/4/2010)
MeltonDBA (4/4/2010)
I have not had a chance to use it but would be interested to hear if it works for you or not. The app was published on codeplex.com under Microsoft's public licensing.I guess you missed my post immediately prior to yours, then? 😛 😉 😀
Eh...sure did:-D
Shame this is not a 2008 server - the OP could use the excellent connection problem debugging tool featured on the blog I referenced.
Was this tool you referenced being restricted to 2008? I don't recall reading anything that said it was.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
April 4, 2010 at 12:59 pm
MeltonDBA (4/4/2010)
Was this tool you referenced being restricted to 2008? I don't recall reading anything that said it was.
Yes - same one! It uses Extended Events (new in 2008, of course), so the 'Limitations' section in the link you posted says: "Limitations: The tool can work with SQL Server 2008 and later versions;..."
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 4, 2010 at 5:19 pm
Hi Shilpa,
We also used to get this error messages. After analyzing, I came to know that when ever the Domain controller restarts, there will be loss of network communication and which causes the SSPI handshake failed error & login failure error. So you may need to talk to your System/Network admin to know what exactly happening at the time you were receiving these errors
Hope this helps
April 5, 2010 at 4:50 am
None of the DCs were rebooted. I dont think that's an issue
April 5, 2010 at 7:59 am
I've seen this under the following circumstances:
- The server was intially installed using "LocalSystem" (which allowed it to register the SPN with the domain).
- The service was later changed to run under a domain user account (which no longer is allowed to register with the domain).
- Some domain level cleanup occurs and the SPN is no longer registered.
Solution for me was to give appropriate permissions on the domain to the account running the service (or switch to LocalSystem account) and restart the service. I believe the permission required was Advanced Active Directory permission "write public information" to the SQL Service Account...
correction- i think it was “Read servicePrincipalName” and “Write servicePrincipalName”
April 5, 2010 at 9:51 am
Shilpa,
Please post the solution once you resolve the issue. I'm eager to know the solution as I'm thinking it' due to restart of DC's
April 5, 2010 at 11:20 am
Is it possible that you have a client from a different domain attempting to access the server- I've also seen that error when that happens... the error log should include the IP address of the client.
April 5, 2010 at 12:43 pm
Yes, the error includes the IP address of the client. However, client and server are on the same domain
April 5, 2010 at 12:55 pm
When this issue occurs, do you have any difficulty in pinging the server or establishing an RDP session with the remote server?
Another item to check would be in relation to password changes. Have passwords recently expired?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply