none
WCF-SQL Adapter / Linked Server / iSeries DB2 database does not work RRS feed

  • Question

  • Hi

    We have a need to link a DB2 database to a SQL database for querying and aggregating data via BizTalk 2013.

    The set up is as follows...

     A SQL Stored Procedure (SQL Server 2012) that reads tables on the SQL Server and also has a Join to a table that is on an iSeries DB2 database that is a SQL Linked Server.

    The stored procedure is called via the WCF-SQL adapter in BizTalk and fails saying it cant find the table in the DB2 Database

    If I run the Stored Procedure in SQL management studio it works perfectly. I thought it might be because of MS DTC being used by the WCF Adapter but can also run the Stored Procedure with a "BEGIN DISTRIBUTED TRANSACTION" at the start of the query in Management Studio.

    The Linked server is set up using the DB2OLEDB provider that came with Host Integration Server 2010

    Any advice would be gratefully  received

    Thanks



    Thursday, July 2, 2015 8:11 PM

Answers

  • When dealing with linked servers there is a clause that controls the security context of the connection in regards to the caller. If this is set then the security as applicable from the "caller" context. So when you ran it under your context (SQL Management Studio with your credentials) the query went through  because likely your account has the select/grant permissions on the DB2 table. When you execute it under BizTalk, it will take the security context of the user running the wcf-SQL send handler (processing host). That user may not have the select/connect/query grant and thus the observation/result.

    Regards.

    • Marked as answer by Angie Xu Tuesday, July 14, 2015 5:47 AM
    Friday, July 3, 2015 8:38 AM

All replies

  • HI,

    I don't think it is MSDTC (Microsoft Distributed Transaction issues) .Any ways you can try setting Ambient transaction to false on WCF SQL binding to see the flow behaviour by eliminating DTC transaction across the servers (SQL server and BizTalk ).

    I would suggest to look into SQL log files on the SQL linked server and for more error specific information .

    This will help you to troubleshoot your current issue.

    Thanks
    Abhishek

    Friday, July 3, 2015 5:51 AM
  • When dealing with linked servers there is a clause that controls the security context of the connection in regards to the caller. If this is set then the security as applicable from the "caller" context. So when you ran it under your context (SQL Management Studio with your credentials) the query went through  because likely your account has the select/grant permissions on the DB2 table. When you execute it under BizTalk, it will take the security context of the user running the wcf-SQL send handler (processing host). That user may not have the select/connect/query grant and thus the observation/result.

    Regards.

    • Marked as answer by Angie Xu Tuesday, July 14, 2015 5:47 AM
    Friday, July 3, 2015 8:38 AM