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

Question

I am trying to use the Oracle database in SSRS, I installed and configured the Oracle Client Data Access as explained at SSRS: OLE DB Oracle provider is not registered on the local machine. but when I clicked on the Test connection or Run the report for preview, I got this error ORA-12154: TNS:could not resolve the connect identifier specified.

Unable to connect to Data source
ORA-12154: TNS:could not resolve the connect identifier specified.

How can I fix this error to connect to oracle database using SSRS Report Builder?

Answer 1

This error "ORA-12154: TNS: could not resolve the connect identifier specified" usually occurs because of the tnsnames.ora file is not configured properly.

How to connect to oracle database using SSRS Report Builder?

1. To be able to connect to the Oracle database, you should first make sure that the Oracle Data Access for Oracle Client has been installed successfully in your local machine as mentioned at How to install Oracle Client Data Access for Microsoft Report Builder?
2. After Installing the Oracle Data Access for Oracle Client, you have to configure tnsnames.ora file to create Oracle net service names, or aliases, for each database server you need to connect to.

How to configure tnsnames.ora file for SSRS?

1. Based on your ODAC installation, locate this folder F:\app\melqassas\product\11.2.0\client_1\Network\Admin\

   To get your Oracle Client installation path location, please check How to find the installation location for ODAC?.

2. At the ADMIN folder, Check if the tnsnames.ora file exists or not, if it does not exist, take a copy from tnsnames.ora in the SAMPLE folder at this location F:\app\melqassas\product\11.2.0\client_1\Network\Admin\Sample then paste it at ADMIN folder.
3. Open the tnsnames.ora file using NotePad to edit it.

4. Copy and paste the below connection string to your "tnsnames.ora" file.

    Alias=
      (DESCRIPTION =
         (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCP)(HOST = ServerIP) (PORT = 1521))
        )
         (CONNECT_DATA =
         (SERVICE_NAME =ServiceName)
       )
     )
   

5. At the above snippet, Set the Alias name to the server hostname or any meaning name as you prefer.
6. Set the HOST parameter with your Oracle DB Server IP or Oracle Server HostName.

   If you set the Host value to the server hostname instead of the Server IP, so in this case, you should add a local DNS entry to the hosts file to let the Server IP point to the Server hostname. For more details, please check How to configure Hosts File?

7. Set the SERVICE_NAME as it's configured in the Oracle Server.

   To know the Oracle Service Name, Check to Get Oracle Service Name in Oracle Server.

8. Save the tnsnames.ora file.
   

   In some cases, you may find the (SERVER = DEDICATED) added to your connection setting in the tnsnames.ora file that mainly raises this error "ORA-12154: TNS: could not resolve the connect identifier specified". In this case, you have to remove this line (SERVER = DEDICATED) and make sure that you have set the SERVICE_NAME correctly as mentioned at Get Oracle Service Name in Oracle Server.

9. Close the Report Builder and reopen it again.
10. Create a new data source with Oracle Provider for OLE DB.
11. Set the Server Name to the Alias Name specified in the "tnsnames.ora" file as mentioned in Step 5.
    
12. Test the connection that should be working properly now.

You might also like to read How to Connect to Oracle DB from Visual Studio?