BizTalk DB2 adapter connection error

Question

My colleagues are attempting to connect BizTalk 2006 R2 via DB2/MVS adapter to a database hosted on z/OS mainframe. When testing the connecting settings, they are getting the following error

Could not connect to data source 'New Data Source':
The network connection was terminated because the host failed to send any data.
SQLSTATE: 08S01, SQLCODE: -605

When putting the settings in a regular connection string and opening with .NET code, that is fine. I am new to BizTalk and DB2. Can anybody suggest what to look out for when this error surfaces?

24 Aug 08:

Well, if normal .NET code with a regular DB2 connection string is used, the connection can be made and queries submitted. What this DB2 adapter is reporting is it cannot even make a proper connection handshake, let alone submitting queries. I am unsure of what are the actual mechanisms involved to make a DB2 connection happen.

25 Aug 08:

According to this MSDN forums posting, it seems to be a login issue.

I have seen that and that is not the case here. If we put the user name as the Package Collection it still hits the same problem.

26 Aug 08:

Because of the scarcity of information regarding connecting to mainframe DB2 databases from Microsoft products, I undertook the task of inspecting raw network packets to get a clue what is going on between the .NET DB2 provider's connection (which works) and the BizTalk 2006 DB2 adapter (which bombs). I observed DB2 traffic is done using the DRDA protocol. And ultimately concluded the BizTalk adapter method fails because of what's recorded in the server's reply SECCHKRM packet

DRDA (Security Check)
    DDM (SECCHKRM)
        Length: 55
        Magic: 0xd0
        Format: 0x02
            0... = Reserved: Not set
            .0.. = Chained: Not set
            ..0. = Continue: Not set
            ...0 = Same correlation: Not set
            DSS type: RPYDSS (2)
        CorrelId: 0
        Length2: 49
        Code point: SECCHKRM (0x1219)
    Parameter (Severity Code)
        Length: 6
        Code point: SVRCOD (0x1149)
        Data (ASCII): 
        Data (EBCDIC): 
    Parameter (Security Check Code)
        Length: 5
        Code point: SECCHKCD (0x11a4)
        Data (ASCII): 
        Data (EBCDIC): 
    Parameter (Server Diagnostic Information)
        Length: 34
        Code point: SRVDGN (0x1153)
        Data (ASCII): \304\331\304\301@\301\331z@\301\344\343\310\305\325\343\311\303\301\343\311\326\325@\206\201\211\223\205\204
        Data (EBCDIC): DRDA AR: AUTHENTICATION failed

Why the same credentials fails here while succeeding in the .NET provider is beyond me. Right now, what I can observe is a marked difference between each method when it comes to the sequence of packets transferred.

.NET DB2 provider

No.     Time        Source                Destination           Protocol Info
      1 0.000000    [client IP]         [DB2 server IP]          TCP      kpop > 50000 [SYN] Seq=0 Win=65535 Len=0 MSS=1460 WS=1
      2 0.000399    [DB2 server IP]          [client IP]         TCP      50000 > kpop [SYN, ACK] Seq=0 Ack=1 Win=16384 Len=0 MSS=1460 WS=0
      3 0.000414    [client IP]         [DB2 server IP]          TCP      kpop > 50000 [ACK] Seq=1 Ack=1 Win=65536 [TCP CHECKSUM INCORRECT] Len=0
      4 0.000532    [client IP]         [DB2 server IP]          DRDA     EXCSAT | ACCSEC
      5 0.038162    [DB2 server IP]          [client IP]         DRDA     EXCSATRD | ACCSECRD
      6 0.041829    [client IP]         [DB2 server IP]          DRDA     ACCSEC | SECCHK | ACCRDB
      7 0.083626    [DB2 server IP]          [client IP]         TCP      50000 > kpop [ACK] Seq=108 Ack=542 Win=65535 Len=0
      8 0.190534    [DB2 server IP]          [client IP]         DRDA     ACCSECRD | SECCHKRM | ACCRDBRM | SQLCARD
      9 0.199776    [client IP]         [DB2 server IP]          DRDA     PRPSQLSTT | SQLATTR | SQLSTT | OPNQRY
     10 0.293307    [DB2 server IP]          [client IP]         TCP      [TCP segment of a reassembled PDU]
     11 0.293359    [DB2 server IP]          [client IP]         TCP      [TCP segment of a reassembled PDU]
     12 0.293377    [client IP]         [DB2 server IP]          TCP      kpop > 50000 [ACK] Seq=870 Ack=1444 Win=64092 [TCP CHECKSUM INCORRECT] Len=0
     13 0.293404    [DB2 server IP]          [client IP]         TCP      [TCP segment of a reassembled PDU]
     14 0.293452    [DB2 server IP]          [client IP]         TCP      [TCP segment of a reassembled PDU]
     15 0.293461    [client IP]         [DB2 server IP]          TCP      kpop > 50000 [ACK] Seq=870 Ack=2516 Win=65536 [TCP CHECKSUM INCORRECT] Len=0
     16 0.293855    [DB2 server IP]          [client IP]         TCP      [TCP segment of a reassembled PDU]
     17 0.293908    [DB2 server IP]          [client IP]         DRDA     SQLDARD
     18 0.293918    [client IP]         [DB2 server IP]          TCP      kpop > 50000 [ACK] Seq=870 Ack=3588 Win=64464 [TCP CHECKSUM INCORRECT] Len=0
     19 0.293957    [DB2 server IP]          [client IP]         DRDA     QRYDSC
     20 0.294008    [DB2 server IP]          [client IP]         DRDA     QRYDTA
     21 0.294017    [client IP]         [DB2 server IP]          TCP      kpop > 50000 [ACK] Seq=870 Ack=4660 Win=65536 [TCP CHECKSUM INCORRECT] Len=0
     22 0.294023    [DB2 server IP]          [client IP]         DRDA     SQLCARD
     23 0.295346    [client IP]         [DB2 server IP]          DRDA     RDBCMM
     24 0.297868    [DB2 server IP]          [client IP]         DRDA     ENDUOWRM | SQLCARD
     25 0.421392    [client IP]         [DB2 server IP]          DRDA     PRPSQLSTT | SQLATTR | SQLSTT | OPNQRY
     26 0.456504    [DB2 server IP]          [client IP]         DRDA     SQLDARD | OPNQRYRM | TYPDEFNAM | QRYDSC | QRYDTA | ENDQRYRM | TYPDEFNAM | SQLCARD
     27 0.456756    [client IP]         [DB2 server IP]          DRDA     RDBCMM
     28 0.488311    [DB2 server IP]          [client IP]         DRDA     ENDUOWRM | SQLCARD
     29 0.498806    [client IP]         [DB2 server IP]          DRDA     PRPSQLSTT | SQLATTR | SQLSTT | OPNQRY
     30 0.630477    [DB2 server IP]          [client IP]         TCP      50000 > kpop [ACK] Seq=5157 Ack=1579 Win=65171 Len=0
     31 0.788165    [DB2 server IP]          [client IP]         DRDA     SQLDARD | OPNQRYRM | TYPDEFNAM | QRYDSC | QRYDTA
     32 0.788203    [DB2 server IP]          [client IP]         DRDA     ENDQRYRM
     33 0.788225    [client IP]         [DB2 server IP]          TCP      kpop > 50000 [ACK] Seq=1579 Ack=5815 Win=64380 [TCP CHECKSUM INCORRECT] Len=0
     34 0.788648    [client IP]         [DB2 server IP]          DRDA     RDBCMM
     35 0.795951    [DB2 server IP]          [client IP]         DRDA     ENDUOWRM | SQLCARD
     36 0.807365    [client IP]         [DB2 server IP]          DRDA     PRPSQLSTT | SQLATTR | SQLSTT | OPNQRY
     37 0.838046    [DB2 server IP]          [client IP]         DRDA     SQLDARD | OPNQRYRM | TYPDEFNAM | QRYDSC | QRYDTA | ENDQRYRM | TYPDEFNAM | SQLCARD
     38 0.838328    [client IP]         [DB2 server IP]          DRDA     RDBCMM
     39 0.841866    [DB2 server IP]          [client IP]         DRDA     ENDUOWRM | SQLCARD
     40 0.973506    [client IP]         [DB2 server IP]          TCP      kpop > 50000 [ACK] Seq=1906 Ack=6304 Win=65482 [TCP CHECKSUM INCORRECT] Len=0

BizTalk DB2 adapter

No.     Time        Source                Destination           Protocol Info
      1 0.000000    [client IP]          [DB2 server IP]          TCP      28165 > 50000 [SYN] Seq=0 Win=8192 Len=0 MSS=1460 WS=8
      2 0.002587    [DB2 server IP]          [client IP]          TCP      50000 > 28165 [SYN, ACK] Seq=0 Ack=1 Win=16384 Len=0 MSS=1460 WS=0
      3 0.010146    [client IP]          [DB2 server IP]          TCP      28165 > 50000 [ACK] Seq=1 Ack=1 Win=65536 Len=0
      4 0.019698    [client IP]          [DB2 server IP]          DRDA     EXCSAT
      5 0.020849    [DB2 server IP]          [client IP]          DRDA     EXCSATRD
      6 0.034699    [client IP]          [DB2 server IP]          DRDA     ACCSEC
      7 0.036584    [DB2 server IP]          [client IP]          DRDA     ACCSECRD
      8 0.042031    [client IP]          [DB2 server IP]          DRDA     SECCHK
      9 0.046350    [DB2 server IP]          [client IP]          DRDA     SECCHKRM
     10 0.046642    [DB2 server IP]          [client IP]          TCP      50000 > 28165 [FIN, ACK] Seq=160 Ack=200 Win=65336 Len=0
     11 0.053787    [client IP]          [DB2 server IP]          TCP      28165 > 50000 [ACK] Seq=200 Ack=161 Win=65536 Len=0
     12 0.056891    [client IP]          [DB2 server IP]          DRDA     ACCRDB
     13 0.058084    [DB2 server IP]          [client IP]          TCP      50000 > 28165 [RST, ACK] Seq=161 Ack=295 Win=0 Len=0

It is interesting to witness the .NET provider issue out various DRDA protocol packets within in a single TCP segment. The BizTalk adapter on the other hand, places only one protocol packet per TCP segment. I do not know why this is so. However, I at the moment think that is a red herring and the true difference causing the failure in authentication is in the DRDA data exchange. I do not know the DRDA protocol so will have to study it before I can make more sense of it.

18 Sep 08:

At this stage the problem is still not solved, as getting cooperation from the DB2 DBA team and help from Microsoft have been met with many obstacles.

What I do want to report is, I have observed perhaps one crucial difference between all the cases of successful connection versus the failed attempt:

The BizTalk DB2 adapter is underlyingly using Microsoft ODBC Driver for DB2. The other software tests that succeed make use of IBM DB2 ODBC DRIVER or IBM DB2 ODBC DRIVER – IBMCL1. The IBM driver's parameter configuration is different from Microsoft's driver. But we do not see any obviously critical difference that may lead to a failed authentication for the Microsoft driver.

Answer 1

I've never used this adapter but myself, so I'm guessing, but maybe it's to do with the account that BizTalk is using to connect or your ports are not configured correctly.

Answer 2

According to this MSDN forums posting, it seems to be a login issue.

Answer 3

Why, it certainly took Microsoft long enough to explicitly confirm this:

proxy connections via DB2Connect is not supported by BizTalk DB2 Adapter

Since our customer's policy is to only access DB2 databases via DB2Connect, the adapter is out of the question.

MORE BACKGROUND INFO

The reason why the DB2 Adapter only works for a direct connection to a z/OS mainframe host, is due to legal restrictions. Technically it is possible to work a connection with DB2Connect, but IBM has made it a priorietary node and prevented other parties from legally establishing the correct DRDA sequence to connect to it.

Answer 4

Hi All;

I found this article very interesting as I am involved in a project which requires connectivity to DB2 on z/OS from BizTalk 2009. BizTalk DB2 adapter was requested from the client. In my situation, using the Data Access Tool which ships with the adapter, I was able to establish connection however, the database user does not have the privs to create packages nor does the client wish to grant such access. My question is: is there any way to prevent these DB2 packages from being created? Client tells me, for .NET based applications, connecting/executing a stored proc is trivial.

Any help is greatly appreciated!