Connect MS SQL Server database to Oracle 11g database

Question

I am struggling to create a db link from a Oracle Database to one SQL Server.

For that, I have added the followings:

In file tnsnames.ora:

sqlUserConn =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
      (CONNECT_DATA=(SID=sqlUserConn))
      (HS=OK)
    )

in file listener.ora:

(SID_DESC=
  (SID_NAME=sqlUserConn)
  (ORACLE_HOME=C:\oraclexe\app\oracle\product\11.2.0\server)
  (PROGRAM=dg4odbc)
)

in file sqlnet.ora:

SQLNET.AUTHENTICATION_SERVICES = (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

in file initsqlUserConn.ora:

HS_FDS_CONNECT_INFO = sqlUserConn
HS_FDS_TRACE_LEVEL = OFF

The files tnsnames.ora, sqlnet.ora and listener.ora are placed in **server\network\admin** and initsqlUserConn.ora is placed in **server\hs\admin**

I have the following sql statement:

CREATE DATABASE LINK sqlUserConn CONNECT TO "USER" IDENTIFIED BY "PASSWORD" USING 'sqlUserConn';

And then to test the db link, I am running something similar to:

Select * from table@sqlUserConn

This command is firing the following error:

ORA-12154: TNS:could not resolve the connect identifier specified

Obviously, I have done something wrong.. but still I don't get it.. It should work as I have seen a lot of users doing it like this

Could someone point me to the correct solution for linking a SQL Server database to Oracle 11g?

Did you create a data source name DSN on the oracle box pointing to the sql server database? Does it work isolated? What does lsnrctl status say? — Bjarte Brandt
I am relatively new to Oracle Databases so I don't know what "oracle box" means.. If you are refering to the system DSN from Windows, yes, I have already created a system DSN named sqlUserConn. I tried to tnsping it but still out of chance.. — rosuandreimihai
The TNS listener (and its configuration) only deals with connections to an Oracle server. And a DBLink can also connect to Oracle. — a_horse_with_no_name
Ok, I think I might have figured it out: the DSN was setup on 32-bit architecture first of all, second I had to reboot the PC because the lsnrctl reload or lsnrctl stop and then start were not actually doing anything and now, using those info's from above everything works as supossed — rosuandreimihai
"oracle box" - where the oracle instance is running (~20 background processes: smon,pmon,etc..). Hard to answer your question. My fellow Windows sysadmins are rebooting to make problems go away. This is something you avoid on *nix platforms. Logfiles will guide you in sorting out problems. — Bjarte Brandt

rosuandreimihai rosuandreimihai · Accepted Answer · 2016-11-05T15:17:24

First of all, make sure you are using the correct DSN architecture (32 or 64 bits). It will fire errors if using incorrect architecture. Oracle on Windows has a strange behavior, therefor the command lsnrctl reload or even using lsnrctl stop and then lsnrctl start wont help in refreshing the info placed in the files specified (tnsnames, listener, etc..). So the only solution is to reboot and then check if dblink works.

Connect MS SQL Server database to Oracle 11g database

1 Answers