After implementing caching for my ASP MVC 5 app, and using SqlDependency and service broker, everything works fine on the local database. I get notifications when a specific change occurs in the database.
However, after deploying the application to production machine, call to SqlDependency.Start(string connectionString) fails by throwing an exception:
System.Data.SqlClient.SqlException: Login failed for user 'sa'.
with the following stack trace (system calls included, calls within my app code - excluded):
[SqlException (0x80131904): Login failed for user 'sa'.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +6749670
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +815
System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +4515
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +84
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +53
System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover) +368
System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout) +6777754
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance) +6778255
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData) +878
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) +1162
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) +72
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) +6781425
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) +103
System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) +2105
System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) +116
System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) +1089
System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) +6785863
System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry) +233
System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry) +278
System.Data.SqlClient.SqlConnection.Open() +239
Since the local and production database are kept in sync (and one was initially restored from the other one) DB settings are the same - I checked this to be sure, though.
I'm quite certain that service broker is enabled for the production DB.
What kind of IIS configuration should I consider important, which may cause this difference? I'm using Sql Server Express at the moment. Also, I don't use SqlCacheDependency but only SqlDependency.
This isn't a Service broker issue.
The error is: Login failed for user 'sa'
Check your connection credentials. Does your app allow the credentials to be modified at run-time? I'd recommend you do allow flexible configuration at deployment.
Even if you checked, can you guarantee that the deployment SQL server instance has the same SQL credentials, this may be the case even if you restored your development database.
User contributions licensed under CC BY-SA 3.0