Database Mirroring login attempt failed with error: ‘Connection handshake failed. An OS call failed: (8009030c) 0x8009030c.

Environment

-> Server JBAG1 is the primary replica and Server JBAG2 is the secondary replica. Server JBAG1 and JBAG2 are part of a failover cluster without shared storage. Server JBAG1 and JBAG2 participate in Always on availability group using synchronous commit mode with automatic failover.

-> Database JBDB is part of a Availability group JBAG. Database in Primary replica seems synchronized, but Replica JBAG2\IN01 is down.

-> Database in secondary replica is not-synchronizing.

-> lets look at the Always on dashboard,

-> Checking the SQL Server errorlog,

JBAG1\IN01

2020-07-03 07:34:13.345 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(The logon attempt failed). State 67.'.  

2020-07-03 07:35:11.500 spid42s A connection timeout has occurred on a previously established connection to availability replica 'JBAG2\IN01' with id [FDBE71F5-C8F7-4A98-AE01-3B4E23451908]. Either a networking or a firewall issue exists or the availability replica has transitioned 

JBAG2\IN01

2020-07-03 07:35:12.380 spid77s A connection timeout has occurred while attempting to establish a connection to availability replica 'JBAG1\IN01' with id [FA3748D6-57FE-4247-8F92-245D2C40418A]. Either a networking or firewall issue exists, or the endpoint address provided for the replica

-> The error message in primary replica JBAG1\IN01 ” 2020-07-03 07:34:13.345 Logon Database Mirroring login attempt failed with error: ‘Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(The logon attempt failed). State 67.’.  [CLIENT: 192.168.0.33]” refers that the primary and secondary replica is not able to talk to each other.

-> Logged into JBAG1 and pinged JBAG2,

-> It was pinging fine. Logged into JBAG2 and tried IPCONFIG,

-> IPAddress for JBAG2 as per ipconfig is 192.168.0.33. There seems to be some issue in JBAG1 where it is resolving to a wrong IPaddress for JBAG2.

-> I checked the hosts file in location C:\windows\System32\drivers\etc on both servers JBAG1 and JBAG2. I was able to see a wrong entry for JBAG2 in the hostfile.

-> I removed the wrong entry and saved the hosts file. It all started working fine,

-> There are scenarios where the same error happens, but pinging to server resolves the correct IPAddress. In those cases verify if SQL Service account password was changed recently and not updated in SQL Server configuration manager. In scenarios like this you will have to try restarting SQL Services in secondary server and check if SQL comes online. If it fails with an credential issue, then update the password in SQL Server configuration manager and that solves the issue.

-> The same error can happen if SQL Service account doesn’t have proper access to the other instance. Verify if access is provisioned properly on all servers in the availability group and provide access if required.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

You must change your password before logging on the first time. Please update your password or contact your system administrator or technical support.

-> I have created an Azure Virtual machine. I was able to login to the Virtual machine without any issues.

-> After 2 days I got below message when I was trying to login to the Virtual machine,

[Window Title]
Remote Desktop Connection

[Content]
You must change your password before logging on the first time. Please update your password or contact your system administrator or technical support.

[OK] [Help]

-> While creating the virtual machine in Azure portal. I just mentioned the admin username and password. But never saw an option to change the password at any time.

-> I decided to reset the password for my account. Clicked on the Virtual Machine -> Under “Support + troubleshooting” -> Selected “Reset password” as shown in below screenshot.

-> I performed a reset of my existing account. Read the info next to Username as shown below,

-> You can also create a new account here and will be added as an admin account.

-> In my case I reset the password for an existing account. I provided the username and then the password to reset the account.

-> I tried logging in and it worked out well.

-> But still I dont know the reason why this happened? I will troubleshoot this further in case this happens again.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

A transport-level error has occurred when receiving results from the server

-> Application logs shows below error everyday intermittently between 12:00 AM to 12:10 AM,

Timestamp: 5/17/2020 10:05:58 PM
Message: HandlingInstanceID: c190c221-3e43-4702-83fa-533122cb3c81
An exception of type ‘System.Data.SqlClient.SqlException’ occurred and was caught.
———————————————————————————-
05/18/2020 00:05:58
Type : System.Data.SqlClient.SqlException, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
Message : A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 – The semaphore timeout period has expired.)
Source : .Net SqlClient Data Provider
Help link :
Errors : System.Data.SqlClient.SqlErrorCollection
ClientConnectionId : c43b433e-4649-48f5-b0bf-05dca3d83432
Class : 20
LineNumber : 0
Number : 121
Procedure :
Server : JBDB01
State : 0
ErrorCode : -2146232060
Data : System.Collections.ListDictionaryInternal
TargetSite : Void OnError(System.Data.SqlClient.SqlException, Boolean, System.Action`1[System.Action])
HResult : -2146232060
Stack Trace : at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
at System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync()
at System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket()
at System.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer()
at System.Data.SqlClient.TdsParserStateObject.TryReadByte(Byte& value)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at Microsoft.Practices.EnterpriseLibrary.Data.Database.DoLoadDataSet(IDbCommand command, DataSet dataSet, String[] tableNames)
at Microsoft.Practices.EnterpriseLibrary.Data.Database.LoadDataSet(DbCommand command, DataSet dataSet, String[] tableNames)
at Data.Export.GetNextCreatedExport()
at Business.Export.GetNextCreatedExport()
at Facade.Export.GetNextCreatedExport()
Additional Info:
MachineName : JBAPP01
TimeStamp : 5/17/2020 10:05:58 PM
FullName : Microsoft.Practices.EnterpriseLibrary.ExceptionHandling, Version=6.0.0.0, Culture=neutral, PublicKeyToken=daf167d5325e0d31
ThreadIdentity : System User
Inner Exception
—————
Type : System.ComponentModel.Win32Exception, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
Message : The semaphore timeout period has expired
Source :
Help link :
NativeErrorCode : 121
ErrorCode : -2147467259
Data : System.Collections.ListDictionaryInternal
TargetSite :
HResult : -2147467259
Stack Trace : The stack trace is unavailable.
Category: Error
Priority: 2
EventId: 100
Severity: Error
Title:Enterprise Library Exception Handling
Machine: JBAPP01
Process Id: 6868
Win32 Thread Id: 11312
Thread Name:
Extended Properties: HelpLink.ProdName – Microsoft SQL Server
HelpLink.EvtSrc – MSSQLServer
HelpLink.EvtID – 121
HelpLink.BaseHelpUrl – http://go.microsoft.com/fwlink
HelpLink.LinkId – 20476

-> The error message at first glance seems like a network issue between Application and database server OR unresponsive database server due to high resource utilization.

-> I setup a perfmon collection on the database server and also started a continuous ping on Application server JBAPP01 pinging Database Server JBDB01 continuously using below powershell command,

ping.exe -t JBDB01|Foreach{"{0} - {1}" -f (Get-Date),$_} > C:\temp\ping\JBDB01.txt

-> The same issue happened. I checked the perfmon counters on the database server. Resource utilization was normal.

-> SQL Server error log and Event Viewer logs on database server did not have any errors during the time the error happened on the application server.

-> Checked the output of the ping command on Application Server and got below details,

5/18/2020 12:05:45 AM – Reply from 192.36.67.12: bytes=32 time<1ms TTL=127
5/18/2020 12:05:46 AM – Reply from 192.36.67.12: bytes=32 time<1ms TTL=127
5/18/2020 12:05:51 AM – Request timed out.
5/18/2020 12:05:56 AM – Request timed out.
5/18/2020 12:06:01 AM – Request timed out.
5/18/2020 12:06:06 AM – Request timed out.
5/18/2020 12:06:11 AM – Request timed out.
5/18/2020 12:06:12 AM – Reply from 192.36.67.12: bytes=32 time<1ms TTL=127
5/18/2020 12:06:13 AM – Reply from 192.36.67.12: bytes=32 time<1ms TTL=127

-> From the above output it is clear that the application is experiencing connectivity errors when there is packet loss as indicated by “Request Timed Out” from above output.

-> This was taken up with Network team. They worked with Windows team and advised that the packet loss are as a result of daily Server snapshot that was performed at that time.

-> Application team updated us that the functionality that is failing every day between 12:00 AM to 12:10 AM is some sort of health monitor that checks if all components of the Application including Database server is healthy. Since the error doesn’t have any business impact, they changed the schedule to skip between 12:00 AM to 12:15 AM and this resolved the issue.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.