The curious case of the missing stored procedure

We sometimes use the Biztalk WCF adapter to talk to SQL Server and for that we need there to be an endpoint stored procedure in the target database for BTS to execute. When the adapter needs to execute the procedure to get or post data to the database, it will first execute a metadata query to confirm the stored procedure's existence - this is unusual behavior and it can lead to some head scratching if the lights are not all on :-)

This is an example of the query that BTS executes:

exec sp_executesql N'SELECT sp.type AS [ObjectType], modify_date AS [LastModified]FROM sys.all_objects AS sp WHERE (sp.name=@ORIGINALOBJECTNAME and SCHEMA_NAME(sp.schema_id)=@ORIGINALSCHEMANAME);SELECT [param].name AS [ParameterName], usrt.name AS [DataType], SCHEMA_NAME(usrt.schema_id) AS DataTypeSchemaName, baset.name AS [SystemType], usrt.is_table_type as IsTableType, usrt.is_assembly_type as IsAssemblyType, CAST(CASE WHEN baset.name IN (N''nchar'', N''nvarchar'') AND param.max_length <> -1 THEN param.max_length/2 ELSE param.max_length END AS int) AS [Length], CAST(param.precision AS int) AS [NumericPrecision], CAST(param.scale AS int) AS [NumericScale], param.is_output AS [IsOutputParameter], AT.assembly_qualified_name AS AssemblyQualifiedName FROM sys.all_objects AS sp INNER JOIN sys.all_parameters AS param ON param.object_id=sp.object_id LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = param.user_type_id LEFT OUTER JOIN sys.types AS baset ON (baset.user_type_id = param.system_type_id and baset.user_type_id = baset.system_type_id) or ((baset.system_type_id = param.system_type_id) and (baset.user_type_id = param.user_type_id) and (baset.is_user_defined = 0) and (baset.is_assembly_type = 1))  LEFT JOIN sys.assembly_types AT ON AT.[name] = usrt.name AND AT.schema_id = usrt.schema_id WHERE (sp.type = ''P'' OR sp.type = ''RF'' OR sp.type=''PC'') AND (sp.name=@ORIGINALOBJECTNAME and SCHEMA_NAME(sp.schema_id)=@ORIGINALSCHEMANAME) ORDER BY param.parameter_id ASC; ',N'@ORIGINALOBJECTNAME nvarchar(12),@ORIGINALSCHEMANAME nvarchar(3)',@ORIGINALOBJECTNAME=N'MyEndPointProc',@ORIGINALSCHEMANAME=N'dbo'

Now, should it transpire that the adapter is unable to execute the endpoint stored procedure and an error is logged in Windows on the BTS server, you may want to confirm the following:

1. Is Biztalk trying to connect to the right SQL Server? A SQL Profiler trace should show the above metadata query if it is
2. Does the Biztalk service account have execute permission on the stored procedure?

You may well see the metadata query in a trace output and assume all is well but still end up with the following exception being raised on the Biztalk server which says the proc doesn't exist:

A message sent to adapter "WCF-Custom" on send port "Send Message to MyDb" with URI "mssql://MyServer//MyDb?MyEndPointProc" is suspended. 
 Error details: Microsoft.ServiceModel.Channels.Common.MetadataException: Object [dbo].[MyEndPointProc] 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:  {69B1CA00-8526-4D70-A6D3-C82093BEC0A1}
 InstanceID: {1F3766A8-8AA9-435E-BFB6-2D785C8D34FB}

"What you talk'n about Willis!? The proc is there dude!!"

This happens because the Biztalk service account has permissions to execute the metadata query but not the stored procedure, so the metadata query returns no records and Biztalk thinks it isn't there and raises it's own exception rather than what you might expect which for Biztalk to try and execute the proc and cause SQL Server to generate a security exception (which would be so obvious and easy to address).

So, there you have it.

Later dudes!