SSL enabled DB2 database using ssma tool is failing

Question

SSL enabled DB2 database using ssma tool is failing

Anonymous

Hi Team,
as part of migrating my DB2 database to Azure SQL server I am using SSMA for DB2 v9.0,
It is connecting to the database without SSL enabled connections but when I am trying to connect to SSL enabled database I am getting very generic error as below:

"An internal network library error has occurred. A network level syntax error has occurred."

the SSMA log shows as below:

[Error: Generic] [16876/9] [2022-06-07 19:28:55]: Exception: Connection to DB2 failed.
An internal network library error has occurred. A network level syntax error has occurred.
site: Microsoft.SSMA.Framework.Generic.DbUtilities.SsmaDbConnection Connect(Microsoft.SSMA.Framework.Db2Mf.Base.Db2MfConnection.Db2MfConnectionParameters, System.Security.SecureString)
source: Microsoft.SSMA.Framework.Db2Mf.Base
at Microsoft.SSMA.Framework.Db2Mf.Base.Db2MfConnection.Db2MfConnectionFactory.Connect(Db2MfConnectionParameters connectionParameters, SecureString password)
at Microsoft.SSMA.Framework.Db2Mf.Generic.Db2MfObjectCollector.Db2MfObjectCollector.ConnectToDb2Mf(IProjectPreferencesProvider projectPreferences, String xmlConnectionParameters, SecureString password, IDbConnection implicitConnection, IUIProgressBarProvider progressBarProvider)
at Microsoft.SSMA.Framework.Db2Mf.Generic.Db2MfObjectCollector.Db2MfObjectCollector.CollectData(String connectionParameters, SecureString password, XMetabase existingMetabase, ICollectionContext context, IDbConnection implicitConnection, XNode filter)
at Microsoft.SSMA.Framework.Generic.Collectors.Default.DefaultObjectCollector.CollectData(String connectionParameters, SecureString password, ICollectionContext context)
at Microsoft.SSMA.Framework.Generic.SSMAWorkspace.MetabaseSsmaWorkspaceItem.Connect(String connectionParameters, SecureString password)
[Error: Generic] [16876/9] [2022-06-07 19:28:55]: Inner Exception #1: An internal network library error has occurred. A network level syntax error has occurred.
site: Void .ctor(System.Data.OleDb.OleDbConnectionString, System.Data.OleDb.OleDbConnection)
source: Microsoft DB2 OLE DB Provider
error code: -2147217900
at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open()
at Microsoft.SSMA.Framework.Generic.DbUtilities.SsmaDbConnection.Open()
at Microsoft.SSMA.Framework.Db2Mf.Base.Db2MfConnection.Db2MfConnectionFactory.Connect(Db2MfConnectionParameters connectionParameters, SecureString password)
[Error: Gui] [16876/9] [2022-06-07 19:28:59]: LowLevelAdapter.ReadSource : Read Source error: Microsoft.SSMA.Framework.Db2Mf.Base.Db2MfConnection.EstablishingDb2MfConnectionException: Connection to DB2 failed.
An internal network library error has occurred. A network level syntax error has occurred. ---> System.Data.OleDb.OleDbException: An internal network library error has occurred. A network level syntax error has occurred.
at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open()
at Microsoft.SSMA.Framework.Generic.DbUtilities.SsmaDbConnection.Open()
at Microsoft.SSMA.Framework.Db2Mf.Base.Db2MfConnection.Db2MfConnectionFactory.Connect(Db2MfConnectionParameters connectionParameters, SecureString password)
--- End of inner exception stack trace ---
at Microsoft.SSMA.Framework.Db2Mf.Base.Db2MfConnection.Db2MfConnectionFactory.Connect(Db2MfConnectionParameters connectionParameters, SecureString password)
at Microsoft.SSMA.Framework.Db2Mf.Generic.Db2MfObjectCollector.Db2MfObjectCollector.ConnectToDb2Mf(IProjectPreferencesProvider projectPreferences, String xmlConnectionParameters, SecureString password, IDbConnection implicitConnection, IUIProgressBarProvider progressBarProvider)
at Microsoft.SSMA.Framework.Db2Mf.Generic.Db2MfObjectCollector.Db2MfObjectCollector.CollectData(String connectionParameters, SecureString password, XMetabase existingMetabase, ICollectionContext context, IDbConnection implicitConnection, XNode filter)
at Microsoft.SSMA.Framework.Generic.Collectors.Default.DefaultObjectCollector.CollectData(String connectionParameters, SecureString password, ICollectionContext context)
at Microsoft.SSMA.Framework.Generic.SSMAWorkspace.MetabaseSsmaWorkspaceItem.Connect(String connectionParameters, SecureString password)
at Microsoft.SSMA.GUI.Product.Db2MfToMsSql.LowLevel.AppConnectToSourceDb2Mf.ConnectToSourceStart(Object guiContext)
[Error: Gui] [16876/9] [2022-06-07 19:28:59]: Exception: Connection to DB2 failed.
An internal network library error has occurred. A network level syntax error has occurred.
site: Microsoft.SSMA.Framework.Generic.DbUtilities.SsmaDbConnection Connect(Microsoft.SSMA.Framework.Db2Mf.Base.Db2MfConnection.Db2MfConnectionParameters, System.Security.SecureString)
source: Microsoft.SSMA.Framework.Db2Mf.Base
at Microsoft.SSMA.Framework.Db2Mf.Base.Db2MfConnection.Db2MfConnectionFactory.Connect(Db2MfConnectionParameters connectionParameters, SecureString password)
at Microsoft.SSMA.Framework.Db2Mf.Generic.Db2MfObjectCollector.Db2MfObjectCollector.ConnectToDb2Mf(IProjectPreferencesProvider projectPreferences, String xmlConnectionParameters, SecureString password, IDbConnection implicitConnection, IUIProgressBarProvider progressBarProvider)
at Microsoft.SSMA.Framework.Db2Mf.Generic.Db2MfObjectCollector.Db2MfObjectCollector.CollectData(String connectionParameters, SecureString password, XMetabase existingMetabase, ICollectionContext context, IDbConnection implicitConnection, XNode filter)
at Microsoft.SSMA.Framework.Generic.Collectors.Default.DefaultObjectCollector.CollectData(String connectionParameters, SecureString password, ICollectionContext context)
at Microsoft.SSMA.Framework.Generic.SSMAWorkspace.MetabaseSsmaWorkspaceItem.Connect(String connectionParameters, SecureString password)
at Microsoft.SSMA.GUI.Product.Db2MfToMsSql.LowLevel.AppConnectToSourceDb2Mf.ConnectToSourceStart(Object guiContext)
[Error: Gui] [16876/9] [2022-06-07 19:28:59]: Inner Exception #1: An internal network library error has occurred. A network level syntax error has occurred.
site: Void .ctor(System.Data.OleDb.OleDbConnectionString, System.Data.OleDb.OleDbConnection)
source: Microsoft DB2 OLE DB Provider
error code: -2147217900
at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open()
at Microsoft.SSMA.Framework.Generic.DbUtilities.SsmaDbConnection.Open()
at Microsoft.SSMA.Framework.Db2Mf.Base.Db2MfConnection.Db2MfConnectionFactory.Connect(Db2MfConnectionParameters connectionParameters, SecureString password)

Thanks in advance
Kind Regards
Alok

GeethaThatipatri-MSFT 29,542 Reputation points Microsoft Employee Moderator

2022-06-07T19:01:17.63+00:00

Hi, @Anonymous Welcome to Microsoft Q&A Thanks for reaching out.
"Sorry for the inconvenience caused, this seems more like a generic connection issue for DB2 rather than related to the tools. Basically, for DB2 SSL-based auth, you might have to use a connection string with additional parameters: SSLClientKeystoredb and SSLClientKeystash.however, I will work with the internal product team to provide you with more details.

Regards
Geetha

Accepted answer

1 additional answer

Your answer

GeethaThatipatri-MSFT 29,542 Reputation points Microsoft Employee Moderator

2022-06-07T19:01:17.63+00:00

Hi, @Anonymous Welcome to Microsoft Q&A Thanks for reaching out.
"Sorry for the inconvenience caused, this seems more like a generic connection issue for DB2 rather than related to the tools. Basically, for DB2 SSL-based auth, you might have to use a connection string with additional parameters: SSLClientKeystoredb and SSLClientKeystash.however, I will work with the internal product team to provide you with more details.

Regards
Geetha

Answer 1

Alberto Morillo 34,671 MVP Volunteer Moderator

Please make use of the connection string mode. You can use Data Access Tool that ships with the Microsoft OLE DB Provider for DB2 to generate the connection string. SSMA can connect to a DB2 server using SSL but it depends of the configuration of the OLEDB provider that it uses for the connection. You may want to provide "Certificate Common Name" to enable SSL/TLS in the provider.

Anonymous

2022-06-07T14:31:14.24+00:00

Hi Alberto thanks for the update,
I tried that Data Access Tool also and there also same error is coming.
the connection string used is as below:
Provider=DB2OLEDB;User ID=<user id>;Password=<password>;Initial Catalog=<db name>;Network Transport Library=TCPIP;Host CCSID=1208;PC Code Page=1208;Network Address=<host name> Port=<Port number>;Units of Work=RUW;DBMS Platform=DB2/6000;Use Early Metadata=False;Defer Prepare=False;DateTime As Char=False;Rowset Cache Size=0;Binary CodePage=0;Datetime As Date=False;AutoCommit=True;Database Name=BLUDB;Authentication=Server;Decimal As Numeric=False;Allow Null Chars=False;LoadBalancing=False;Persist Security Info=True;Cache Authentication=False;Connection Pooling=False;Literal Replacement=False;sslConnection=true;

Kind Regards
Alok
Anonymous

2022-06-07T14:36:19.523+00:00

and when I tried with Common certificate name it gives error as below:
The authentication method used is not supported by the remote system. Contact your system administrator.

the connection string extended with
Certificate Common Name=DigiCert Global Root CA;
Alberto Morillo 34,671 Reputation points MVP Volunteer Moderator

2022-06-07T15:08:47.61+00:00

Please compare your 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;
Anonymous

2022-06-08T05:52:16.39+00:00

Hi Albertomorillo,
I tried the connection string you provided but still no luck.

The connection string i used is as below:
Provider=DB2OLEDB;User ID=useName;Password=password;Initial Catalog=DB_NAME;Network Transport Library=TCPIP;Host CCSID=37;PC Code Page=1252;Network Address=server_NAME;Network Port=Port_nmbr;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;

BUT
When I added Certificate Common Name clause in the above string it worked fine.

SO the working connection string is
Provider=DB2OLEDB;User ID=useName;Password=password;Initial Catalog=DB_NAME;Network Transport Library=TCPIP;Host CCSID=37;PC Code Page=1252;Network Address=server_NAME;Network Port=Port_nmbr;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;Certificate Common Name=DigiCert Global Root CA;Decimal As Numeric=False;Allow Null Chars=False;LoadBalancing=False;Persist Security Info=False;Cache Authentication=False;Connection Pooling=False;Literal Replacement=False;

Thanks a lot for all your support.

Kind Regards
Alok Srivastava

Answer 2

Anonymous

Hi Albertomorillo,
Thanks for your assistance I made the working connection string by adding

Certificate Common Name

clause and the final connection string is as below:

Provider=DB2OLEDB;User ID=useName;Password=password;Initial Catalog=DB_Name;Network Transport Library=TCPIP;Host CCSID=37;PC Code Page=1252;Network Address=server_NAME;Network Port=Port_Nmmbr;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;Certificate Common Name=DigiCert Global Root CA;Decimal As Numeric=False;Allow Null Chars=False;LoadBalancing=False;Persist Security Info=False;Cache Authentication=False;Connection Pooling=False;Literal Replacement=False;

Kind Regards
Alok Srivastava

GeethaThatipatri-MSFT 29,542 Reputation points Microsoft Employee Moderator

2022-06-08T08:59:57.78+00:00

Hi, @Anonymous Happy that your issue is now resolved. and thanks for providing the feedback. could you also please accept the answer.

Regards
Geetha
GeethaThatipatri-MSFT 29,542 Reputation points Microsoft Employee Moderator

2022-06-08T09:44:59.637+00:00

Thank you for visiting the Microsoft QA forums! Have a great day!

Regards
Geetha

Share via

SSL enabled DB2 database using ssma tool is failing

1 additional answer

Your answer