WCF-SQL Adapter and permissions
Posted by DipeshA on April 16, 2012
If you are using a WCF SQL adapter in your solution, it is imperitive to set permissions on the stored procedures you call from within BizTalk. However, if you havent set permissions, the error message can be a little misleading.
The adapter failed to transmit message going to send port “WcfSendPort_SqlAdapterBinding_TypedProcedures_MyDB” with URL “mssql://MYSRVSQ01//MyDB?”. It will be retransmitted after the retry interval specified for this Send Port. Details:”Microsoft.ServiceModel.Channels.Common.MetadataException: Object [dbo].[uspInsertRecords] of type StoredProcedure does not exist
The above message can be quite confusing especially when the stored procedure does exist in your database. The above basically means the BT host account does not have execute permissions on the stored procedure being called. You need to restart the BT host instance after setting permissions.
Now, lets see what happens when I have a working solution (my permissions are set) and I revoke the execute permission on the same stored proc (remember, I havent restarted the BT host after revoking the permission). I now see this error message:
The adapter failed to transmit message going to send port “WcfSendPort_SqlAdapterBinding_TypedProcedures_MyDB” with URL “mssql://MYSRVSQ01//MyDB?”. It will be retransmitted after the retry interval specified for this Send Port. Details:”System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object ‘uspInsertRecords’, database ‘MyDB’, schema ‘dbo’.
This seems to be a much more clear message, isint it? However, if you restart the BT host and try processing your file again, you will see the first error.
This entry was posted on April 16, 2012 at 2:59 pm and is filed under BizTalk 2006/2009, BizTalk 2010. Tagged: biztalk, permissions, WCF-SQL adapter. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
Reading Notes 2012-04-23 | Matricis said
[...] WCF-SQL Adapter and permissions - Well explained. I got this once, and I remember spending few minutes trying to understand this error message… It's a shame that it's not the good one that is display first… [...]