configure new linked server from SQL Server 2016 to Oracle 12c

Question

I'm having difficulty creating a Linked Server in SQL Server 2016 to talk to a remote Oracle database.

I have installed Oracle Database Client 12c. First I installed InstantClient and then later realizing I needed more functionality installed Runtime. During the setup I accepted C:\Oracle\product12円.1.0\client_1 as the Oracle home directory. For the Oracle Home User, I used the Windows Built-in Account NT AUTHORITY\LOCAL SERVICE.

I used Oracle Net Configuration Assistant to create a tnsnames.ora file with alias = MyAlias and service name = MyServiceName. Net Configuration Assistant was able to test the database connection successfully after I provided credentials for the remote database.

The tnsnames.ora file created by Oracle Net Configuration Assistant was in this folder: C:\Oracle\product12円.1.0\client_1\network\admin

From a Powershell prompt, I ran tnsping MyAlias and got OK (200 msec).

I think (hope!) this means I got the Oracle side correct.

On the Microsoft side, I installed .NET Framework 3.5. Then I downloaded and installed Oracle Data Access Components (ODAC) from here. Specifically I downloaded ODAC122010Xcopy_x64.zip and followed the instructions in the readme file included. The command line options I used during install were:

 .\install.bat all c:\oracle odac true true

The parameters of the above commmand were:

• component_name = all (install all Oracle Providers)
• oracle_home_path = c:\oracle (should it be the Oracle base or the subfolder?)
• oracle_home_name = odac (determines registry key path)
• install_dependents = true
• machine_wide_configuration = true (this parameter was a guess; I want this to work for all users of this machine)

I then added C:\oracle and C:\oracle\bin to the beginning of the Environment variable PATH. In Powershell this was:

 [Environment]::SetEnvironmentVariable("Path","C:\oracle;C:\oracle\bin;" + $env:Path, [System.EnvironmentVariableTarget]::Machine)

Within SQL Server Management Studio, I can see OraOLEDB.Oracle in the list of Linked Server Providers.

Now trying to create the Linked Server. Here's my config:

screenshot of SSMS Linked Server wizard

For security, I'm trying for now with a fixed security context, using the same credentials that worked when testing the connection with Oracle Net Configuration Assistant.

It doesn't work. Here's the generic error:

The linked server has been created but failed a connection test. Do you want to keep the linked server?

Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "MYLINKEDSERVER". OLE DB provider "OraOLEDB.Oracle" for linked server "MYLINKEDSERVER" returned message "ORA-12154: TNS:could not resolve the connect identifier specified". (Microsoft SQL Server, Error: 7303)

What am I missing? What diagnostic tests would be valuable?

• Piotr Palka: Could you post the location of the tns.names file which contains your service? ODAC probably uses different tns.names file.
• dlh: Question has been updated w/ path of the automatically created tnsnames.ora file.

Answer 1

Try changing your Data Source and Provider String to MYALIAS. Looking at the three Oracle Linked Servers I have on my server, that is how I have them setup.enter image description here

• dlh: It turns out that Product Name doesn't have any effect. It can be "Oracle" or "asdfasdf". Provider string can remain blank. The real fix was Data source has to be MyAlias.
• EdStevens: @dlh - well, to be more accurate, the real fix was Data source has to be the name of a net service name in your tnsnames.ora file. Not necessarily "MyAlias". Your root problem, as identified by the ORA-12154 in your error stack, was the value of Data source was not found as a net service name in tnsnames.ora.
• Willie Cheng: Hi all , How to set the role with "SYSDBA" into the "Linked server"

Answer 2

I checked it today on my MS SQL 2016 and oracle 12c and in works fine.

You don't need oracle instant client - only oracle ole db provider and tnsnames.ora

you have to set "TNS_ADMIN=C:\oracle\network\admin" and "ORA_CLIENT_PATH=C:\oracle" and add "c:\oracle;c:\oracle\bin" to PATH

in the "Linked Server" Setting you have to fill "dataSource" with the alias from tnsname.ora. "productName" can be empty

rolf

Answer 3

at the command prompt (cmd) type

tnsping dbname

The description string you get is the connect information. The script below should create the basic connect information for a linked server to Oracle in SQL Server management studio.

EXEC master.dbo.sp_addlinkedserver @server = N'ORCL12C', @srvproduct=N'Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'Orcl12c', @provstr=N'((DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ORCL12C)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl12c)));'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ORCL12C',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ORCL12C',@useself=N'False',@locallogin=N'HOME2\hughsm',@rmtuser=N'NORTHWIND',@rmtpassword=N'password'

• Marcello Miorelli: that did not work for me, starting with tnsping