BizTalk WCF-SQL Error: System.NotSupportedException: The SqlDbType “” is not supported

BizTalk WCF-SQL Error: System.NotSupportedException: The SqlDbType “” is not supported

Recently while I was testing an integration solution on a client that uses WCF-SQL to insert data on a SQL Server database I got the following error:

System.NotSupportedException: The SqlDbType “” is not supported. Modify your table, view, stored procedure, or function definition to avoid having parameters or columns of this type.

This was a surprise since everything was working properly in the development environment.

Cause

In this solution, we were doing bulk insert on a SQL Server database by using a User-Defined Types as the input to the stored procedure, in our case a User-Defined Table Type. There are other ways to do a SQL Server bulk insert or update in BizTalk Server but definitely, this is the best approach. And this is one of the reasons why we are getting on the error message with the keyword: SqlDbType.

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 on the User-Defined Type associated to the Store procedure that we were invoking.

Solution

To solve this issue, you must give access to the user, in my case BizTalk Host Instance Account to properly execute the stored procedure, you must:

  • Open SQL Server Management Studio and connect to your server.
  • In the Object Explorer, select and expand the desired database and expand the Security folder and then the Users.
  • Right-click on the User, BizTalk Host Instance Account, and choose Properties.
  • On the Database User windows, choose the Owned Schemas tab, and then on the Schemas owned by this user panel select the schema bind to your User-Defined Table Type.
    • In our case: Material
  • Click OK and try again to send the data to your SQL Server. It should work.

The post BizTalk WCF-SQL Error: System.NotSupportedException: The SqlDbType “” is not supported appeared first on SANDRO PEREIRA BIZTALK BLOG.

BizTalk Pipeline Components Extensions Utility Pack: SQL Server Polling Debatch Message By Grouping Filter Pipeline Component

BizTalk Pipeline Components Extensions Utility Pack: SQL Server Polling Debatch Message By Grouping Filter Pipeline Component

The BizTalk Pipeline Components Extensions Utility Pack community project for BizTalk Server 2016, once again, got a new update! It now has a new component that you can use in your custom BizTalk Server pipelines: SQL Server Polling Debatch Message By Grouping Filter Pipeline Component.

Debatching messages, which are received from WCF-SQL Adapter, is quite a simple task to do. You need to:

  • Specify that the message has an envelope by setting the Envelope property to Yes

  • Specify the Body XPath property

  • And finally, set the Max Occurs property to 1

Next, the default XMLReceive pipeline will do the rest. However, what happens if you want to extend and customize this behavior? In my case, let’s imagine I have the following row samples:

SeqId

Name

MsgIdentifier

Role

Company

1

Sandro Pereira

0000001

Team Lead

DevScope

2

Pedro Almeida

0000001

Senior Developer

DevScope

3

Rui Romano

0000002

Team Lead

DevScope

4

João Sousa

0000003

Team Lead

DevScope

5

Joana Barbosa

0000002

BI Architect

DevScope

We will poll all the data from the SQL Database (not all, but let’s say the first 20 lines), and we want to split or debatch the incoming message based on the value of the MsgIdentifier property. So, after passing the receive pipeline, we will get, based on the previous example, 3 different messages:

table

The main question is: How do we handle scenarios where debatching is done based on business logic?

The answer is that we don’t have anything out-of-the-box that allows us to do these kinds of tasks. Most of you will think about debatching these kinds of messages inside an orchestration.

However, the best way for us to achieve this goal is to develop a custom Disassemble pipeline component.

SQL Server Polling Debatch Message By Grouping Filter Pipeline Component

The reason for choosing the Disassemble stage is simple; it is here that normally the process of breaking up a large interchange message into smaller messages happens, by removing the Envelopes, which is often called “debatching”. So, this should indeed be the place for us to create our custom Disassemble pipeline component.

The SQL Server Polling Debatch Message By Grouping Filter pipeline component is a custom disassemble pipeline component that can be used to debatch incoming messages from the WCF-SQL adapter, by filtering from a specific element of the message. It will provide the following capabilities:

  • It allows you to define the grouping element
    • This will be an integer that will define the position of the element inside the message after the source code

Note: This custom pipeline component can be used with other LOB adapters but it was never tested before.


public void Disassemble(Microsoft.BizTalk.Component.Interop.IPipelineContext pc, Microsoft.BizTalk.Message.Interop.IBaseMessage inmsg)
{
    string originalDataString;

    try
    {
        //fetch original message
        Stream originalMessageStream = inmsg.BodyPart.GetOriginalDataStream();
        byte[] bufferOriginalMessage = new byte[originalMessageStream.Length];
        originalMessageStream.Read(bufferOriginalMessage, 0, Convert.ToInt32(originalMessageStream.Length));
        originalDataString = System.Text.ASCIIEncoding.ASCII.GetString(bufferOriginalMessage);
    }
    catch (Exception ex)
    {
        throw new ApplicationException("Error in reading original message: " + ex.Message);
    }

    XmlDocument originalMessageDoc = new XmlDocument();
    StringBuilder messageString;
    string msgBatchId = string.Empty;

    try
    {
        //load original message
        originalMessageDoc.LoadXml(originalDataString);
                
        //fetch namespace and root element
        string namespaceURI = originalMessageDoc.DocumentElement.NamespaceURI;
        string rootElement = originalMessageDoc.DocumentElement.Name;

        //start batching messages
        messageString = new StringBuilder();
        messageString.Append("<" + rootElement + " xmlns:ns0='" + namespaceURI + "'>");
        string rowId = string.Empty;

        foreach (XmlNode childNode in originalMessageDoc.DocumentElement.ChildNodes)
        {
            messageString.Append("<" + childNode.Name + ">");
            foreach (XmlNode rows in childNode.ChildNodes)
            {
                rowId = rows.ChildNodes[this.GroupingElement].InnerText;

                if (msgBatchId == string.Empty)
                    msgBatchId = rowId;

                if (msgBatchId != rowId)
                {
                    messageString.Append("</" + childNode.Name + ">");
                    messageString.Append("</" + rootElement + ">");

                    //Queue message
                    CreateOutgoingMessage(pc, messageString.ToString(), namespaceURI, rootElement);

                    msgBatchId = rowId;

                    messageString.Remove(0, messageString.Length);
                    messageString.Append("<" + rootElement + " xmlns:ns0='" + namespaceURI + "'>");
                    messageString.Append("<" + childNode.Name + ">");
                    messageString.Append(rows.OuterXml);
                }
                else
                {
                    messageString.Append(rows.OuterXml);
                }
            }
            messageString.Append("</" + childNode.Name + ">");
        }

        messageString.Append("</" + rootElement + ">");

        CreateOutgoingMessage(pc, messageString.ToString(), namespaceURI, rootElement);
    }
    catch (Exception ex)
    {
        throw new ApplicationException("Error in writing outgoing messages: " + ex.Message);
    }
    finally
    {
        messageString = null;
        originalMessageDoc = null;
    }


    // _msgs.Enqueue(inmsg);
}

The source code shown here is just a snippet. To be able to use this, you can download the latest version from the BizTalk Pipeline Components Extensions Utility Pack.

What is BizTalk Pipeline Components Extensions Utility Pack?

The BizTalk Pipeline Components Extensions Utility Pack is a set of custom pipeline components (libraries) with several custom pipeline components that can be used in receive and send pipelines, which will provide an extension of BizTalk out-of-the-box pipeline capabilities.

The project is available on the BizTalk Server Open Source Community repository on GitHub (https://github.com/BizTalkCommunity). Everybody can contribute with new pipeline components that can be used to extend or improve the existing BizTalk Server capabilities.

At the moment, it is only available for BizTalk Server 2016, but it will soon be compiled and available for previous versions of the product.

Where to download it?

You can download BizTalk Pipeline Components Extensions Utility Pack from GitHub here: BizTalk Pipeline Components Extensions Utility Pack.

 

The post BizTalk Pipeline Components Extensions Utility Pack: SQL Server Polling Debatch Message By Grouping Filter Pipeline Component appeared first on BizTalk360.

BizTalk WCF-SQL Error: Microsoft.ServiceModel.Channels.Common.ConnectionException: Login failed for user

BizTalk WCF-SQL Error: Microsoft.ServiceModel.Channels.Common.ConnectionException: Login failed for user

And yes, this is just another “Login failed for user” SQL Server WCF-Adapter related error. In the past I wrote about a similar topic BizTalk WCF-SQL Error:

This time the error message, the cause, and the solution are slightly different. This time while trying to communicate to a brand-new SQL Server server/database to insert date on a table thru BizTalk WCF-SQL adapter I got the following error:

Microsoft.ServiceModel.Channels.Common.ConnectionException: Login failed for user ‘BTSHostSrvc’. —> System.Data.SqlClient.SqlException: Login failed for user ‘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, SqlAuthenticationProviderManager sqlAuthProviderManager)

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.

BizTalk Server WCF-SQL: Login failed for user

In the event viewer the message is pretty much the same:

A message sent to adapter “WCF-Custom” on send port “STAGING_BULK_SQL_WCf_SEND” with URI “mssql://SQLSRV/ /ESBAsync” is suspended.

Error details: Microsoft.ServiceModel.Channels.Common.ConnectionException: Login failed for user ‘DOMAIN BTSHostSrvc’. —> System.Data.SqlClient.SqlException: 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, SqlAuthenticationProviderManager sqlAuthProviderManager)

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: {84B22A22-13F7-47C7-91B5-A863E64E268E}

BizTalk Server WCF-SQL: Login failed for user

Cause

Once again, sometimes is not quite true, the cause of the problem is simple to diagnose, and the error message gives a very good intel in the cause 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 SQL Server or SQL Server instance.

Just to be clear, this is not having permission to insert, read or event full permission to do operation on a specific database, that is completely different – I check all of that and the user have the correct access/permission. What I forget was to give access to connect to the SQL Server/SQL Server Instance.

Solution

To solve this issue, you must give access to the user, in my case BizTalk Host Instance Account to connect to the SQL Server and for that, you must:

  • Open SQL Server Management Studio and connect to your server.
  • In the Object Explorer, expand the “Security” folder under the server.
  • Right click on the “Logins” folder and choose “New Login…”
  • Add the username or group in the format “DomainUserNameOrGroup”

03-WCF-SQL-Receive-Location-Login-Failed-For-user-Create-SQL-Server-Login

  • Choose the “Securables” tab and make sure that you grant “Connect SQL” permission to the SQL Server/SQL Server instance

04-WCF-SQL-Receive-Location-Login-Failed-For-user-Create-SQL-Server-Login-Securables

  • Click “OK” and your user will be created and have access to connect to your SQL Server.
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. View all posts by Sandro Pereira

BizTalk WCF-SQL Error: System.Data.SqlClient.SqlException (0x80131904): Procedure or function ” expects parameter ‘@

BizTalk WCF-SQL Error: System.Data.SqlClient.SqlException (0x80131904): Procedure or function ” expects parameter ‘@

Today while trying to test a solution where it was supposed to invoke a SQL Server Stored Procedure with optional parameters thru the BizTalk Server WCF-SQL adapter I got this following error: Procedure or function expects parameter which was not supplied.

A message sent to adapter “WCF-Custom” on send port “WcfSendPort_SqlAdapterBinding_Procedures_dbo_Custom” with URI “mssql://.// AsyncTransactions?” is suspended.

Error details: System.Data.SqlClient.SqlException (0x80131904): Procedure or function ‘InsertTransaction’ expects parameter ‘@TransactionId’, which was not supplied.

Server stack trace:

at System.Runtime.AsyncResult.End[TAsyncResult](IAsyncResult result)

at System.ServiceModel.Channels.ServiceChannel.SendAsyncResult.End(SendAsyncResult result)

at System.ServiceModel.Channels.ServiceChannel.EndCall(String action, Object[] outs, IAsyncResult result)

at System.ServiceModel.Channels.ServiceChannel.EndRequest(IAsyncResult result)

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.Channels.IRequestChannel.EndRequest(IAsyncResult result)

at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.RequestCallback(IAsyncResult result)

ClientConnectionId:2731f081-b009-4ba5-a420-95efe7307e71

Error Number:201,State:4,Class:16

MessageId: {746C3B89-A7ED-4C12-8548-0FE174ACA2CB}

InstanceID: {FF06027F-BB96-498B-9515-1E2D55FEAC5A}

BizTalk Server WCF-SQL Adapter: Procedure or function expects parameter

Followed by other similar warning messages:

The adapter failed to transmit message going to send port “WcfSendPort_SqlAdapterBinding_Procedures_dbo_Custom” with URL “mssql://.// AsyncTransactions?”. It will be retransmitted after the retry interval specified for this Send Port. Details:”System.Data.SqlClient.SqlException (0x80131904): Procedure or function ‘InsertTransaction’ expects parameter ‘@TransactionId’, which was not supplied.

The adapter failed to transmit message going to send port “WcfSendPort_SqlAdapterBinding_Procedures_dbo_Custom” with URL “mssql://.// AsyncTransactions?”. It will be retransmitted after the retry interval specified for this Send Port. Details:”Microsoft.ServiceModel.Channels.Common.XmlReaderParsingException: The start element with name “Request” and namespace “http://BizTalkTesting.SQLBulkOperations” was unexpected. Please ensure that your input XML conforms to the schema for the operation.

BizTalk Server WCF-SQL Adapter: Procedure or function expects parameter - Event Viwer

Cause

The cause of this problem is, once again, quite obvious and the error message present clearly identifies the origin of the problem. The stored procedure is expecting a certain field or several fields that are not being passed maybe because they are marked on the schema as optional fields.

Solution

Of course, the simple problem is to pass all the parameter presented and required by the stored procedure. You should pass them:

  • as NULL, if they are nillable;
  • or as empty strings;

Having said that, my problem was not that quite simple because the parameter described in the error above needs to be optional. After I analyze the stored procedure contract I realized that all of them are set as mandatory fields that need to be passed:

ALTER PROCEDURE [dbo].[InsertTransaction] 
    -- Add the parameters for the stored procedure here
    @SourceSystem  VARCHAR(50),
    @DestinationSystem VARCHAR(50),
    @TransactionId uniqueidentifier,
    @MessageId uniqueidentifier,
    @DocumentType VARCHAR(50),
    @DocumentId VARCHAR(100),
    @BodyMessage ntext,
    @BodyType VARCHAR(5),
    @Priority int
AS
BEGIN
...

When I say that all of them are mandatory, of course, I can set it as NULL but in terms of BizTalk Server, I need to send all the fields in the XML message.

To make it actually optional, we need to change the contract of the schema to something like this:

ALTER PROCEDURE [dbo].[InsertTransaction] 
    -- Add the parameters for the stored procedure here
    @SourceSystem  VARCHAR(50),
    @DestinationSystem VARCHAR(50),
    @TransactionId uniqueidentifier  = null,
    @MessageId uniqueidentifier  = null,
    @DocumentType VARCHAR(50),
    @DocumentId VARCHAR(100),
    @BodyMessage ntext,
    @BodyType VARCHAR(5),
    @Priority int
AS
BEGIN BEGIN
...

Once we modify the stored procedure to have default NULL values the problem will be solved. Now we don’t need to send these fields as NULL or blank, now we actually can omit them from the message – optional fields!

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. View all posts by Sandro Pereira

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

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 ‘DOMAINBTSHostSrvc’. —> System.Data.SqlClient.SqlException: Cannot open database “AsyncTransactions” requested by the login. The login failed.

Login failed for user ‘DOMAINBTSHostSrvc’.

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}

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.

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

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.

BizTalk WCF-SQL Error: Microsoft.ServiceModel.Channels.Common.MetadataException: Object [dbo].[StoredProcedureName] of type StoredProcedure does not exist

BizTalk WCF-SQL Error: Microsoft.ServiceModel.Channels.Common.MetadataException: Object [dbo].[StoredProcedureName] of type StoredProcedure does not exist

I told you in my last blog post an error never comes alone … when an error appears, it always appears two or three. Or that, or I have a tendency to attract, or find weird errors! This time I was completely stunned: StoredProcedure does not exist, while trying to invoke a stored procedure thru BizTalk WCF-SQL adapter:

Microsoft.ServiceModel.Channels.Common.MetadataException: Object [dbo].[InsertTransaction] of type StoredProcedure does not exist

Server stack trace:

at System.Runtime.AsyncResult.End[TAsyncResult](IAsyncResult result)

at System.ServiceModel.Channels.ServiceChannel.SendAsyncResult.End(SendAsyncResult result)

at System.ServiceModel.Channels.ServiceChannel.EndCall(String action, Object[] outs, IAsyncResult result)

at System.ServiceModel.Channels.ServiceChannel.EndRequest(IAsyncResult result)

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.Channels.IRequestChannel.EndRequest(IAsyncResult result)

at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.RequestCallback(IAsyncResult result)

StoredProcedure does not exist: BizTalk WCF-SQL adapter

or in the event viewer:

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.MetadataException: Object [dbo].[InsertTransaction] of type StoredProcedure does not exist

Server stack trace:

at System.Runtime.AsyncResult.End[TAsyncResult](IAsyncResult result)

at System.ServiceModel.Channels.ServiceChannel.SendAsyncResult.End(SendAsyncResult result)

at System.ServiceModel.Channels.ServiceChannel.EndCall(String action, Object[] outs, IAsyncResult result)

at System.ServiceModel.Channels.ServiceChannel.EndRequest(IAsyncResult result)

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.Channels.IRequestChannel.EndRequest(IAsyncResult result)

at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.RequestCallback(IAsyncResult result)

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

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

StoredProcedure does not exist: BizTalk WCF-SQL adapter Event Viewer

As I normally say, doubt what you read – even if it is on my blog – because there are many conspiracy theories, and we can find so-called solutions by googling it… but a lot of them are just that: conspiracy theories.

Cause

Personally, I usually like to start with the simplest thing: If it says that doesn’t exist… let’s check, and in this case, the error raised by the adapter it’s just stupid because the stored procedure exists in that specific database as you may see in the picture below:

StoredProcedure does not exist: Stored Procedure exist

At a first glimpse, it seems that was not a security issue because I was able to connect to the database, otherwise, I would get the following error:

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

Again, some conspiracy theories may let you think that:

  • you should regenerate schemas;
  • or that may be a mismatch in the namespaces;
  • the operation is not properly set;
  • that the “?” character that you normally find in the URI is causing this problem;
  • and my favorite is that you should give “sysadmin” rights to the service account that is running the host instance.

Lucky, I remembered to double check the security permission directly on SQL Server, so I opened the SQL Server Management Console with the service account and try to execute the stored procedure and… guest what… I didn’t have permissions!

When I double check the permissions to that service account, it had: db_datareader and db_datawriter… that in some situations are enough:

StoredProcedure does not exist: Service Account Role Permissions

Of course, given “sysadmin” or “db_ower” would solve all my problems but sometimes you are not allowed to do that for security reasons and also is not a best practice, special now with GDPR.

So the problem was clear that the service account that is running the host instance bind to that send port didn’t have the right permissions to execute the stored procedure… unfortunately, the error raised by the adapter is out of context.

Solution

The best approach for you to solve this problem is to create a new server role, for that particular database, in SQL Server:

  • In Object Explorer, access to your database and expand it.
  • Expand the Security folder.
  • Right-click the “Database Roles” folder and select “New Database Role…”
  • In the “New Database Role” window
    • On the “Role name” property, on the General page, enter a name for the new database role, for example, “db_spexecution”
    • On the “Owner” property, inherit from db_datareader and db_datawriter (optional)

StoredProcedure does not exist: Select database User or Role

  • On the Securables page, under Securables, click “Search” button.
    • On “Add Objects” window, select “Specific objects…” and click “OK”

StoredProcedure does not exist: add objects

    • On “Select Objects” windows, click “Object Types…” and then select “Stored Procedures”

StoredProcedure does not exist: Select objects type Stored Procedures

    • After selecting the object type, click “Browse…” and from the “Browser for Objects” windows select the stored procedures you want to invoke. (only the one that you need)

StoredProcedure does not exist: browse objects Stored Procedures

    • Click “Ok” and again “OK” to return to the main “New Database Role” window.
  • The last step, on the Securables page, is to give Execute permissions “Grant” and “Grant with”.

StoredProcedure does not exist: new role securables tab

  • And finally give, on the General tab, add the service account that is running the host instance to the Role Members for that role.

StoredProcedure does not exist: new role General tab

  • Click “OK” to finish.

You can now try to resend the suspended messages or send new ones, and you will be able to communicate and execute the stored procedures without any problem.

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. View all posts by Sandro Pereira

BizTalk Server: Creation of Adapter WCF-SQL Configuration Store entries failed. Access denied.

BizTalk Server: Creation of Adapter WCF-SQL Configuration Store entries failed. Access denied.

This week while configuring and optimizing a brand-new BizTalk Server 2016 environment we got the following error message while trying to configure register the WCF-SQL Adapter in the BizTalk Server Administration console:

Creation of Adapter WCF-SQL Configuration Store entries failed. Access denied. See the event log (on computer ‘SQL-SERVER’) for more details.

Creation of Adapter WCF-SQL Configuration Store entries failed

(sorry the picture quality, it was taken with my cell phone)

Despite I was a member of BizTalk Administration group, I didn’t have remote access to the SQL Server machine that was managed by another team so I couldn’t go there to check it out. Nevertheless, I reach that team (SQL and sysadmins) already with a possible solution that it turned out to be correct.

Cause

Many of the times these types of issues indicate or lead us to believe that there are problems associated with MSDTC. Or is not properly configured, Windows Firewall may be blocking DTC communications or in HA environment’s SSO is not clustered and may be offline.

All these possibilities should be investigated. However, if any of the points mentioned above were, for this particular case, a probable cause for this problem, it should have already manifested itself when the team pre-installed the environment and they did install the environment without encountering any problems.

The only difference between the installation and now my configuration was that these tasks were made by different users!

It is important to mention that, the user that is trying to registering an Adapter using the BizTalk Server Administration Console, need to have permissions to the SSO Database in order to register its properties so that he can store and retrieve the properties at design time and runtime.

And that is one of the reasons for why the “BizTalk Server Administrators” group should be a member of the “SSO Administrators” group.

BizTalk administrations are responsible for configuring all the components of BizTalk and many of them need to interact with SSO Database.

The people/team that was responsible to install BizTalk Server, they were members of BizTalk Server Administration, SSO Administration and some of them System Administrations and that was the reason why they didn’t get this problem or similar problems. The reason for the problem I faced was because:

  • My user was a member of BizTalk Server Administrators and local admin only. But the BizTalk Server Administrators wasn’t member of SSO Administration group.

Solution

To solve this problem, you may have two options:

  • Add my user to the SSO Administrators group.
    • Not recommended because in my opinion is more difficult to manage user access rights if you add them to each individual group.
  • Or add the “BizTalk Server Administrators” as a member of the “SSO Administrators” group.

After my user or the “BizTalk Server Administrators” group was added as a member of the “SSO Administrators” group, I was able to register the adapter.

Note: this problem can happen with any adapter you are trying to register.

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. View all posts by Sandro Pereira

WCF-SQL Adapter: Connecting to the LOB system has failed. A network-related or instance-specific error occurred while establishing a connection to SQL Server

WCF-SQL Adapter: Connecting to the LOB system has failed. A network-related or instance-specific error occurred while establishing a connection to SQL Server

I been delivering a lot of BizTalk Server Training Courses for Developers and for Administrator in the past years that I normally like to call them “Developing BizTalk Server version Course” or “BizTalk Server Operations, Administration, and Maintenance (OAM) course” – yes I know, I am very creative in terms of names – and one of the good things about using Azure Virtual Machines for that is that we can easily create several developer machines in a short period of time and manage them for not consuming resources. And normally on of the task that is present in both courses is to configure BizTalk Server environment, because I believe that all BizTalk Developers should know the basics of BizTalk Server in terms of installation, configuration and optimizations – tasks that are normally done by BizTalk Administrations – I can go further and say that, in my personal opinion, if you don’t know these basic concepts you are not truly a BizTalk Developer because many things can be done by configurations and not code.

One of these tasks is to install and configure BizTalk Server LOB adapters, in especially SQL Server adapter, since in a Developer standalone machine we will need to have SQL Server, so it is easy to test this LOB Adapter. However, if we create the Azure BizTalk Server 2016 Developer machine, configure BizTalk Server and then install and configure LOB adapters without doing anything more and you try to create a BizTalk Server solution using Visual Studio and generate SQL Server schemas:

  • In the Solution Explorer, right-click your project, click “Add”, and then click “Add Generated Items…”
  • In the “Add Generated Items – <Project Name> dialog box, select “Consume Add Service”, and then click “Add”.
  • Select the sqlBinding and properly configuring the URI

We will get the following error message:

Connecting to the LOB system has failed.

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: TCP Provider, error: 0 – The remote computer refused the network connection.).

Connecting to the LOB system has failed: WCF-SQL Adapter

when we try to connect to the SQL Server configured.

Cause

This error may occur for several reasons like BizTalk Server cannot communicate with SQL Server machine because some firewall restrictions or SQL Server does not accept Remote Connections and so on.

However, in our case, we are talking about a standalone BizTalk machine that is trying to access a database in the local SQL Server.

Our problem was that by default what Microsoft could possibly configure wrong in terms of SQL Server protocols on the BizTalk Server developer image on Azure… is actually set up wrongly!

Connecting to the LOB system has failed: SQL Server 2016 Configuration Manager protocols

And as I described in my BizTalk Server Installation and configuration tutorial (see here), one of the important steps is to configure SQL Server Network Protocols, in special ensuring that TCP/IP is enabled and Shared Memory is disabled. You can see how to accomplish this using SQL Server Configuration Manager tool here.

The actual problem that was causing the connecting to fail while trying to connect to the LOB system, in this particular case the SQL Server is because the TCP/IP protocol is disabled.

Solution

To properly configure the protocols for SQL Server, especially the TCP/IP protocol, you should:

  • Press the “Windows key” to switch to Metro UI and type “SQL Server 20016 Configuration Manager” and click on “SQL Server 2016 Configuration Manager” option on Apps menu.
  • In SQL Server Configuration Manager windows, from the left-hand pane expand “SQL Server Network Configuration” option and then click “Protocols for MSSQLSERVER”
  • Verify that both “TCP/IP” and “Named Pipes” are enabled;
  • If not, right-click in the protocol, and then click “Enable”
  • Repeat to enable the other protocol if necessary.
  • Verify that “Shared Memory” is Disable.
  • If not, right-click Shared Memory, and then click “Disable”
  • In the left-hand pane, click “SQL Server Services”, right-click “SQL Server (MSSQLSERVER)”, and then click “Restart”.

Connecting to the LOB system has failed: SQL Server 2016 restart

  • Close SQL Server Configuration Manager.
  • Click “OK” and then “Apply”

After correctly configure the SQL Server protocols, again, especially the TCP/IP, you will be able to successfully connect and generate the SQL Server Schemas that you need for your BizTalk Server Solution.

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. View all posts by Sandro Pereira