BizTalk WCF-SQL Error: Microsoft.ServiceModel.Channels.Common.ConnectionException: Cannot open database "Database-Name" requested by the login. The login failed

To finalize, maybe, these series of Errors and Warnings… Causes and Solutions let’s describe and address a fairly simple one that from time to time appears: Login Failed. While trying to communicate to a SQL Server database to invoke a stored procedure thru BizTalk WCF-SQL adapter I got this following error:

A message sent to adapter “WCF-Custom” on send port “STAGING_SQL_WCF_SEND” with URI “mssql://SQL-SERVER-NAME//AsyncTransactions?InboundId=ins” is suspended.

Error details: Microsoft.ServiceModel.Channels.Common.ConnectionException: Cannot open database “AsyncTransactions” requested by the login. The login failed.

Login failed for user ‘DOMAIN\BTSHostSrvc’. —> System.Data.SqlClient.SqlException: Cannot open database “AsyncTransactions” requested by the login. The login failed.

Login failed for user ‘DOMAIN\BTSHostSrvc’.

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.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)

at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)

at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)

at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)

at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)

at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)

at System.Data.SqlClient.SqlConnection.Open()

at Microsoft.Adapters.Sql.SqlAdapterConnection.OpenConnection()

— End of inner exception stack trace —

Server stack trace:

at Microsoft.Adapters.Sql.SqlAdapterConnection.OpenConnection()

at Microsoft.Adapters.Sql.ASDKConnection.Open(TimeSpan timeout)

at Microsoft.ServiceModel.Channels.Common.Design.ConnectionPool.GetConnection(Guid clientId, TimeSpan timeout)

at Microsoft.ServiceModel.Channels.Common.Design.ConnectionPool.GetConnectionHandler[TConnectionHandler](Guid clientId, TimeSpan timeout, MetadataLookup metadataLookup, String& connectionId)

at Microsoft.ServiceModel.Channels.Common.Channels.AdapterRequestChannel.OnOpen(TimeSpan timeout)

at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)

at Microsoft.BizTalk.Adapter.Wcf.Runtime.OneWayOperationSendPortRequestChannel`1.OnOpen(TimeSpan timeout)

at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)

at System.ServiceModel.Channels.ServiceChannel.OnOpen(TimeSpan timeout)

at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)

at System.ServiceModel.Channels.CommunicationObject.Open()

Exception rethrown at [0]:

at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)

at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)

at System.ServiceModel.ICommunicationObject.Open()

at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.GetChannel[TChannel](IBaseMessage bizTalkMessage, ChannelFactory`1& cachedFactory)

at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.SendMessage(IBaseMessage bizTalkMessage)

MessageId: {71CDC883-AE8D-4303-A72E-ADA9ECE91981}

InstanceID: {1EA7B77C-924F-4C74-87DE-BCC4D1AD8E6A}

Login Failed: Cannot open database requested by the login

Cause

The cause of the problem is quite obvious and the error message, this time because sometimes is not quite true like we saw in my last post, the error message clearly identifies the origin of the problem.

This problem occurs because the user account that you used to access the database, in my case the BizTalk Host Instance Account, don’t have permissions to connect the database.

Solution

Simple problem, simple solution. You need to grant permission to the user to access database

  • In SQL Server Management Studio, open Object Explorer and expand it to the database that you want to give access
  • Right-click on the Security folder, select “New”, and then click “User”.
  • On the General page, enter username and login in the Login name box.
  • And depending on what you want to do, you may configure the Database role memberships like “db_datareader” or/and “db_datawriter
  • Or, on the Securables page, under Securables, click on the “Search” button.
    • On the “Add Objects” window, select “Specific objects…” and click “OK” and choose the object type, it can be:
      • Give access to a specific database like “Connect
      • Give access only to certain tables of the database like read and write
      • Execute stored procedures
      • And so on.

Login Failed: Select Object types

  • After you select the objects, on the Securables page, you need to give proper permissions, in this case, grant “Connect” to the database

Login Failed: connect database granted

  • Click OK.

Once again, given “sysadmin” or “db_owner” would solve all our problems but it goes against security best practices and sometimes these tables contain sensitive data or personal data, and nowadays with General Data Protection Regulation (GDPR) in EU this sometimes can be a backdoor for other possible problems and teams need to start taking into considerations these security policies.

You always should give the minimum rights to a certain user for him to do the necessary tasks. Nothing more, nothing less… as things should be.

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

BizTalk360
BizTalk Server

Over 500+ customers across
30+ countries depend on BizTalk360

Learn More
Serverless360
Azure

Manage and monitor serverless
components effortlessly

Learn More
Atomicscope
Business Users

Monitor your Business Activity in iPaaS
or Hybrid integration solutions

Learn More

Back to Top