Application fails when I set up SQL Server as peristence

[ssingh (Santosh Singh)]

So until I use InMemoryPersistence, service can start successfully but when I specify it to use SQLServerPersistence, it fails

1. Same connectionstring for SQLPersistence works in another service, and I rechecked Connectionstring atleast 100 times

Here is the error that I get
The thread 0x281c has exited with code 0 (0x0).
Exception thrown: ‘System.Data.SqlClient.SqlException’ in mscorlib.dll
Exception thrown: ‘System.Data.SqlClient.SqlException’ in YCM.DWH.EOD.Manager.Calculation.exe
System.Transactions Critical: 0 : Microsoft Learn: Build skills that open doors in your careerUnhandled exceptionYCM.DWH.EOD.Manager.Calculation.exeSystem.Data.SqlClient.SqlException, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) at System.Data.SqlClient.SqlInternalConnectionTds…ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionPool.WaitForPendingOpen()
— End of stack trace from previous location where exception was thrown —
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Extensions.<OpenConnection>d__2.MoveNext() in C:\BuildAgent\work\c62f716772bc8655\src\SqlPersistence\Extensions.cs:line 38
— End of stack trace from previous location where exception was thrown —
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Installer.<Install>d__4.MoveNext() in C:\BuildAgent\work\c62f716772bc8655\src\SqlPersistence\Installer.cs:line 33
— End of stack trace from previous location where exception was thrown —
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at NServiceBus.InitializableEndpoint.<RunInstallers>d__13.MoveNext() in C:\BuildAgent\work\a93f853f0c1b9532\src\NServiceBus.Core\InitializableEndpoint.cs:line 184
— End of stack trace from previous location where exception was thrown —
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at NServiceBus.InitializableEndpoint.<Initialize>d__1.MoveNext() in C:\BuildAgent\work\a93f853f0c1b9532\src\NServiceBus.Core\InitializableEndpoint.cs:line 68
— End of stack trace from previous location where exception was thrown —
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at NServiceBus.Endpoint.<Start>d__1.MoveNext() in C:\BuildAgent\work\a93f853f0c1b9532\src\NServiceBus.Core\Endpoint.cs:line 27
— End of stack trace from previous location where exception was thrown —
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Runtime.CompilerServices.TaskAwaiter1.GetResult() at YCM.DWH.EOD.Manager.Calculation.Program.&amp;lt;Main&amp;gt;d__1.MoveNext() in D:\WorkSpace\YCM\YCM.Core\Main\Departments\Operations\YCM.DWH.EOD\YCM.DWH.EOD.Manager.Calculation\Program.cs:line 64 --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Runtime.CompilerServices.TaskAwaiter.GetResult() at YCM.DWH.EOD.Manager.Calculation.Program.&amp;lt;Main&amp;gt;(String[] args)</StackTrace><ExceptionString>System.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) ---&amp;gt; System.ComponentModel.Win32Exception (0x80004005): Access is denied at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal&amp;amp; connection) at System.Data.ProviderBase.DbConnectionPool.WaitForPendingOpen() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Extensions.&amp;lt;OpenConnection&amp;gt;d__2.MoveNext() in C:\BuildAgent\work\c62f716772bc8655\src\SqlPersistence\Extensions.cs:line 38 --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Installer.&amp;lt;Install&amp;gt;d__4.MoveNext() in C:\BuildAgent\work\c62f716772bc8655\src\SqlPersistence\Installer.cs:line 33 --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at NServiceBus.InitializableEndpoint.&amp;lt;RunInstallers&amp;gt;d__13.MoveNext() in C:\BuildAgent\work\a93f853f0c1b9532\src\NServiceBus.Core\InitializableEndpoint.cs:line 184 --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at NServiceBus.InitializableEndpoint.&amp;lt;Initialize&amp;gt;d__1.MoveNext() in C:\BuildAgent\work\a93f853f0c1b9532\src\NServiceBus.Core\InitializableEndpoint.cs:line 68 --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at NServiceBus.Endpoint.&amp;lt;Start&amp;gt;d__1.MoveNext() in C:\BuildAgent\work\a93f853f0c1b9532\src\NServiceBus.Core\Endpoint.cs:line 27 --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Runtime.CompilerServices.TaskAwaiter1.GetResult()
at YCM.DWH.EOD.Manager.Calculation.Program.<Main>d__1.MoveNext() in D:\WorkSpace\YCM\YCM.Core\Main\Departments\Operations\YCM.DWH.EOD\YCM.DWH.EOD.Manager.Calculation\Program.cs:line 64
— End of stack trace from previous location where exception was thrown —
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.GetResult()
at YCM.DWH.EOD.Manager.Calculation.Program.<Main>(String[] args)
ClientConnectionId:00000000-0000-0000-0000-000000000000
Error Number:5,State:0,Class:20HelpLink.ProdNameMicrosoft SQL ServerHelpLink.EvtSrcMSSQLServerHelpLink.EvtID5HelpLink.BaseHelpUrlBingHelpLink.LinkId20476System.ComponentModel.Win32Exception, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089Access is denied at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.GetResult()
at YCM.DWH.EOD.Manager.Calculation.Program.<Main>(String[] args)
System.ComponentModel.Win32Exception (0x80004005): Access is denied5
An unhandled exception of type ‘System.Data.SqlClient.SqlException’ occurred in mscorlib.dll
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

[tmasternak (Tomasz)]

Hi Santosh,

it looks like db connection issue. It does not have to be an invalid connectionstring but might be SQL Server having tcp connectivity disabled or default tcp port for SQL Server being blocked on a firewall or yet another thing.

Are you able to connect to your SQL Server in any other way from the same machine your endpoint is running e.g. Management Studio?

Tomek

[ssingh (Santosh Singh)]

Yes, same connection string from different application using uses SQLPersistence works fine.

[ramonsmits]

Are you using integrated security or do you supply a username/password via the connection string? If using integrated security, does that user account have permission to access the database?

[danielmarbach]

Hi

When using integrated security I would also check under which user credentials the application runs. For example if you are running inside Service Fabric by default the services run under network authority (this can be changed).

Regards
Daniel

[ssingh (Santosh Singh)]

Sorry, was away for few days so couldn’t reply

1. There is no issue with connection string because same connection string exist in both application
2. I even created all tables which is generated while building project.