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


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:\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_ADMINat 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

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

Comments (30)

  1. Ramanan says:

    how to give network path while creating oracle directory.

  2. Marcos says:

    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.

  3. punja says:

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

  4. Akshatha says:

    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.

  5. Yogayndra says:

    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)));

  6. Gr says:

    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?

  7. Ravi says:

    Very helpful article. I was struggling to establish connection on my new installation and Step 7 fixed it for me.

  8. RobX says:

    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?

  9. Dinesh says:

    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

  10. Mathew says:

    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

  11. mohammad imran rizvi says:

    I am able to connect by specifyingl the information in the data source instead of using the TNS alias.

    This works great.

  12. #13 Was the Ticket says:

    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.

  13. Joshua says:

    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…

  14. Rajan says:

    Thanks for the detailed explanation. It real works.

  15. Shar Websurfer says:

    I had this error in the past and I solved through turfybot.online.fr/oracle/11g/errors/ORA-12154.html

  16. Bob says:

    Also check your DNS settings on the network adapter. Make sure you can ping any server names short or long.

  17. noziony says:

    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.

  18. ehsansahil says:

    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…

  19. rahul says:

    ORA-12154: TNS:could not resolve the connect identifier specified.

    solution:

    http://www.youtube.com/watch

  20. rahul says:

    hope this solves your problem

    http://www.youtube.com/watch

  21. Rahul says:

    seriously, i need help.. the problem is not solved yet.

    i have tried the instructions given by

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

  22. Umar says:

    I was able to resolve the issue.

    Thanks for the article TNS_ADMIN

  23. Jamil says:

    Excellent and extremilly important
    thank very

  24. Tom says:

    There are many huge, glaring ambiguities and deficiencies throughout this post. Here is my attempt to clarify them:

    1. “Alias” is referred to as “Connection Name” in Oracle SQL Developer. I’ve never seen it called “Alias” in any Oracle documentation and this could throw someone off.
    2. If someone uses a Service Name instead of a SID when they create their database, it will not have a folder in the Oracle Home directory — it will simply be where the path is “Dir:\app\product\11.1.0\network\admin” where tnsnames.ora should go, and so “client_1” is left out. This was left out.
    3. Being able to connect using SQL Plus isn’t a guarantee Oracle SQL Developer will work – in fact, I ran into that very case where SQL Developer would not connect, while SQL Plus would. This article doesn’t explain why that might be the case.
    4. This was removed – there’s no #4.
    5. Explain that the path someone needs to make sure is present is the Oracle Home directory\SID\bin, or Oracle Home directoy\bin if they are using a Service Name instead of a SID.
    6. Explain that the Oracle Home path someone needs to make sure is present is like Dir:\oracle\product\11.
    7. Explain that the path will not include “client_1” if the database has a Service Name instead of a SID.
    8. How is the SQL Server startup account found? Fortunately for me, all Authenticated Users have Read & Execute rights on the Oracle Home directory, so it was a non-issue, but this response was extremely vague on how to determine this for the amateur developer/DBA. Where is the process monitor log? Again, extremely vague. I found a log at Oracle_Home\sqldeveloper\sqldevloper\bin\sqlnet.log, but it shows a different connection string than I was attempting to use (one to the localhost), so I’m not even sure it’s the correct one.
    9. Also indicate that other Oracle Home directories should probably be removed/Oracle client instances uninstalled using the Oracle installer program to remove them.
    10. I did not have this registry path – what then?
    11. What would connecting to a SQL instance and (attempting to connect to an) Oracle instance at the same time prove? Network connectivity? This check can be accomplished simply by going to the internet and being able to pull up this page.
    12. Explain they need to select the Oracle DB Provider driver when opening the UDL file and use the same *Connection Name*, not “TNS” name, as in their tnsnames.ora file for the Data Source. This didn’t help me connect using SQL Developer, though – I already could connect with SQL Plus, so I knew a UDL connection would succeed. And as an aside, if you try to use the resulting connection string from the UDL file in a .NET application, it will give you an exception that “Keyword ‘Provider’ is not supported” when using an OracleConnection.
    13. No complaints.

  25. kenneth says:

    i am trying to link the oracle forms to the oracle database on the same machine

  26. Shakthi says:

    Very helpful article

  27. Islam Eltally says:

    Thanks very much, It is very helpful

  28. Otis says:

    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.sql
    Omit from your db password selected charset and it should be fine.

    Cheers!

Skip to main content