Also make sure you have installed Oracle Client on the SQL server. If the SQL server is 64 bit then we need to install 64 bit Oracle provider. You can also create linked server using Oracle ODBC driver together with Microsoft OLE DB provider for ODBC. Once again on a 64 bit SQL server you need to install the
64-Bit OLEDB Provider for ODBC (MSDASQL)
and 64 bit Oracle ODBC drivers. However 64-Bit OLEDB Provider for ODBC (MSDASQL) is already there in Windows Vista/Windows Server 2008 and later OS.
This particular error message is a very general error message and can happen for quite a number of reasons. For general understanding of the error, you can review oracle documentation like this
In SQL Server Linked Server, it could indicate a few things (not limited to)–
1. SQL Server (and oracle net libraries) is not able to get the TNS alias from tnsnames.ora file.
2. Something is wrong with the way the alias is created in the tnsnames.ora file (incorrect syntax)
3. TNS alias could not be resolved into a connect descriptor
Below is a list of things that you can try to resolve this issue.
1. Verify that the tnsnames.ora file has the alias and the service name that the customer is using.
Note: make sure that the path is a valid path and there is no space.
6. Check the value of the key ”Oracle_Home” in the registry under HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraClient11g_home1 and verify that it has the right path for the Oracle home.
7. Check for the registry key “TNS_ADMIN” at HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE. If it exists then make sure it has the right value as “Dir:\app\product\11.1.0\client_1\network\admin”. If you don’t see the key then create the key and set appropriate value as below.
Regedit->HKEY_LOCAL_MACHINE->Software->Oracle->RightClick NEW->StringValue and name
it TNS_ADMIN and give the value “X:\app\product\11.1.0\client_1\network\admin”
Note: This is not a must but in some cases this is what fixed the issue.
8. Check if SQL server start up account has permission to the Oracle Home. Also collect Process monitor log and check for “access denied”. Process monitor log should show if we are able to find the tnsnames.ora file.
9. Make sure you don’t have multiple Oracle homes or multiple Oracle clients installed. Check the "HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ALL_HOMES\HOME_COUNTER" key value.
10. Check if Oracle OLE DB provider is running InProcess. If ‘yes’ then try to run out-of- process and see if that resolves the issue.
Note: You can check and verify if MS OLE DB Provider for Oracle is running InProcess from the registry key at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\MSDAORA
11. You can try collecting simultaneous Network trace from both SQL and Oracle servers and check if there are any communications between the two servers.
12. Try to connect to Oracle from the SQL server using the UDL. Use the same TNS name. If you get the same error that means the issue is not specific to SSMS or linked server.
Creating and Configuring Universal Data Link (.udl) Files