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

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:

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?