C# Windows service SQL Server connection issue

0
votes

On Windows Server 2012, I have created a C# Windows service which runs with a service account.

In constructor I am reading configuration from a SQL Server database using ADO.NET.

When I am trying to start the service, it fails with below exceptions. After a few attempts to start the service, it is getting started.

"The wait operation timed outconnection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement."

Below is the exception message from Logs

The wait operation timed outconnection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement.
This could be because the pre-login handshake failed or the server was unable to respond back in time.
The duration spent while attempting to connect to this server was - [Pre-Login] initialization=21169; handshake=332;

at system.Data.sqlclient.sqlinternalconnection.onError(sqlException exception, Boolean breakconnection, Action'l wrapcloseinAction)
at System. Data. Sqlclient.TdsParser.ThrowExceptionAndwarning(TdsParserStateobject stateobj, Boolean callerHasconnectionLock, Boolean asyncclose)
at system.Data.sqlclient.TdsParserstateoblect.ReadsniError(TdsParserstateobject stateobj, unt32 error)
at system.Data.sqlclient.TdsParserstateoblect.ReadsnisyncoverAsync()
at System. Data. Sqlclient.TdsParserStateob)ect.TryReadNetworkPacket()
at System. Data. Sqlclient.TdsParser.consumePreLoginiiandshake(Boolean encrypt, Boolean trustservercert, Boolean integratedsecurity, Boolean& marscapable)
at system.Data.sqlclient.TdsParser.connect(serverinfo serverinfo, sqlinternalconnectionTds connHandler, Boolean ignoresniopenTimeout, 1nt64 timerExpire, Boolean encrypt, Boolean trustservercert, Boolean integratedsecurity, Boolean withFailover)
at system.Data.sqlclient.sqlinternalconnectionTds.AttemptoneLogin(serverinfo serverinfo, string newPassword, securestring newsecurePassword, Boolean ignoresniopenTimeout, TimeoutTimer timeout, Boolean withFailover)
at system.Data.sqlclient.sqlinternalconnectionTds.LoginNoFailover(serverinfo serverinfo, string newPassword, securestring newsecurePassword, Boolean redirecteduserinstance, sqlconnectionstring connectionoptions, sqlcredential credential, TimeoutTimer timeout)
at system.Data.sqlclient.sglinternalconnectionTds.openLoginEnlist(TimeoutTimer timeout, sqlconnectionstring connectionoptions, sqlcredential credential, string newPassword, securestring newsecurePassword, Boolean redirecteduserinstance)
at system.Data.sqlclient.sqlinternalconnectionTds..ctor(DbconnectionPoolidentity identity, sqlconnectionstring connectionoptions, sqlcredential credential, object providerinfo, string newPassword, securestring newsecurePassword, Boolean redirecteduserinstance, sqlconnectionstring userconnectionoptions, sessionData reconnectsessionData)
at system.Data.sqlclient.sqlconnectionFactory.createconnection(Dbconnectionoptions options, DbconnectionPoolKey poolKey, object poolGroupProviderinfo, DbconnectionPool pool, Dbconnection owningconnection, Dbconnectionoptions useroptions)
at system.Data.ProviderBasembconnectionFactory.createPooledconnection(DbconnectionPool pool, Dbconnection owningobject, Dbconnectionoptions options, DbconnectionPoolKey poolKey, Dbconnectionoptions useroptions)
at system.Data.ProviderBasembconnectionPool.createobject(Dbconnection owningobject, Dbconnectionoptions useroptions, Dbconnectioninternal oldconnection)
at system.Data.ProviderBasembconnectionPool.usercreateRequest(Dbconnection owningobject, Dbconnectionoptions useroptions, Dbconnectioninternal oldconnection)
at system.Data.ProviderBasembconnectionPool.TryGetconnection(Dbconnection owningobject, uInt32 waitFormultipleobjectsTimeout, Boolean allowcreate, Boolean onlyonecheckconnection, Dbconnectionoptions useroptions, Dbconnectioninternal& connection)
at system.Data.ProviderBase.DbconnectionPool.TryGetconnection(Dbconnection owningobject, Taskcompletionsource'l retry, Dbconnectionoptions useroptions, Dbconnectioninternal& connection)
at system.Data.ProviderBasembconnectionFactory.TryGetconnection(Dbconnection owningconnection, Taskcompletionsource'l retry, Dbconnectionoptions useroptions, Dbconnectioninternal oldconnection, Dbconnectioninternal& connection)
at system.Data.ProviderBase.Dbconnectioninternal.Tryopenconnectioninternal(Dbconnection outerconnection, DbconnectionFactory connectionFactory, Taskcompletionsource'l retry, Dbconnectionoptions useroptions) at system.Data.ProviderBasembconnectionclosed.Tryopenconnection(Dbconnection outerconnection, DbconnectionFactory connectionFactory, Taskcompletionsource'l retry, Dbconnectionoptions useroptions) at system.Data.sqlclient.sqlconnection.Tryopeninner(Taskcompletionsource'l retry) at system.Data.sqlclient.sqlconnection.Tryopen(Taskcompletionsource'l retry) at system.Data.sqlclient.sqlconnection.open()

1
c#sql-serverado.netwindows-services
does the error only occur on startup? – Ctznkane525
This is a global exception in start windows service. Make sure your sql connection have a Connection Timeout and put you constructor code in try catch block and log the actual exception. – Fred
@Ctznkane525 yes, once it is started It works with out any issues – Ajay Nikam
@Fred added complete exception message in question – Ajay Nikam
@AjayNikam Base on exception you have SQL connection issue, Check health and viability of your SQL Server – Fred

1 Answers

1
votes

Issue resolved after adding "MultiSubnetFailover=True" in connection string

Thanks everyone for help.