Avatar of contactsam
contactsam asked on

Establish connection from Oracle DB to MSSQL Server

Hi,

We are running into challenge while connecting to SQL server 2008 hosted on Windows server 2008 machine from Oracle database server running on 12c. The objective is to establish db link between Oracle and MSSQL server to
view the sql server data. Both Oracle and MSSQL server are running on 2 different host machine. For this we performed the following steps


a) From the oracle server changed the parameter inithsodbc.ora (D:\app\crm.sa\product\12.2.0\dbhome_1\hs\admin
HS_FDS_CONNECT_INFO = mysource1
HS_FDS_TRACE_LEVEL = OFF


b) Modified tnsnames.ora to point to mysource1
mysource1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dhqdbp25.doti.ae)(PORT = 1433))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = mysource1)
      (HS=OK)
    )
  )
c) Modified listener.ora
(SID_DESC =
      (SID_NAME = mysource1)
      (ORACLE_HOME = D:\app\crm.sa\product\12.2.0\dbhome_1)
      (PROGRAM = HSODBC)
    )
    )
d) Finally created odbc connect string(mysource1) from system dsn hosted on oracle database server
and the connection was tested successfully.

Finally we tested with tnsping to mysource 1
C:\Users\crm.sa>tnsping mysource1
TNS Ping Utility for 64-bit Windows: Version 12.2.0.1.0 - Production on 26-SEP-2021 03:43:48
Copyright (c) 1997, 2016, Oracle.  All rights reserved.
Used parameter files:
D:\app\crm.sa\product\12.2.0\dbhome_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dhqdbp25.doti.ae)(PORT = 1433))) (CONNECT_DATA = (SERVICE_NAME = mysource1) (HS=OK)))
TNS-12537: TNS:connection closed

SQLWindows OSMicrosoft SQL ServerOracle Database

Avatar of undefined
Last Comment
Qlemo

8/22/2022 - Mon
David Johnson, CD

you do realize that both the OS and the SQL Server version are no long supported and are a security issue.

can you connect from SQL Server to Oracle? It appears you have problems connecting FROM Oracle to SQL Server.. is sql server setup for sql authentication?
ASKER
contactsam

Hello David,

Thank you for the update. You are right there is security issue as the O/S for SQL server is no longer supported
For now we would like to test and retrieve useful data from SQL Server to be displayed on CRM application(Oracle)

We tested connectivity from Oracle to SQL database via odbc and also we were able to query from SQL server database.
But we have not tested connectivity from SQL to Oracle. Is it mandatory to test the connectivity.
SQL server is setup with sql authentication. Validated.

Regards
David Johnson, CD

sql server 2008 is also end of life and no longer supported.

is port 1433 open on the windows server for sql connection?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER
contactsam

Yes, we can do telnet from Oracle db server to MSQL server
Oracle DB> telnet dhqdbp25 1433, Port is open, where dhqdbp25 is MSSQL Server
ASKER
contactsam

From the sql server log file(dhqdbp25), following message did appear
Date      9/25/2021 3:52:55 PM
Log      SQL Server (Current - 9/25/2021 12:06:00 PM)
Source      Logon
Message
Length specified in network packet payload did not match number of bytes read; the connection has been closed. Please contact the vendor of the client library. [CLIENT: 10.100.25.205]
** 10.100.25.205 : Oracle database esrver
Qlemo

Your TNSNames.ora entry always needs to point to a Listener entry, here to that of the gateway.
mysource1 =
  (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (CONNECT_DATA = (SERVICE_NAME = mysource1))
      (HS=OK)
  )

Open in new window

All the real connection info is managed by the ODBC DSN reference in inithsodbc.ora.

Since 11g you should replace hsodbc by dg4odbc in the listener.ora.

Remember to set up a database link to be able to provide credentials for the MSSQL connecftion. The ODBC entry itself does not allow for storing those.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
contactsam

Hello,
After making the above changes,
tnsping worked fine.earlier it errored out connection refused.

created dblink and tried to run below sql statement
select * from CALL_CENTRE_CRM@mysourcedsn
EROR at line 1:
ORA-12638: Credential retrieval failed
After searching in google, updated sqlnet.ora with below line
SQLNET.AUTHENTICATION_SERVICES= (NONE)
it errored out, can share the error message in next update



Qlemo

That line should have removed the error, but you might have to restrat the listener.
Ifit still errors out, please provide the error message.

ASKER
contactsam

Hello,
After changing to SQLNET.AUTHENTICATION_SERVICES= (NONE),restarted listener, here is the error message.
C:\Users\crm.sa>sqlplus / as sysdba;
SQL*Plus: Release 12.2.0.1.0 Production on Sun Sep 26 17:57:24 2021
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name: sys as sysdba
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> select * from CALL_CENTRE_CRM@mysourcedsn2;
select * from CALL_CENTRE_CRM@mysourcedsn2
ERROR at line 1:
ORA-28546: connection initialization failed, probable Net8 admin error
ORA-02063: preceding line from MYSOURCEDSN2


Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Qlemo

The error messages is created if the kind of connection (Oracle or HS/DG) is wrong. Check the tnsnames.ora entry.
ASKER
contactsam

Hello
Here is the TNS entry for sql server data source
MYSOURCE1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dhqdbd22.dotstg.ae)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = mysource1)
    )
  )

Open in new window

Is there validation that can be performed against above tns entry. mysource1 is configured in system dsn pointing to sql server database

ASKER
contactsam

Snapshot of listener.ora
(SID_DESC =
      (SID_NAME = mysource1)
      (ORACLE_HOME = D:\app\crm.sa\product\12.2.0\dbhome_1)
      (PROGRAM = dg4odbc)
    )
    )
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
contactsam

Snapshot of sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES= (NONE)
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
Qlemo

Again: the TNSNames.ora references to the listener configuration - no reference to the real data source there. The only reference is in that init*.ora file.
The host dhqdbd22.dotstg.ae you noted there is different from the MSSQL host dhqdbp25.doti.ae. Is that where the HS listener config is stored and running? If that's true, I don't know what is going wrong here from the data you posted.

ASKER
contactsam

Reviewed above comments
dhqdbd22: Oracle database server, HS listener config is stored and running.
dhqdbp25: MSQL server

Kindly clarify the following statement
The TNSNames.ora references to the listener configuration - no reference to the real data source there. The only reference is in that init*.ora file.

If you look at tnsnames.ora, there is line that refers to sqldatabase server. This is same service name that is configured in system dsn-odbc pointing to sql database server and database name.
SERVICE_NAME = mysource1
Is there anything missing that needs to be udpated in tnsnames.ora

Regards


 
Your help has saved me hundreds of hours of internet surfing.
fblack61
Qlemo

To clarifiy the real names used on different places, here a config using different and obvious names:
Listener.ora
    (SID_DESC =
      (SID_NAME = HSListenerSID)
      (ORACLE_HOME = D:\app\crm.sa\product\12.2.0\dbhome_1)
      (PROGRAM = dg4odbc)
    )

Open in new window


TNSNames.ora:
TNS_MsSql =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = HSListenerHost)(PORT = 1521))
    (CONNECT_DATA = (SERVICE_NAME = HSListenerSID)
    (HS=OK)
  )

Open in new window

(btw, you forgot that (HS=OK) line in your last TNSNames post).

initHSListenerSID.ora (D:\app\crm.sa\product\12.2.0\dbhome_1\hs\admin)
HS_FDS_CONNECT_INFO = mysource1

Open in new window

Note that the name is not inithsodbc.ora, that is just the template name for your own init files.

Translated to your environment, you need to add ths HS=OK line in tnsnames.ora, and rename your inithsodbc.ora file to initmysource1.ora.

Qlemo

FWIW, https://www.experts-exchange.com/articles/9850/Configuring-and-using-Oracle-Database-Gateway-for-ODBC.html describes how to configure HS/DG with Access (but I don't think it adds anything more than what I've written here).
ASKER
contactsam

Hi,

Is Oracle gateway binaries included as part of  oracle database binaries 12.2.x or needs to be installed separately
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Qlemo

The ODBC gateway is part of the installed files, you just need to configure it.
ASKER
contactsam

Thank you ,
After following above steps, we were finally able to establish dblink between Oracle and SQL Server.
To reach this level, we had to change in sqlnet.ora,
SQLNET.AUTHENTICATION_SERVICES= (NONE)

The flip side, we could not login using sys / as sysdba,

Any suggestion is appreciated.

Regards

ste5an

This is a security gain.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER CERTIFIED SOLUTION
Qlemo

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question