Use Microsoft Linux ODBC Driver and Linked Server to access OLEDB Data sources on Remote Systems

  • Article

By Gregory Suarez | Sr. Escalation Engineer | SQL Server

I was recently working with one of our customers when he indicated it would be great if the Microsoft Linux ODBC driver could be used to access his other database systems - in addition to Microsoft SQL Server. Apparently, he liked the driver so much; the idea of having a single database client stack could simplify administration, reduce the memory footprint of his client application and further increase performance and throughput.

At the time, I didn’t think much of this – but shortly after his comment, I realized this is something that’s easily accomplished. After all, the driver is similar to what we currently have running on the Windows platform. As long as an appropriate OLEDB provider is configured as a linked server within SQL Server everything should be good to go.

A linked server configuration enables SQL Server to execute commands against OLE DB data sources on remote servers. Linked servers offer the following advantages:

· Remote server access.

· The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.

· The ability to address diverse data sources similarly.

* Note, the MS Linux ODBC does not support distributed transaction

See the following article for more details concerning linked servers: https://msdn.microsoft.com/en-us/library/ms188279.aspx

As a test, I decided to use the following components:

1. Redhat Enterprise Linux 5.x client configured with Microsoft Linux ODBC Driver.

2. SQL Server 2008 R2 configured with a linked server to IBM’s DB2 (using the Microsoft OLEDB Provider for DB2 )

3. Sun’s Solaris 11 OS configured with IBM DB2 Version 9.7 Server (x64)

The goal is simply to return results from an IBM DB2 9.7 system running on a Solaris 11 Unix system to a Redhat Linux workstation configured with the Microsoft Linux ODBC driver. Of course, Microsoft SQL Server is sitting in the middle.

From the Redhat Linux workstation, I created and executed the following script to create the linked server:

createLinkedServer.sql

IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'SOLARIS2')EXEC master.dbo.sp_dropserver @server=N'SOLARIS2', @droplogins='droplogins'

GO
EXEC master.dbo.sp_addlinkedserver @server = N'SOLARIS2', @srvproduct=N'DB2OLEDB', @provider=N'DB2OLEDB', @datasrc=N'Solaris2', @provstr=N'Provider=DB2OLEDB;User ID=gregorys;Password=password1;Initial Catalog=TEST;Network Transport Library=TCPIP;Host CCSID=1252;PC Code Page=1252;Network Address=65.53.9.96;Network Port=50000;Package Collection=TEST;Default Schema=DB2INST1;Process Binary as Character=False;Units of Work=RUW;DBMS Platform=DB2/6000;Defer Prepare=False;DateTime As Char=False;Rowset Cache Size=0;DateTime As Date=False;Auth Encrypt=False;AutoCommit=True;Authentication=Server;Decimal As Numeric=False;FastLoad Optimize=False;Derive Parameters=True;Persist Security Info=True;Data Source=TEST;Connection Pooling=False;'

/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SOLARIS2',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO
EXEC master.dbo.sp_serveroption @server=N'SOLARIS2', @optname=N'collation compatible', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SOLARIS2', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SOLARIS2', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SOLARIS2', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SOLARIS2', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SOLARIS2', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SOLARIS2', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SOLARIS2', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'SOLARIS2', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'SOLARIS2', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SOLARIS2', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'SOLARIS2', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SOLARIS2', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

sqlcmd -i ./createLinkedServer.sql -U xxx -P xxxx -S 65.53.9.94

Next, I created a stored procedure to execute the linked query:

createStoreProcedure.sql

USE [Northwind]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Solaris2]
AS
BEGIN
SET NOCOUNT ON;
SELECT [EMPNO],[FIRSTNME],[MIDINIT] ,[LASTNAME],[WORKDEPT],[PHONENO] ,[HIREDATE],[JOB] ,[EDLEVEL] ,[SEX],[BIRTHDATE],[SALARY],[BONUS],[COMM] FROM [SOLARIS2].[TEST].[DB2INST1].[EMPLOYEE];

END
GO

sqlcmd -i ./createStoreProcedure.sql -U xxx -P xxxx -S 65.53.9.94

And finally, I executed the stored procedure.

Below, we have Redhat Linux pulling data from Solaris/DB2 - compliments of the Microsoft Linux ODBC driver and SQL Server’s linked server functionality.

1> solaris2
2> go
EMPNO FIRSTNME MIDINIT LASTNAME WORKDEPT PHONENO HIREDATE JOB EDLEVEL SEX BIRTHDATE SALARY BONUS COMM
------ ------------ ------- --------------- -------- ------- ---------------- -------- ------- --- ---------------- ----------- ----------- -----------
000010 CHRISTINE I HAAS A00 3978 1965-01-01 PRES 18 F 1933-08-24 52750.00 1000.00 4220.00
000020 MICHAEL L THOMPSON B01 3476 1973-10-10 MANAGER 18 M 1948-02-02 41250.00 800.00 3300.00
000030 SALLY A KWAN C01 4738 1975-04-05 MANAGER 20 F 1941-05-11 38250.00 800.00 3060.00
000050 JOHN B GEYER E01 6789 1949-08-17 MANAGER 16 M 1925-09-15 40175.00 800.00 3214.00
000060 IRVING F STERN D11 6423 1973-09-14 MANAGER 16 M 1945-07-07 32250.00 500.00 2580.00
000070 EVA D PULASKI D21 7831 1980-09-30 MANAGER 16 F 1953-05-26 36170.00 700.00 2893.00
000090 EILEEN W HENDERSON E11 5498 1970-08-15 MANAGER 16 F 1941-05-15 29750.00 600.00 2380.00
000100 THEODORE Q SPENSER E21 0972 1980-06-19 MANAGER 14 M 1956-12-18 26150.00 500.00 2092.00
000110 VINCENZO G LUCCHESSI A00 3490 1958-05-16 SALESREP 19 M 1929-11-05 46500.00 900.00 3720.00
000120 SEAN O'CONNELL A00 2167 1963-12-05 CLERK 14 M 1942-10-18 29250.00 600.00 2340.00
000130 DOLORES M QUINTANA C01 4578 1971-07-28 ANALYST 16 F 1925-09-15 23800.00 500.00 1904.00
000140 HEATHER A NICHOLLS C01 1793 1976-12-15 ANALYST 18 F 1946-01-19 28420.00 600.00 2274.00
000150 BRUCE ADAMSON D11 4510 1972-02-12 DESIGNER 16 M 1947-05-17 25280.00 500.00 2022.00
000160 ELIZABETH R PIANKA D11 3782 1977-10-11 DESIGNER 17 F 1955-04-12 22250.00 400.00 1780.00
000170 MASATOSHI J YOSHIMURA D11 2890 1978-09-15 DESIGNER 16 M 1951-01-05 24680.00 500.00 1974.00
000180 MARILYN S SCOUTTEN D11 1682 1973-07-07 DESIGNER 17 F 1949-02-21 21340.00 500.00 1707.00
000190 JAMES H WALKER D11 2986 1974-07-26 DESIGNER 16 M 1952-06-25 20450.00 400.00 1636.00
000200 DAVID BROWN D11 4501 1966-03-03 DESIGNER 16 M 1941-05-29 27740.00 600.00 2217.00
000210 WILLIAM T JONES D11 0942 1979-04-11 DESIGNER 17 M 1953-02-23 18270.00 400.00 1462.00
000220 JENNIFER K LUTZ D11 0672 1968-08-29 DESIGNER 18 F 1948-03-19 29840.00 600.00 2387.00
000230 JAMES J JEFFERSON D21 2094 1966-11-21 CLERK 14 M 1935-05-30 22180.00 400.00 1774.00
000240 SALVATORE M MARINO D21 3780 1979-12-05 CLERK 17 M 1954-03-31 28760.00 600.00 2301.00
000250 DANIEL S SMITH D21 0961 1969-10-30 CLERK 15 M 1939-11-12 19180.00 400.00 1534.00
000260 SYBIL P JOHNSON D21 8953 1975-09-11 CLERK 16 F 1936-10-05 17250.00 300.00 1380.00
000270 MARIA L PEREZ D21 9001 1980-09-30 CLERK 15 F 1953-05-26 27380.00 500.00 2190.00
000280 ETHEL R SCHNEIDER E11 8997 1967-03-24 OPERATOR 17 F 1936-03-28 26250.00 500.00 2100.00
000290 JOHN R PARKER E11 4502 1980-05-30 OPERATOR 12 M 1946-07-09 15340.00 300.00 1227.00
000300 PHILIP X SMITH E11 2095 1972-06-19 OPERATOR 14 M 1936-10-27 17750.00 400.00 1420.00
000310 MAUDE F SETRIGHT E11 3332 1964-09-12 OPERATOR 12 F 1931-04-21 15900.00 300.00 1272.00
000320 RAMLAL V MEHTA E21 9990 1965-07-07 FIELDREP 16 M 1932-08-11 19950.00 400.00 1596.00
000330 WING LEE E21 2103 1976-02-23 FIELDREP 14 M 1941-07-18 25370.00 500.00 2030.00
000340 JASON R GOUNOT E21 5698 1947-05-05 FIELDREP 16 M 1926-05-17 23840.00 500.00 1907.00