Start Free TrialLog in
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
Avatar of David Johnson, CD
David Johnson, CD
Flag of Canada image
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?
Avatar of contactsam
contactsam

ASKER

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
Avatar of David Johnson, CD
David Johnson, CD
Flag of Canada image
sql server 2008 is also end of life and no longer supported.

is port 1433 open on the windows server for sql connection?
Avatar of contactsam
contactsam

ASKER

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
Avatar of contactsam
contactsam

ASKER

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
Avatar of Qlemo
Qlemo
Flag of Germany image
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.
Avatar of contactsam
contactsam

ASKER

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



Avatar of Qlemo
Qlemo
Flag of Germany image
That line should have removed the error, but you might have to restrat the listener.
Ifit still errors out, please provide the error message.

Avatar of contactsam
contactsam

ASKER

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


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

ASKER

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

Avatar of contactsam
contactsam

ASKER

Snapshot of listener.ora
(SID_DESC =
      (SID_NAME = mysource1)
      (ORACLE_HOME = D:\app\crm.sa\product\12.2.0\dbhome_1)
      (PROGRAM = dg4odbc)
    )
    )
Avatar of contactsam
contactsam

ASKER

Snapshot of sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES= (NONE)
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
Avatar of Qlemo
Qlemo
Flag of Germany image
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.

Avatar of contactsam
contactsam

ASKER

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


 
Avatar of Qlemo
Qlemo
Flag of Germany image
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.

Avatar of Qlemo
Qlemo
Flag of Germany image
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).
Avatar of contactsam
contactsam

ASKER

Hi,

Is Oracle gateway binaries included as part of  oracle database binaries 12.2.x or needs to be installed separately
Avatar of Qlemo
Qlemo
Flag of Germany image
The ODBC gateway is part of the installed files, you just need to configure it.
Avatar of contactsam
contactsam

ASKER

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

Avatar of ste5an
ste5an
Flag of Germany image
This is a security gain.
ASKER CERTIFIED SOLUTION
Avatar of Qlemo
Qlemo
Flag of Germany image
Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts

  • "Invaluable tool for our organization"

    Josh Regalski

  • "Your help has saved me hundreds of hours of internet surfing."

    @fblack61

  • "Just a dang good service!"

    @golferski

  • "Worth every penny."

    Steve Hougom

  • "It’s been a life saver."

    Maria Halt

  • "Best support site I’ve seen!"

    Bob Schneider

  • "I would be lost without them."

    @fblack61

  • "Saved my job multiple times."

    Walt Forbes

  • "I love this site."

    Maria Duwe

  • "Friendly respectful help."

    Andrew Kowtalo

  • "Such top-notch experts."

    @magento

  • "The best money I have ever spent."

    @rwheeler23

  • "Beyond any comprehensible value"

    George Morris

  • "Invaluable tool for our organization"

    Josh Regalski

Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo
© 1996-2023 Experts Exchange, LLC. All rights reserved. Covered by US Patent