Linked Server from SQL Server 2012 to AIX DB2 via SSL

Question

I need to create a linked server from SQL Server 2012 to a IBM DB2 (9.x) on AIX. It is a mandatory requirement to create the connection with SSL.

I was trying to use "Microsoft® OLEDB Provider for DB2 v4.0 for Microsoft® SQL Server® 2012" but I was not able to connect to DB2 by the Data Access Tool from MS OLEDB.

Later I installed the IBM DB2 client tools and GSKIT and with this I can connect to DB2 via SSL. With the installation also came the connection provider "IBM OLE DB Provider for DB2". The problem is that I can't create the linked server.

I always get the error message

Cannot create an instance of OLEDB Provider "IBMDADB2.DB2COPY1" for linked Sever "mydb2" (Microsoft SQL Server , Error 7302)

In some discussions was mentioned to use a specific provider string, but in this case the field of the provider string is greyed out.

I just have the options for Product name and Data Source. Is the Data Source == the DB Name of the Connection form the IBM BD2 Client tools? What should I use as Data Source?

Does your DB2 data source show up in the Windows ODBC Data Source Administrator with the name "damndb2"? — mustaccio
i could connect the damndb2 with odbc (at least in the CLI/ODBC Settings i have a button "Connect" which returns "Conncetion testet successfully". in the bind CLI/ODBC support utilities shows a log which say SQL0551N authotization or privelege on Execute is not given. — Joe Platano
I'm asking about the Windows ODBC Data Source Administrator — mustaccio
yes i was using the Windows ODBC Data Source Administrator. Creating a new SYSTEM DSN. There i can choose the driver comes with DB2 client tools. i can give the data source name and the DB alias, which is already preselected.... — Joe Platano

Joe Platano Joe Platano · Accepted Answer · 2014-11-04T09:51:10

Okay i could solve the problem and want to share the solution.

The mentioned provider IBMDADB2.DB2COPY1 comes from the installation of the IBM DB2 Client tools.

In Server Objects --> Linked Servers --> right click new server you can choose the provider. Using the "IBM OLE DB Provider for DB2 - DB2COPY1" which has access to the configured connection including the SSL Keys, the field Data Source is greyed out. Linked Server - create new object

The key to successfully create a linked server to a DB2 is not the guy but the script. There you can use also provider string information even it is greyed out in the GUI.

EXEC master.dbo.sp_addlinkedserver @server = N'MyLinkedServer', @srvproduct=N'IBMDADB2',
   @provider=N'IBMDADB2', @provstr= N'HOSTNAME=hosturl;Port=12345;
      DATABASE=DBinDB2Client;UID=ItsMe;Pwd=secret;Security=SSL;', @datasrc=N'DBinDB2Client'

Et voila, as you can see the linked server will be created successfully and can acceess the DB2 Database. The issue is solved.

__

edit:

the linked server can only! used by it's creator. if other users want to access the server, they'll get this error:

Cannot create an instance of OLE DB provider "MSDADASQL" for linked server "MyDB2"

there is a tutorial how to give the permission to other users: http://blogs.msdn.com/b/dataaccesstechnologies/archive/2011/09/28/troubleshooting-cannot-create-an-instance-of-ole-db-provider.aspx

after you walk through the tutorial, you also need to reboot.

Linked Server from SQL Server 2012 to AIX DB2 via SSL

1 Answers