This is an old question but Oracle’s latest installers are no improvement, so I recently found myself back in this swamp, thrashing around for several days …
My scenario was SQL Server 2016 RTM. 32-bit Oracle 12c Open Client + ODAC was eventually working fine for Visual Studio Report Designer and Integration Services designer, and also SSIS packages run through SQL Server Agent (with 32-bit option). 64-bit was working fine for Report Portal when defining and Testing an Data Source, but running the reports always gave the dreaded «ORA-12154» error.
My final solution was to switch to an EZCONNECT connection string — this avoids the TNSNAMES mess altogether. Here’s a link to a detailed description, but it’s basically just: host:port/sid
http://www.oracledistilled.com/oracle-database/oracle-net/using-easy-connect-ezconnect-naming-method-to-connect-to-oracle-databases/
In case it helps anyone in the future (or I get stuck on this again), here are my Oracle install steps (the full horror):
Install Oracle drivers: Oracle Client 12c (32-bit) plus ODAC.
a. Download and unzip the following files from http://www.oracle.com/technetwork/database/enterprise-edition/downloads/database12c-win64-download-2297732.html and http://www.oracle.com/technetwork/database/windows/downloads/utilsoft-087491.html ):
i. winnt_12102_client32.zip
ii. ODAC112040Xcopy_32bit.zip
b. Run winnt_12102_client32client32setup.exe. For the Installation Type, choose Admin. For the installation location enter C:OracleOracle12. Accept other defaults.
c. Start a Command Prompt “As Administrator” and change directory (cd) to your ODAC112040Xcopy_32bit folder.
d. Enter the command: install.bat all C:OracleOracle12 odac
e. Copy the tnsnames.ora file from another machine to these folders: *
i. C:OracleOracle12networkadmin *
ii. C:OracleOracle12product12.1.0client_1networkadmin *
Install Oracle Client 12c (x64) plus ODAC
a. Download and unzip the following files from http://www.oracle.com/technetwork/database/enterprise-edition/downloads/database12c-win64-download-2297732.html and http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html ):
i. winx64_12102_client.zip
ii. ODAC121024Xcopy_x64.zip
b. Run winx64_12102_clientclientsetup.exe. For the Installation Type, choose Admin. For the installation location enter C:OracleOracle12_x64. Accept other defaults.
c. Start a Command Prompt “As Administrator” and change directory (cd) to the C:SoftwareOracle ClientODAC121024Xcopy_x64 folder.
d. Enter the command: install.bat all C:OracleOracle12_x64 odac
e. Copy the tnsnames.ora file from another machine to these folders: *
i. C:OracleOracle12_x64networkadmin *
ii. C:OracleOracle12_x64product12.1.0client_1networkadmin *
* If you are going with the EZCONNECT method, then these steps are not required.
The ODAC installs are tricky and obscure — thanks to Dan English who gave me the method (detailed above) for that.
First published on MSDN on Jun 30, 2010
This is one of the most common errors while creating linked server to Oracle database. Today I will discuss the reason for this error and possible resolutions.
Full error message:
OLE DB provider «MSDAORA» for linked server «LINKED_ORA» returned message «ORA-12154: TNS:could not resolve the connect identifier specified».
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider «MSDAORA» for linked server «LINKED_ORA».
First of all make sure you have reviewed the following Microsoft KB article that has a lot of good information on troubleshooting Oracle linked server issues.
How to set up and troubleshoot a linked server to an Oracle database in SQL Server
http://support.microsoft.com/kb/280106
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
http://ora-12154.ora-code.com/
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.
TNS entry for the Oracle database
===========================
OracleDB_Dev =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = server01.mydomain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = OracleDB)
(SERVER = DEDICATED)
)
)
In the above tnsnames.ora file Alias = OracleDB_Dev
Service Name: OracleDB (Actual Oracle service name [instance name in SQL])
2. Check the sqlnet.ora file under ‘Admin’ folder in Oracle home [Dir:appproduct11.1.0client_1networkadmin] and ensure that we have TNSNames in NAMES.DIRECTORY_PATH
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
3. Verify if you can connect to Oracle from the SQL server machine using tools installed with Oracle Client [For example «SQL Developer» or “SQL Plus”] with the same user id/password or TNS alias.
5. Check if the environment variable ‘PATH’ has the path for tnsnames.ora file specified.
Sample Value of Environment Variable PATH:
E:appproduct11.1.0client_1bin
;C:Program FilesBusiness ObjectsCommon3.5binNOTES;C:Program FilesBusiness ObjectsCommon3.5binNOTESDATA;%Systemroot%Microsoft.NETFrameworkv1.1.4322;%SystemRoot%system32;%SystemRoot%;%SystemRoot%System32Wbem;C:Program FilesDellSysMgtomabin;C:Program FilesMicrosoft SQL Server80ToolsBINN;C:Program FilesCommon FilesMicrosoft Sharedweb server extensions60TEMPLATEADMIN1033;C:Program FilesMicrosoft SQL Server80ToolsBinn;C:Program FilesMicrosoft SQL Server90DTSBinn;C:Program FilesMicrosoft SQL Server90Toolsbinn;C:Program FilesMicrosoft SQL Server90ToolsBinnVSShellCommon7IDE;C:Program FilesMicrosoft Visual Studio 8Common7IDEPrivateAssemblies;C:Program FilesMicrosoft Network Monitor 3
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_MACHINESOFTWAREORACLEKEY_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_MACHINESOFTWAREORACLE. If it exists then make sure it has the right value as “Dir:appproduct11.1.0client_1networkadmin”. 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:appproduct11.1.0client_1networkadmin”
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_MACHINESOFTWAREORACLEALL_HOMESHOME_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_MACHINESOFTWAREMicrosoftMSSQLServerProvidersMSDAORA
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
http://msdn.microsoft.com/en-us/library/e38h511e(VS.71).aspx
13. Try to specify all the information in the data source instead of using the TNS alias to connect to the Oracle database (this is a way to bypass tnsnames.ora file when connecting to Oracle).
Sample Data Source:
Data Source=(DESCRIPTION=(CID=GTU_APP)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST= server01.mydomain.com)(PORT=1521)))(CONNECT_DATA=(SID=OracleDB)(SERVER=DEDICATED)));
Author : Mohammad(MSFT) SQL Developer Engineer, Microsoft
Reviewed by : Azim(MSFT), SQL Developer Technical Lead , Microsoft
If you are familiar with database links on Oracle database, you may have already encountered the error “ORA-12154: TNS:could not resolve the connect identifier specified“. That is a common error which can be caused by a lot of factors. Today, I’m going to focus on the importance of environment variables to avoid this type of error message (particularly the “TNS_ADMIN” environment variable).
Context
For the rest of this post, I will work on a schema called “ARO” on “DBL121” database (Oracle 12.1.0.2). The server called “mylab01” runs on Linux.
My database link “MY_DB_LINK” has been created as follows:
mylab01> . oraenv ORACLE_SID = [DBL121] ? DBL121 [...] mylab01> sqlplus aro [...] SQL> CREATE DATABASE LINK my_db_link USING 'DBL122'; Database link created.
The database link points to “DBL122” database on server “mylab02” (same Oracle & OS version). The “DBL122” database also holds a schema “ARO” with the same password, that’s why I did not mentioned “CONNECT TO **** IDENTIFIED BY ****” during the database link creation.
Because I only specify “USING ‘TARGET_DB’” as connect string, it will use “local naming” method in order to resolve the net service name “DBL122” (that is, it will use the information store in a tnsnames.ora file).
My tnsnames.ora file located in “$ORACLE_HOME/network/admin” on “mylab01” contains the following information:
DBL122 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = mylab02 )(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DBL122)
)
)
My Oracle environment variables on “mylab01” are:
ORACLE_SID=DBL121 ORACLE_BASE=/app/mylab01/oracle ORACLE_HOME=/app/mylab01/oracle/product/12.1.0 TNS_ADMIN=/app/mylab01/oracle/product/12.1.0/network/admin
I will only work on “mylab01” for the rest of this post!
Basic tests
Before testing the new database link, we can make some basic tests.
- Server “mylab02” is reachable from “mylab01” on port 1521
- Database “DBL122” is up and running on “mylab02”. The listener is ready to accept new connections.
- I can connect to the remote database “DBL122” using the net service name “DBL122” with “ARO” user:
mylab01> sqlplus aro@DBL122
[...]
SQL> select sys_context('userenv','db_name') from dual;
SYS_CONTEXT('USERENV','DB_NAME')
--------------------------------------------------------------------------------
DBL122
At this point, everything seems to be fine!
Database link tests
From there, things will start to get complicated…
Local connection
For my first test, I will use a local connection (Bequeath NT Protocol), that is, I will not use Oracle Net to connect to my database.
(Be careful: if the “TWO_TASK” environnement variable is set in your environnement, you will not get a local connection but a connection through Oracle Net!)
mylab01> . oraenv
ORACLE_SID = [DBL121] ? DBL121
[...]
mylab01> sqlplus aro
[...]
SQL> select count(*) from TABLE1@my_db_link;
COUNT(*)
----------
4
It works!
Connection through listener
Now, I will try to connect to the same database, same schema and execute the same query but using Oracle Net (if your software that connects to the database resides on another host, this is the method that will be used to connect to the database).
mylab01> . oraenv
ORACLE_SID = [DBL121] ? DBL121
[...]
mylab01> sqlplus aro@DBL121
[...]
SQL> select count(*) from TABLE1@my_db_link;
select count(*) from TABLE1@my_db_link
*
ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified
Very confusing…
What’s going on?
Warning: I did not find anything in Oracle documentation about that point so it will only be base on my personal findings.
Oracle will not deal with database links in the same way depending on the type of connection you have established with the database. Especially for net service name resolution.
If you connect locally (using Bequeath protocol) to your database, Oracle will resolve the net service name of your database link using your current value for “TNS_ADMIN” environment variable.
If you connect through a listener (using Oracle Net) to your database, Oracle will resolve the net service name of your database link using the value of “TNS_ADMIN” which was set on your server hosting the database at the time where your database has been started.
That’s a very important point, because when you startup a database, you always check your “ORACLE_HOME” and “ORACLE_SID” environment variables but rarely “TNS_ADMIN” (personally, I never checked before encountering this problem).
How to check the environment variables of your database processes
Now, you might want to check which environment variables were set when you started your database. There is two ways to achieve that.
The “system way”
I called this the “system way” because you will not use any Oracle utility using this method. The command depend of your OS.
For each platform, the first step consist to identify the PID of the “smon” process of your database instance. There are many ways, here is the one I use:
mylab01> ps -ef|grep smon oracle 8258 1 0 Dec04 ? 00:00:00 ora_smon_DBL121
Then, choose the command that match your operating system.
Linux
mylab01> strings /proc/8258/environ|grep TNS_ADMIN
(In my case, the command returned “TNS_ADMIN=/tmp” because I set up the value for testing purpose before starting my “DBL121” database).
AIX
myaix01> ps eww 8258| tr ' ' 'n' | grep TNS_ADMIN
Solaris
mysol01> pargs -e 8258
HP-UX
Unfortunately, it seems there is no simple way to find process environ on HP-UX using system commands.
The “Oracle way”
If you prefer to use Oracle utility, you can get the same information using… lsnrctl!
You need do adapt the command to your environment. In my case, the database “DBL121” is registered on a listener called “LISTENER”.
mylab01> lsnrctl LSNRCTL> set current_listener LISTENER Current Listener is LISTENER LSNRCTL> set displaymode verbose Service display mode is VERBOSE LSNRCTL> services
Next, you need to identify the service of your database and look at the line containing “ENVS =“. You will find the value of the variable “TNS_ADMIN” which was defined when starting the database.
The advantage of this method is that it also works on HP-UX.
How to correct the problem
If you are in the same situation I described, you have two ways to correct the problem.
If you are allowed to, restart the database instance after checking that your environment variable “TNS_ADMIN” is set correctly.
If you are not allowed to restart the database instance, you can fix the problem by creating a symbolic link to the “good” tnsnames.ora file (or create a new one with the proper informations) in the path identified in the previous step (“/tmp” in my example).
mylab01> ln -s $ORACLE_HOME/network/admin/tnsnames.ora /tmp/tnsnames.ora
Control:
mylab01> sqlplus aro@DBL121
[...]
SQL> select count(*) from TABLE1@my_db_link;
COUNT(*)
----------
4
Perfect 🙂
Stay tuned for more DBA stuff!