April 22, 2010 at 9:25 am
Dear All,
Please help me in resolving this problem or at least in identifying root cause of problem:
No user (sql or widows, remotely or locally) was able to login and jobs were failing due to login failure.
Following errors were found in the logs:
SSPI handshake failed with error code 0x80090308 while establishing a connection with integrated security; the connection has been closed. [CLIENT: <local machine>]
Login failed for user ''. The user is not associated with a trusted SQL Server connection. [CLIENT: <local machine>]
After that all the jobs started failing due to error ‘unable to determine if the owner(username) of job has server access (reason: The certificate, asymmetric key, or private key data is invalid. [SQLSTATE 42000] (Error 15297)).
Server TCP provider has stopped listening on port [ 1433 ] due to a failure. Error: 0x2739, state: 1. The server will automatically attempt to reestablish listening.
SQL services are running on domain user which is in admin group. My instance is running NTLM and SQL authentication. The user was not blocked and the password also didn’t changed. I could not find any reason why it happened to me.
Finally I recycled SQL services to resolve the issue and till now it has not occurred again. I am wondering if anyone else has faced same issue anytime and can help me to find out and trobleshoot real issue.
Additional info:
My instance is SQL 2005 (ver 9.00.3042.00),SP2 and Standard Edition. Its running on Microsoft Windows Server 2003, Enterprise Edition; Version 5.2.3790 Service Pack 2 Build 3790.
I don’t think there was any problem with domain controller at that time because my other instances are also running on the same user and i didn't face any issue there. When I logged in on the server it connected well.. I pinged from other servers it pinged fine.
April 22, 2010 at 9:57 am
Possibly a problem with authentication to the domain controller. AD users trying to log in would genereate that error- USUALLY sql accounts would not be affected, but if the service stopped that's a different story. Perhaps you can't authenticate the SQL Service account either.
Can you log in to the server as the same account that is running the SQL Service?
April 22, 2010 at 10:05 am
You have to register the SPN to the domain controller.
This URL should assist you in doing the same.
http://blogs.msdn.com/sql_protocols/archive/2005/10/12/479871.aspx%5B/url%5D
Remember to run the setspn command at domain controller. And a domain Administrator should run this command.
You must be a member of the Domain Administrators group to run the SETSPN command.
For standalone server run the below command:
setspn -A MSSQLSvc/<FQDN>:<Port> <SQL_Service_Account>
FQDN : Fully qualified name
Port: SQL Server port number
SQL_Service_Account : Account under which sql server is running.
April 22, 2010 at 11:01 am
I think Error: 0x2739, state: 1 is the key to finding the cause of this problem. Is there any additional information in the system or application event logs around the time or immediately prior to the failure? Also, is there anything "unusual" about the system: recent changes? VM environment? I/O type errors in the logs? Network type errors in the logs?
0x2739 gives us cryptic information: A protocol was specified in the socket function call that does not support the semantics of the socket type requested.
April 22, 2010 at 1:29 pm
NJ-DBA (4/22/2010)
I think Error: 0x2739, state: 1 is the key to finding the cause of this problem. Is there any additional information in the system or application event logs around the time or immediately prior to the failure? Also, is there anything "unusual" about the system: recent changes? VM environment? I/O type errors in the logs? Network type errors in the logs?0x2739 gives us cryptic information: A protocol was specified in the socket function call that does not support the semantics of the socket type requested.
There was no information in system logs in that near past..I am afraid I don't have all the application logs now to dig out more details .There was no system change. My machine is physical..
May 12, 2010 at 12:36 pm
I am still clueless on this. I would really appreciate any help..
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply