locked
Can I configure SSMA to use DB2 server_encrypt authentication? RRS feed

  • Question

  • I am trying to use Microsoft SQL Server Migration Assistant (SSMA) for DB2 (version 7.5.0) to migrate a DB2 11.1 database to MS SQL 2014. The DB2 connection used to work, but fails after the administrator changed the DB2 authentication method from the default of “server” to “server_encrypt” to meet an audit requirement. Now when I connect to the the DB2 server, I am getting the following error:

    Connection to DB2 failed.

    The authentication method used is not supported by the remote system. Contact your system administrator.

    Is it possible to configure SSMA to use the DB2 server_encrypt authentication method?  If so, how?

    Thank you in advance for your help.

    Tuesday, December 5, 2017 6:04 PM

Answers

  • Teige,

    I could not get the SSMA Standard mode connection dialog box to connect to my DB2 server using the server_encrypt authentication method.

    I was able to connect using the Connection String mode and the following connection string:

    Provider=DB2OLEDB;User ID=useName;Password=password;Initial Catalog=dbName;Network Transport Library=TCPIP;Host CCSID=37;PC Code Page=1252;Network Address=serverName;Network Port=portNbr;Default Schema=schemaName;Units of Work=RUW;DBMS Platform=DB2/MVS;Use Early Metadata=False;Defer Prepare=False;DateTime As Char=False;Rowset Cache Size=0;Binary CodePage=0;Datetime As Date=False;AutoCommit=True;Authentication=Server_Encrypt_UsrPwd;Decimal As Numeric=False;Allow Null Chars=False;LoadBalancing=False;Persist Security Info=False;Cache Authentication=False;Connection Pooling=False;Literal Replacement=False;

    Thank you again for your help.

    • Marked as answer by Rick Grant Monday, December 11, 2017 3:18 PM
    Monday, December 11, 2017 3:18 PM

All replies

  • Hi Rick, 

    This problem occurs when the authentication method used by client is not supported by DB2. Based on my searching, SERVER_ENCRYPT connect method can only supported on zSeries or System i database server.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    • Edited by Teige Gao Wednesday, December 6, 2017 2:51 AM
    Wednesday, December 6, 2017 2:45 AM
  • Hi Teige.

    Thanks you for your response.  Just for clarification, the DB2 server is hosted on a Linux OS. This DB2 instance supports and requires SERVER_ENCRYPT authentication.  Are you saying that the SSMA client does not support SERVER_ENCRYPT to a Linux hosted DB2 server?

    Thanks again.

    Wednesday, December 6, 2017 10:39 PM
  • Hi Rick,

    According to the document of IBM, if the client is authenticated using the method SERVER_ENCRYPT at the server, the user ID and password are encrypted when they are sent over the network from the client to the server. 

    This process requires the driver to encrypt the ID and password before the client send it to server. SSMA is a client, it requires the driver to connect to DB2, based on my searching, DB2 has some limitation on the client provided for Windows.

    I suggest you opening a case in DB2 forum and ask for which driver is supported SERVER_ENCRYPT on Windows.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Rick Grant Monday, December 11, 2017 3:18 PM
    • Unmarked as answer by Rick Grant Monday, December 11, 2017 3:18 PM
    Thursday, December 7, 2017 1:51 AM
  • Teige,

    I could not get the SSMA Standard mode connection dialog box to connect to my DB2 server using the server_encrypt authentication method.

    I was able to connect using the Connection String mode and the following connection string:

    Provider=DB2OLEDB;User ID=useName;Password=password;Initial Catalog=dbName;Network Transport Library=TCPIP;Host CCSID=37;PC Code Page=1252;Network Address=serverName;Network Port=portNbr;Default Schema=schemaName;Units of Work=RUW;DBMS Platform=DB2/MVS;Use Early Metadata=False;Defer Prepare=False;DateTime As Char=False;Rowset Cache Size=0;Binary CodePage=0;Datetime As Date=False;AutoCommit=True;Authentication=Server_Encrypt_UsrPwd;Decimal As Numeric=False;Allow Null Chars=False;LoadBalancing=False;Persist Security Info=False;Cache Authentication=False;Connection Pooling=False;Literal Replacement=False;

    Thank you again for your help.

    • Marked as answer by Rick Grant Monday, December 11, 2017 3:18 PM
    Monday, December 11, 2017 3:18 PM