Oracle talking to SQL Server over a normal database link?

Now with all my (off topic) posting about SQL Server these past few weeks it was inevitable that at some point i would want to create a direct link between Oracle and SQL Server for interfacing.

Now annoyingly this is quite easy from SQL to Oracle - it's a little more fiddly the other way round.

I've been meaning to post on this for a while as it's something I've done many time over the years and I always struggle to find a good note that describes all the steps required to create a direct link.

And to be clear here what i am doing is making a SQL Server database 'appear' at the end of a normal database link. So i will have the ability to say

select * from table@SQLServer

directly from in sqlplus - now wouldn't that be nice......

My example here uses what is known as "database gateway for odbc" or "dg4odbc for short (in earlier oracle version prior to 11.1 it was knows as "heterogeneous services for odbc" or "hsodbc"). Neither of those flows off the tongue very well. You'll see as i go through the example a lot of things still refer to "hs" - this is just for historic reasons.

In my case I'm running Oracle 11.2.0.2 (just what i had to hand no particular reason for that exact version) and i'm on SLES11 sp 2 (though i think the steps shown are reasonably Linux generic).

Before we get started just a quick summary of how the thing hangs together - the flow of processing is as follows:

Database link -> tnsnames -> listener ->dg4odbc->odbc driver -> sql server

so we are essentially creating an odbc connection and then telling oracle how to get to it and use it.

Right now that's all clear then lets get started.

For me the first thing to do was to get out friendly unix team to install some software for me

The 3 rpms they installed were (though i think the development one is not required)

# rpm -qa |grep -i odbc

unixODBC-devel-2.2.12-198.17

unixODBC-2.2.12-198.17

freetds-unixodbc-0.91-1

This was done for me as i do not have root rights.

unixODBC is the driver manager software (just think of the odbc tool on windows where lots of different vendors drivers are installed and managed - this is the driver manager software)

freetds is a free odbc driver that allows connection to SQL Server (others are available of course)

Once that software is installed what i first want to prove is that i can just create an odbc connection outside of oracle.

After installation of unixODBC i have 2 config files to deal with

/etc/unixODBC/odbc.ini - containing specific database connection details - driver/location etc

and

/etc/unixODBC/odbcinst.ini - which contains the odbc drivers installed on the system (in this case just FreeTDS)

So in the odbcinst.ini i have

[FreeTDS]

Description = FreeTDS unixODBC Driver

Driver = /usr/lib64/libtdsodbc.so.0

Setup = /usr/lib64/libtdsodbc.so.0

UsageCount = 1

At this point the odbc.ini was empty

Now as i don;t have root rights i got the unix team to give me sudo access to run the odbcinst program which lets you add and remove drivers/database connections (this you don't have to do - you could just edit the 2 files directly if you have rights)

I preconfigured a template file (/tmp/test.temp) with the details of what i wanted to connect to - this takes the following format

[DEMO]

Driver = FreeTDS

Description = Demo connection

Trace = No

Server = sql server hostname here

Database = sql server db name here

UID = username here

Password=password here

Port = 1435

As you can see this entry is specifically saying i want to use the FreeTDS driver please. To install this i now run

sudo odbcinst -i -s -f /tmp/test.temp

i for install

s for data source

and f for the template file with the definition

Now odbc.ini contains the definition i just installed - i am now in a position to test it - this can be done with the isql tool which comes as part of unixodbc

I just pass in the DSN name (DEMO as defined earlier) as well as the username and password (which makes me wonder why they are in the config file at all - anyway...)

oracle@server:> isql DEMO username password

+---------------------------------------+

| Connected! |

| |

| sql-statement |

| help [tablename] |

| quit |

| |

+---------------------------------------+

SQL>

And we get a Connected! message implying this is some kind of miracle.......

Now lets try and run some SQL

SQL> select product from master.sys.servers;

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| product |

| SQL Server |

SQLRowCount returns 1

1 rows fetched

And all that seems fine - we have a working odbc driver in place and we can get to our desired destination - now comes the tricky part - wrapping up the Oracle layer on top - this was not easy to get to the bottom of (even though I've done this many times before on other platforms....)

The first thing i did was create a new listener - this isn't 100% essential it could be all in one but it's nice to keep it away from the 'normal' stuff

first up new listener

/oracle/11.2.0.2.0.DB/network/admin]# cat listener.ora

DEMO =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = server)(PORT = 1545))

)

SID_LIST_DEMO =

(SID_LIST =

(SID_DESC=

(SID_NAME=DEMO)

(ORACLE_HOME=/oracle/11.2.0.2.0.DB)

(PROGRAM=hsodbc)

)

Nothing really special there - just the program part may look odd to you

The next step is to create something that maps to the SID_NAME of DEMO listed in the listener.ora - what will happen when connections are sent to the listener is that it will call the hsodbc program and try and connect to something which has a config called DEMO.

This DEMO config sits in $ORACLE_HOME/hs (probably a directory you never noticed before...)

The content of this file is as follows:

/oracle/11.2.0.2.0.DB/hs/admin]# cat initDEMO.ora

HS_FDS_CONNECT_INFO = DEMO

HS_FDS_TRACE_LEVEL = 1

HS_FDS_TRACE_FILE_NAME = /tmp/hstrace.log

HS_FDS_SHAREABLE_NAME = /usr/lib64/libtdsodbc.so.0

The first line is the key mapping line here we are saying the listener has routed something with a SID_NAME of DEMO to this file - this will now go off and try and find a HS_FDS_CONNECT_INFO (the DSN basically) - also called DEMO.

We then need a tns entry for out client to be able to find out where this SQL Server DB is - note the content refers to the local server on the new listener port we just created (nothing remote here) and we have to include the special HS=OK value to tell oracle there is some crazy odbc stuff going on.

DEMO =

( DESCRIPTION =

( ADDRESS =

( PROTOCOL = TCP )

( HOST = servername)

( PORT = 1545 )

)

( CONNECT_DATA =

( SID = DEMO )

)

( HS = OK )

)

If we try and tnsping this address

tnsping demo

TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 10-DEC-2014 19:42:19

Used parameter files:

/oracle/11.2.0.2.0.DB/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact ( DESCRIPTION = ( ADDRESS = ( PROTOCOL = TCP) ( HOST = server) ( PORT = 1545)) ( CONNECT_DATA = ( SID = DEMO)) ( HS = OK))

TNS-12541: TNS:no listener

We get a failure as the listener is not started - so lets start it

lsnrctl start demo

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 10-DEC-2014 19:42:42

Starting /oracle/11.2.0.2.0.DB/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.2.0 - Production

System parameter file is /oracle/11.2.0.2.0.DB/network/admin/listener.ora

Log messages written to /oracle/diag/tnslsnr/server/demo/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server)(PORT=1545)))

TNS-01201: Listener cannot find executable /oracle/11.2.0.2.0.DB/bin/hsodbc for SID DEMO

Listener failed to start. See the error message(s) above...

And now begins the first of many issues in getting this working.... At least this first issue is obvious - it's back to the point i mentioned earlier - the whole thing was renamed so hsodbc no longer exists - i need to update my listener entry with the new program dg4odbc - which i do below

DEMO =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = server)(PORT = 1545))

)

SID_LIST_DEMO =

(SID_LIST =

(SID_DESC=

(SID_NAME=DEMO)

(ORACLE_HOME=/oracle/11.2.0.2.0.DB)

(PROGRAM=dg4odbc)

)

Now i try and start it again

lsnrctl start demo

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 10-DEC-2014 19:45:15

Starting /oracle/11.2.0.2.0.DB/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.2.0 - Production

System parameter file is /oracle/11.2.0.2.0.DB/network/admin/listener.ora

Log messages written to /oracle/diag/tnslsnr/server/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server=1545)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=server=1545)))

STATUS of the LISTENER

------------------------

Alias demo

Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production

Start Date 10-DEC-2014 19:45:16

Uptime 0 days 0 hr. 0 min. 0 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /oracle/11.2.0.2.0.DB/network/admin/listener.ora

Listener Log File /oracle/diag/tnslsnr/server/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server=1545)))

Services Summary...

Service "DEMO" has 1 instance(s).

Instance "DEMO", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

Thats better - now lets try the tnsping

tnsping demo

TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 10-DEC-2014 19:45:28

Used parameter files:

/oracle/11.2.0.2.0.DB/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact ( DESCRIPTION = ( ADDRESS = ( PROTOCOL = TCP) ( HOST = server) ( PORT = 1545)) ( CONNECT_DATA = ( SID = DEMO)) ( HS = OK))

OK (10 msec)

So now we are looking good

Lets try sqlplus now to this connection (using some random username/password)

sqlplus a/b@demo

SQL*Plus: Release 11.2.0.2.0 Production on Wed Dec 10 19:46:08 2014

ERROR:

ORA-28547: connection to server failed, probable Oracle Net admin error

That's not good...a check in the log directory shows this.....

$ORACLE_HOME/hs/log

Oracle Corporation --- WEDNESDAY DEC 10 2014 19:46:08.804

Heterogeneous Agent Release

11.2.0.2.0

HS Agent diagnosed error on initial communication,

probable cause is an error in network administration

Network error 2: NCR-00002: NCR: Invalid usage

HS Gateway: NULL connection context at exit

But the listener seems happy enough.....

10-DEC-2014 19:48:24 * (CONNECT_DATA=(SID=DEMO)(CID=(PROGRAM=sqlplus@server)(HOST=server)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=51495)) * establish * DEMO * 0

So what could be wrong - a bit of digging (hoping really as there was not a lot to go on) revealed that maybe i had to tell it exactly where the odbc.ini file was - so i did that in the initDEMO.ora file by adding a line at the end.

HS_FDS_CONNECT_INFO = DEMO

HS_FDS_TRACE_LEVEL = Debug

HS_FDS_TRACE_FILE_NAME = /tmp/hstrace.log

HS_FDS_SHAREABLE_NAME = /usr/lib64/libtdsodbc.so.0

set ODBCINI=/etc/unixODBC/odbc.ini

I also realized that i had to also add some environment specific LD_LIBRARY_PATH settings so that the listener would be able to find all the libraries it needed - so i added an extra line to the listener.ora as shown below

DEMO =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = server)(PORT = 1545))

)

SID_LIST_DEMO =

(SID_LIST =

(SID_DESC=

(SID_NAME=DEMO)

(ORACLE_HOME=/oracle/11.2.0.2.0.DB)

(PROGRAM=dg4odbc)

(ENVS=LD_LIBRARY_PATH=/usr/lib64/unixODBC:$ORACLE_HOME/lib)

)

To check these are picked up ok after a restart we can do some clever stuff in lsnrctl to reveal it - see demo below

LSNRCTL> set displaymode verbose

Service display mode is VERBOSE

LSNRCTL> services

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=server)(PORT=1545)))

Services Summary...

Service "DEMO" has 1 instance(s).

Instance "DEMO", status UNKNOWN, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:1 refused:0

LOCAL SERVER

(ADDRESS=(PROTOCOL=beq)(PROGRAM=/oracle/11.2.0.2.0.DB/bin/dg4odbc)(ENVS='LD_LIBRARY_PATH=/usr/lib64/unixODB C:$ORACLE_HOME/lib,ORACLE_HOME=/oracle/11.2.0.2.0.DB,ORACLE_SID=DEMO')(ARGV0=dg4odbcDEMO)(ARGS='(LOCAL=NO)'))

The command completed successfully

So now feeling confident lets create a database link and give it a try..... (and note i used username and password in double quotes to force the specific case to be kept)

create database link demo connect to "username" identified by

"password" using 'DEMO';

Database link created.

Now lets try and select from it

select product from "master.sys.databases"@demo;

select product from "master.sys.databases"@demo

ERROR at line 1:

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

ORA-02063: preceding line from DEMO

And a suitable disaster - lets check the logs again - and at least we get something better to go on - progress at least

from hs/log directory

Entered hgolofns at 2014/12/10-20:12:04

hoaerr:28500

Exiting hgolofns at 2014/12/10-20:12:04

Failed to load ODBC library symbol: /usr/lib64/libtdsodbc.so.0(SQLDescribeParam)

A bit of digging again revealed the issue - i had mentioned the freetds library in my config - that was wrong - i needed the generic unixodbc library - so i updated that in the initDEMO.ora file

HS_FDS_CONNECT_INFO = DEMO

HS_FDS_TRACE_LEVEL = Debug

HS_FDS_TRACE_FILE_NAME = /tmp/hstrace.log

HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so

set ODBCINI=/etc/unixODBC/odbc.ini

I restarted listener just in case and tried again

select product from "master.sys.databases"@demo;

select product from "master.sys.databases"@demo

ERROR at line 1:

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

[

Still not working but the trace file changed

Exiting hgopoer, rc=0 at 2014/12/10-20:17:04

hgocont, line 2754: calling SqlDriverConnect got sqlstate I

Exiting hgocont, rc=28500 at 2014/12/10-20:17:04 with error ptr FILE:hgocont.c LINE:2774 FUNCTION:hgocont() ID:Something other than invalid authorization

Exiting hgolgon, rc=28500 at 2014/12/10-20:17:04 with error ptr FILE:hgolgon.c LINE:790 FUNCTION:hgolgon() ID:Calling hgocont

Another quick search revealed that this setting may need to be mentioned in the odbc.ini and odbcinst.ini for all freetds connections

TDS_Version = 8.0

So i added the line into the odbc.ini and odbcinst.ini (well i had to use odbcinst to do it - you might be able to just vi the file) - as an example here is the odbcinst.ini config now

cat odbcinst.ini

[FreeTDS]

Description = FreeTDS unixODBC Driver

Driver = /usr/lib64/libtdsodbc.so.0

Setup = /usr/lib64/libtdsodbc.so.0

UsageCount = 3

TDS_Version = 8.0

I then tried again...this time getting a different error - ORA-28513 returned - looking and traces and googling then revealed a multibyte characterset problem - this could be resolved by setting yet another config line in the initDEMO.ora config file to fix the LANGUAGE/CHARSET to be used - see HS_LANGUAGE below

cat initDEMO.ora

HS_FDS_CONNECT_INFO = DEMO

HS_FDS_TRACE_LEVEL = Debug

HS_FDS_TRACE_FILE_NAME = /tmp/hstrace.log

HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so

HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1

set ODBCINI=/etc/unixODBC/odbc.ini

And we try again........

select product from "master.sys.databases"@demo;

select product from "master.sys.databases"@demo

ERROR at line 1:

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

[unixODBC][FreeTDS][SQL Server]Login failed for user 'username'.

{42000,NativeErr = 18456}[unixODBC][FreeTDS][SQL Server]Unable to connect to

data source {08001}

ORA-02063: preceding 2 lines from DEMO

And this is the first point where it looks like a credential issue - lets see what sql server says

Message

Login failed for user 'username'. Reason: Password did not match that for the login provided. [CLIENT: server]

And sure enough i discovered a type in my password - lets fix that by dropping and recreating the db link to give it the correct password.

And now we try again (again)

select product from "master.sys.databases"@demo;

select product from "master.sys.databases"@demo

ERROR at line 1:

ORA-00942: table or view does not exist

[FreeTDS][SQL Server]Invalid object name 'master.sys.databases'.

{42S02,NativeErr = 208}[FreeTDS][SQL Server]Statement(s) could not be prepared.

{42000,NativeErr = 8180}[FreeTDS][SQL Server]Invalid object name

'master.sys.databases'. {42S02,NativeErr = 208}[FreeTDS][SQL

Server]Statement(s) could not be prepared. {42000,NativeErr = 8180}

ORA-02063: preceding 2 lines from DEMO

Now that's looking much better -it connected it just doesn't like my SQL - lets try something a bit simpler with none of that dot business going on

select * from table_i_know_exists@demo

and it works (for a few rows)

until.....

ERROR:

ORA-28528: Heterogeneous Services datatype conversion error

ORA-02063: preceding line from DEMO

Aaargh

Another investigation shows me its some 32/64 conversion error going on somewhere in the chain - again there is an easy fix

Another new config line in initDEMO.ora (you can see a pattern developing here right......) This time its HS_FDS_SQLLEN_INTERPRETATION=32

HS_FDS_CONNECT_INFO = DEMO

HS_FDS_TRACE_LEVEL = Debug

HS_FDS_TRACE_FILE_NAME = /tmp/hstrace.log

HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so

HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1

HS_FDS_SQLLEN_INTERPRETATION=32

set ODBCINI=/etc/unixODBC/odbc.ini

Now with that all added it works a treat! Now i just need to switch off the debug stuff so it runs much faster!

So there you have it how to link oracle directly to SQL Server - for free! (well as long as you don't need to buy ODBC drivers its free as this function is part of the base product from Oracle). There are further enhanced version of these kind of connections (referred to as gateways) where there is a specific extra piece of software to be installed - this is all discussed in this excellent blog note from oracle here:

https://blogs.oracle.com/db/entry/oracle_gateway_master_note

Within that it refers to a metalink ( i still refuse to call it MOS) note which explicit states the facts from a licence point of view:

https://support.oracle.com/epmos/faces/DocumentDisplay?id=232482.1

So what are you waiting for give it a try.........

Comments

Mike Kirtley5 January 2015 at 00:30
Hi - you are using an Oracle 11.2 setup but in the listener.ora you have -
(PROGRAM=hsodbc)
For 11g the executable was renamed to 'dg4odbc' so you should have -
(PROGRAM=dg4odbc)
Regards,
Mike
Mike5 January 2015 at 08:36
Hi Rich - sorry, I didn't scroll down after spotting the error. I work with the gateways on a daily basis and just noticed that error straight off. Regards, Mike

#cloud blog

Search This Blog

Oracle talking to SQL Server over a normal database link?

Comments

Post a Comment