Share via

“ORA-12154: TNS: could not resolve the connect identifier specified” error while creating a linked server to Oracle

  • 06/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

https://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 https://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:\app\product\11.1.0\client_1\network\admin] 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:\app\product\11.1.0\client_1\bin;C:\Program Files\Business Objects\Common\3.5\bin\NOTES\;C:\Program Files\Business Objects\Common\3.5\bin\NOTES\DATA\;%Systemroot%\Microsoft.NET\Framework\v1.1.4322;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;C:\Program Files\Dell\SysMgt\oma\bin;C:\Program Files\Microsoft SQL Server\80\Tools\BINN;C:\Program Files\Common Files\Microsoft Shared\web server extensions\60\TEMPLATE\ADMIN\1033\;C:\Program Files\Microsoft SQL Server\80\Tools\Binn\;C:\Program Files\Microsoft SQL Server\90\DTS\Binn\;C:\Program Files\Microsoft SQL Server\90\Tools\binn\;C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\;C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\;C:\Program Files\Microsoft 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_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

https://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

Comments

  • Anonymous
    August 26, 2010
    how to give network path while creating oracle directory.

  • Anonymous
    April 24, 2012
    This could help http://ora-12154.ora-code.net/

  • Anonymous
    August 23, 2012
    Excellent article! Error started occurring out of a sudden. Worked on the issue on and off for four days. This article solved my issue and my code is working once more. Thank you very very much.

  • Anonymous
    August 26, 2012
    The following link has useful information, jgvimalan.wordpress.com/.../ora-12154-tnscould-not-resolve-the-connect-identifier-specified

  • Anonymous
    November 22, 2012
    www.dba-oracle.com/t_ora_12154_tns_resolve_service_name.htm check environment for this error ORA-12154: TNS:could not resolve the connect identifier specified TNS_ADMIN=C:oracleproduct10.2.0client_1NETWORKADMIN

  • Anonymous
    February 14, 2013
    If username/password@db is like - abc/welc@12@database. I am getting error. Since it is considering db name - 12@database. I tried giving password in double quotes. Still does't work. Please help.

  • Anonymous
    March 11, 2013
    Thanks the sample complete datasource was really helpful for connectivity. Data Source=(DESCRIPTION=(CID=GTU_APP)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST= server01.mydomain.com)(PORT=1521)))(CONNECT_DATA=(SID=OracleDB)(SERVER=DEDICATED)));

  • Anonymous
    October 04, 2013
    How Can Write Code in Access So That Link To Specified Table In ORacle Without Using ODBC, When Give A Message 'Need Admin Permission ' Can Someone Help Me?

  • Anonymous
    October 25, 2013
    Very helpful article. I was struggling to establish connection on my new installation and Step 7 fixed it for me.

  • Anonymous
    October 28, 2013
    Very helpful - Oracle 64-Bit on Windows 2008 64 bit The problem I have - the Oracle Net Manager list the 11.2.0Client_2NetowrkAdmin  and shows successful login under the test. But... The Oracle Installer was used twice as it failed because it was not installed with As Admin. It is one of the problems Oracle has. Was able to edit the TNS_Names and to get it working after a 2nd Oracle Client attempt was made. The Oracle De-Installer Won't work (another Oracle problem). My ODBC Create New Data Source shows OraClient11g_Home1 - but doesn't show OraClient11g_Home2.   How can I get out of this ORACLE LIMBO?

  • Anonymous
    February 19, 2014
    Hi, Thank you for the article. i was in trouble for around 4 days and was resolceed at last. Please make sure you add the Key TNS_ADMIN in the registry and create a enviroinment variable with name TNS_ADMIN and specify the correct path where the oracle is installed for Example:C:oracleproduct32bit11.2.0.3.0NETWORKADMIN. Thanks, Dinesh

  • Anonymous
    May 27, 2014
    Regedit->HKEY_LOCAL_MACHINE->Software->Oracle->RightClick NEW->StringValue and name it TNS_ADMIN and give the value  “X:appproduct11.1.0client_1networkadmin” This solved my issue. I was trying to connect using Entity framework with  LDAP instead of TNSNAMES

  • Anonymous
    July 22, 2014
    I am able to connect by specifyingl the information in the data source instead of using the TNS alias. This works great.

  • Anonymous
    March 04, 2015
    I read all the way down and when I got to #13 I knew that was the problem. Everything else had checked out. I remembered a few years ago when I was trying to create a database link with a locked down remote server and I had to do the same thing. Sometimes that is what Oracle needs and wants - the whole connection.

  • Anonymous
    March 05, 2015
    I went right to 13.  Worked beautifully... I wish ORACLE would get their act together on making integration with other systems easier.   I always groan out load (GOL?) whenever I have to deal with a new configuration/installation...

  • Anonymous
    March 23, 2015
    Thanks for the detailed explanation. It real works.

  • Anonymous
    April 04, 2015
    I had this error in the past and I solved through turfybot.online.fr/oracle/11g/errors/ORA-12154.html

  • Anonymous
    June 03, 2015
    The comment has been removed

  • Anonymous
    July 17, 2015
    Thank you very much. This helped a lot. It solved my problem that I could not use sqlplus connecting via TNSname. With your advics I found my problem was TNS_ADMIN and ORACLE_HOME in regedit.

  • Anonymous
    August 11, 2015
    Its a coman error... just foĺlow bellow steps Step1.c/oracle/product/10.2.0/db1/network/ADMIN here copy the (tnsnames.ora) And past on... Step2.C/DevSuiteHome_1/network/ADMIN Step3.c/DevSuiteHome_1/jinit Step4.instal the jinit Step5.reboot ur computer Lets enjoy...

  • Anonymous
    October 20, 2015
    ORA-12154: TNS:could not resolve the connect identifier specified. solution: www.youtube.com/watch

  • Anonymous
    October 22, 2015
    hope this solves your problem www.youtube.com/watch

  • Anonymous
    October 25, 2015
    seriously, i need help.. the problem is not solved yet. i have tried the instructions given by www.youtube.com/watch still, its not solved.. help me out error: ora-12154 tns could not resolve service name i am using windows 7 64 bit and oracle 11g

  • Anonymous
    November 18, 2015
    I was able to resolve the issue. Thanks for the article TNS_ADMIN

  • Anonymous
    March 09, 2016
    HI Author,I was able to resolve the issue by using TNS_ADMIN .Thanks for the article .Very much appreciated .Thanks again

  • Anonymous
    March 17, 2016
    thank you. it's helpful :)

  • Anonymous
    April 10, 2016
    Excellent and extremilly importantthank very

  • Anonymous
    June 08, 2016
    The comment has been removed

  • Anonymous
    June 22, 2016
    i am trying to link the oracle forms to the oracle database on the same machine

  • Anonymous
    June 27, 2016
    Very helpful article

  • Anonymous
    October 25, 2016
    Thanks very much, It is very helpful

  • Anonymous
    November 20, 2016
    The simplest resolution for this issue without spending hours troubleshooting a somewhat non-issue is to change the account password to eliminate @. The error occurs at least in part because the @ in the password is treated as the connect service designator.in the example: "connect abc/welc@12@database"SqlPlus attempts to connect as user "abc" with pass "welc" to service name @12 and run the script @database.sqlOmit from your db password selected charset and it should be fine.Cheers!

  • Anonymous
    March 07, 2017
    ora-12154 tns could not resolve the connect identifierif you are still facing this error please go through this !!!!same error we will get if we don't follow the oracle password policiescheck the below link for password setup rules for oracle https://docs.oracle.com/cd/B28359_01/network.111/b28531/guidelines.htm#CHDEGEIFI also had this issue, and the root cause was, my password has '@' character. I just enclosed the password in double quotes and it worked. e.g. connect system/"p@sswor

  • Anonymous
    June 19, 2017
    very detailed explanation.But on top of all make sure that, there is not space before Alias = OracleDB_Dev.OracleDB_Dev= (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = server01.mydomain.com)(PORT = 1526)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = OracleDB) ) )