2

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\product\12.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\product\12.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?

  • Could you post the location of the tns.names file which contains your service? ODAC probably uses different tns.names file. – Piotr Jun 7 at 18:58
  • Question has been updated w/ path of the automatically created tnsnames.ora file. – dlh Jun 7 at 19:02
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

  • 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. – dlh Jun 7 at 19:53
  • @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. – EdStevens Jun 8 at 12:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.