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

by Sandro Pereira | Jul 4, 2018 | BizTalk Community Blogs via Syndication

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)

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}

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:

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:

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)

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

• • On “Select Objects” windows, click “Object Types…” and then select “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)

• • 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”.

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

• 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