none
Biztalk DB2 data adapter calling stored procedure user authorization error RRS feed

  • Question

  • what DB2 permissions are needed to call a procedure?  When you compile a procedure it generates a package if it is not specified and I have granted execute permissions on the package and procedure.  I can successfully call the procedure from within Toad, but when I call it through Biztalk, I get a user unauthorized error in the event viewer.
    Monday, July 16, 2012 1:18 PM

All replies

  • One thing you can try would be to run your BizTalk Service as the same account "Toad" is running as just to see if that works.  If it does, then make sure the account the BizTalk Service is running as has the same DB2 permissions as the "Toad" account.

    David Downing... If this answers your question, please Mark as the Answer. If this post is helpful, please vote as helpful.

    Monday, July 16, 2012 6:00 PM
  • Make sure you have BizTalk administrator's rights. Verify you are not part of BizTalk server operators group.

    Just try

    http://msdn.microsoft.com/en-us/library/aa562158(v=bts.10).aspx


    Thanks, Raja MCTS BizTalk Server 2010, MCC If this answers your question please mark it accordingly

    Monday, July 16, 2012 6:56 PM
  • I am using the same user id in Toad and in the DB2 adapter call.  I'm looking at this http://technet.microsoft.com/en-US/library/ee252429(v=BTS.10).aspx thinking that you have to open up the Data Access Tool within Host Integration Server to set up a DB2 OLEDB UDL. 
    Monday, July 16, 2012 7:02 PM
  • Yep, I am in the Biztalk Server Administrators group. 
    Monday, July 16, 2012 7:03 PM
  • the db user account (BIZTK1) has CREATE, BIND and GRANT privs to PUBLIC.  These have been granted to the Biztk1 and t14tops schemas and the connection string is as follows:

    User ID=BIZTK1;

    Password=***************;

    Initial Catalog=DSNT;

    Network Transport Library=TCPIP;

    Host CCSID=37;

    PC Code Page=1208;

    Network Address=10.200.7.164;

    Network Port=426;

    Package Collection=BIZTK1;

    Default Schema=T14TOPS;

    Process Binary as Character=True;

    Connect Timeout=15;

    Units of Work=RUW;

    Default Qualifier=T14TOPS;

    DBMS Platform=DB2/MVS;

    Use Early Metadata=False;

    Defer Prepare=True;

    DateTime As Char=False;

    Rowset Cache Size=0;

    Binary CodePage=0;

    Max Pool Size=100;

    Datetime As Date=False;

    AutoCommit=True;

    Database Name=DSNT;

    Authentication=Server;

    Persist Security Info=True;

    Cache Authentication=False;

    Mode=Read;

    Connection Pooling=True;

    Derive Parameters=False;

    Tuesday, July 17, 2012 1:42 PM
  • The data access tool does help with trying to connect. Could you paste the error you are getting here? I worked on a project a while back that connected to DB2. It took a while to get the configuration right in the data access tool to get it to connect successfully.

    I think I would get stuck on what package collection it was supposed to be at one time.

    Thanks,


    If this answers your question, please use the "Answer" button to say so | Ben Cline

    Tuesday, July 17, 2012 9:18 PM
    Moderator
  • I have looked at the data access tool and "Connect" and "Packages" buttons work w/o errors.  The "sample query" returns error: SQLSTATE: 42721, SQLCODE: -725, but I am calling a stored procedure so I don't think this matters.  Thanks for the suggestion. 

    Wednesday, July 18, 2012 11:56 AM
  • Event Log on BT App Svr: The adapter "DB2" raised an error message. Details "The user does not have the authority to access the host resource. Check your authentication credentials or contact your system administrator.".

     

    We elevated Biztk1’s permissions to SysCtl and reset IIS, recycled the host instance and received the same error.  Next, we gave Biztk1 sysadmin (highest level) and it still failed.

    The DB2 Log files had:

    7/13 10.30.29 DSNTMSTR DSNL030I  - DSNLTSEC.2E DDF PROCESSING FAILURE FOR    

                           LUWID=AC139529.E1F2.C9DC80B57298                      

                           AUTHID=BIZTK1, REASON=00D31050                        

    There is a tool called “Insight” for DB2 that will trace the sql commands and it is sending this command thru before the error appears.  REASON=00D31050: (http://www-01.ibm.com/support/docview.wss?uid=isg1II14496) The few things this does say to look for check out.  Both Userid/pwd are 8 chars or less.

    SELECT VARCHAR (RTRIM (DBNAME), 128) as TABLE_QUALIFIER, VARCHAR (RTRIM (CREATOR), 128) as TABLE_OWNER, VARCHAR (RTRIM (NAME), 128) as TABLE_NAME, VARCHAR (RTRIM (TYPE), 128) as TABLE_TYPE, VARCHAR (REMARKS, 51), CASE WHEN TYPE = 'A' THEN 1 WHEN TYPE = 'P' THEN 2 WHEN TYPE = 'T' THEN 2 WHEN TYPE = 'L' THEN 3 WHEN TYPE = 'V' THEN 3 ELSE 4 END AS SORTORDER, '', ''

    FROM SYSIBM.SYSTABLES

    WHERE CREATOR = 'T14TOPS'

    ORDER BY SORTORDER,TABLE_OWNER, TABLE_NAME FOR FETCH ONLY;  

    I can run this through Toad and it returns 4200 records (no errors executing it as Biztk1). 

    Wednesday, July 18, 2012 12:01 PM
  • Another tool I used I think was called DB2 Navigator but I am guessing this should be functionally equivalent to running with Toad.

    That command looks like it is trying to receive the list of tables rather than just initially connecting. In the data access tool (if I remember correctly), it returns a list of the available tables and you can choose which ones to connect to. Did you see anything in Insight that shows a previous initial connection was successful?

    This issue feels like there is something different about the way Toad is connecting vs how BizTalk is connecting. The error says it is for z/OS, is this correct for DB2/MVS? I would just check to make sure BizTalk is using the correct DB2 OS setting in the data access tool.

    Which BizTalk version are you using?

    I was using BizTalk 2010 / HIS 2010 to connect.

    Did you already confirm if the DB2 version you are connect to is supported by the BizTalk version?

    This might be worth checking because there may be a version compatibility difference.

    Thanks,


    If this answers your question, please use the "Answer" button to say so | Ben Cline


    Wednesday, July 18, 2012 4:40 PM
    Moderator
  • This does pertain to z/OS.  However, using the MVS os, I can successfully test "Connect", "Packages" and "Sample Query" in the Data Access Tool.  However, we had to grant SysAdmin to our user ID for the "Packages" and "Sample Query" to work.  The "Packages" created a pkg (plan table)  MSCS001 (futher info: http://msdn.microsoft.com/en-us/library/aa705138(v=bts.10).aspx) which is used to bind the procedure to the package on the database. 

    NOTE: when you compile procedures in DB2 and do not specify a package, DB2 will automatically create one for you. You will need to provide grants for the procedure as well as the package.

    Next, I enabled my db2 receive location to test it through Biztalk.  This failed with the same error that user is unauthorized

    Thursday, July 19, 2012 2:55 PM