I'm trying to connect to a DB2 database from a CLR method in our SQL Server database. The connection is done over an OleDbConnection. This worked before, but we're migrating to a new environment. We used to work on an SQL Server 2014 using the IBM DB2 9.7 driver. Now we're going to an SQL Server 2019 using the latest IBM DB2 11.5 driver. Development is done in .NET Framework 4.8 and uses CLR 4.0. Because of the updated environment we now sign our CLR assembly with a certificate before publishing.
The CLR code itself works and can be called, but the OleDbConnection to the DB2 database doesn't work.
When running the OleDbConnection.Open() method we get the following error:
A .NET Framework error occurred during execution of user-defined routine or aggregate "clrGetData":
System.Data.OleDb.OleDbException: Unspecified error
System.Data.OleDb.OleDbException:
at System.Data.OleDb.OleDbServicesWrapper.GetDataSource(OleDbConnectionString constr, DataSourceWrapper& datasrcWrapper)
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, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open()
Strange thing is, when I make a simple console application which tries to connect to the same database over OleDb, using the same connection string and the same code for initializing the connection and such, the connection is opened without problems and calls can be done to the DB2 database. I've tested this on both the application server (which calls the CLR method on our SQL Server database) and the database server (on which the CLR's run and make a connection to the DB2 database).
I also tried to connect to the DB2 database directly from our web application on the application server, by copying the CLR code to the web application. This also goes without any problems. However, as our current setups requires the sync between the 2 databases to be initialised from our database, it would be a lot or refactoring work to fully replace our CLR procedures.
This is an example of a CLR procedure we're testing at the moment:
[Microsoft.SqlServer.Server.SqlProcedure]
public static void clrGetData(string userId)
{
var connectionString = <Get connection string to DB2 database>;
using (var db2connection = new OleDbConnection(connectionString))
{
db2connection.Open();
if (db2connection == null) throw new ArgumentNullException("connection");
Microsoft.SqlServer.Server.SqlMetaData[] mdDos = new Microsoft.SqlServer.Server.SqlMetaData[1];
mdDos[0] = new Microsoft.SqlServer.Server.SqlMetaData("Profile", System.Data.SqlDbType.VarChar, 100);
string storedProcedure = string.Format("<Name of SP on DB2 database>");
var cmd = new DB2Command();
cmd.CommandText = storedProcedure;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Connection = db2connection;
// parameters
OleDbParameter dbParam;
dbParam = new OleDbParameter("<name>", OleDbType.Char, 10, "<srcColumn>");
dbParam.Value = userId;
dbParam.Direction = ParameterDirection.Input;
cmd.Parameters.Add(dbParam);
// Some other input parameters
// ...
dbParam = new OleDbParameter("<name>", DB2Type.Integer, 10, "<srcColumn>");
dbParam.Direction = ParameterDirection.Output;
cmd.Parameters.Add(dbParam);
// Some other output parameters
// ...
var reader = cmd.ExecuteReader();
Microsoft.SqlServer.Server.SqlPipe sqlpipe = Microsoft.SqlServer.Server.SqlContext.Pipe;
Microsoft.SqlServer.Server.SqlDataRecord record = new Microsoft.SqlServer.Server.SqlDataRecord(mdDos);
sqlpipe.SendResultsStart(record);
if (reader.Read())
{
record.SetValue(0, reader[5]);
sqlpipe.SendResultsRow(record);
}
sqlpipe.SendResultsEnd();
reader.Close();
db2connection.Close();
}
}
I've already expanded the console test application to also connect with OdbcConnection and DB2Connection (by referencing the IBM.Data.DB2.dll used in the IBM driver). I get both of them to connect as well, but once again, when using this in the CLR procedure, I get stuck.
When using Odbc, I get the following error:
SqlException (0x80131904): A .NET Framework error occurred during execution of user-defined routine or aggregate "clrGetData":
System.Data.Odbc.OdbcException: ERROR [IM003] Specified driver could not be loaded due to system error 1114: A dynamic link library (DLL) initialization routine failed. (IBM DB2 ODBC DRIVER - DB2COPY1, D:\Program Files\IBM\SQLLIB\BIN\DB2CLIO.DLL).
System.Data.Odbc.OdbcException:
at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcConnectionOpen..ctor(OdbcConnection outerConnection, OdbcConnectionString connectionOptions)
at System.Data.Odbc.OdbcConnectionFactory.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, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.Odbc.OdbcConnection.Open()
When using the IBM dll, we're having troubles because we can't add external references without adding them to SQL Server as assemblies as well. Since the IBM driver itself probably references a whole lot of other non-supported references, this would be a whole other problem to fix.
These are the connection strings we're using for each method:
// OleDb:
Provider=IBMDADB2.DB2COPY1;Database=<DB Name>;Hostname=<IP Address>;Protocol=TCPIP;Port=<Port Number>;Uid=<Username>;Pwd=<Password>;
// Odbc:
Driver={IBM DB2 ODBC DRIVER - DB2COPY1};Database=<DB Name>;Hostname=<IP Address>;Port=<Port Number>;Protocol=TCPIP;UID=<Username>;PWD=<Password>;
// IBM DB2:
Server=<IP Address>:<Port Number>;Database=<DB Name>;UID=<Username>;PWD=<Password>;
I've tried googling a lot of those problems, but Google is not my friend in this, and no solution seems to work yet.
What method would be best to connect to a DB2 database inside a CLR procedure in SQL Server? And what could be wrong with our setup/code?
Edit:
As asked by commenter below, the output of the db2level command:
D:\Program Files\IBM\SQLLIB\BIN>db2level
DB21085I This instance or install (instance name, where applicable: "DB2")
uses "64" bits and DB2 code release "SQL11050" with level identifier
"0601010F".
Informational tokens are "DB2 v11.5.0.1077", "s1906101300",
"DYN1906101300WIN64", and Fix Pack "0".
Product is installed at "D:\Program Files\IBM\SQLLIB" with DB2 Copy Name
"DB2COPY1".
Solution:
The solution was in two parts:
Resolved by:
ensuring that the latest fixpack of the Db2 fat client was installed (previously a fixpack 0 was installed, which is usually unsuitable for production).
ensuring that the userid being used to connect to Db2 (from MS-SQLServer) was a member of the correct loca/ldap/ActiveDirectory group that grants the required access to the Db2 database and its objects.
Always check with the latest fixpacks of IBM database products and drivers, these are downloadable from fixpack central website.