Start Free Trial

asked on

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

I need to be able to connect to an Oracle 12.2 database using VB.Net 2017.

I installed ODAC122010_x64 client software and ODTwithODAC122010 to a new C:\Oracle folder.

I defined a Data Source in the TNSNAMES.ORA:
WLCD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xs-bhm-dbd-1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = WLCD)
)
)

I added an Environmental Variable to the server I am running the code from;
%TNS_ADMIN% = C:\oracle\product\12.2.0\client_1\Network\Admin

I added "C:\oracle\product\12.2.0\client_1;C:\oracle\product\12.2.0\client_1\Network\Admin" to the Path variable

I am able to create a connection in the Server Explorer in VS2017 and test the connection just fine. However, when I try to connect using code, I get "ORA-12154: TNS:could not resolve the connect identifier specified".

Dim wcConn = New OracleConnection
wcConn.ConnectionString = "DATA SOURCE=xs-bhm-dbd-1:1521/WLCD;PERSIST SECURITY INFO=True;USER ID=xxxxxxxx; Password=xxxxxxx"
wcConn.Open()

<<I get the "ORA-12154: TNS:could not resolve the connect identifier specified" at the wcConn.Open>>

I have researched all kinds of websites and watched videos to find why this is a problem.

I followed these instructions on how to troubleshoot this error:
1. Create the environment variable: ORACLE_HOME via: control panel > system > advanced > environment variables and then DBArtisan knew where to find tnsnames.ora.
<<Created new variable ORACLE_HOME and set to: C:\oracle\product\12.2.0\client_1>>

2. Make sure that "TNSNAMES" is listed as one of the values of the NAMES.DIRECTORY_PATH parameter in the Oracle Net profile (SQLNET.ORA)
<<Here is my SQLNET.ORA file:>>
SQLNET.AUTHENTICATION_SERVICES= (ALL)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

3. Verify that a TNSNAMES.ORA file exists and is in the proper directory and is accessible.
<<I did this but not sure what else to do to make it more accessible>>

4. Check that the net service name used as the connect identifier exists in the TNSNAMES.ORA file.
<<I know the TNSNAMES.ORA is setup correct because I can connect successfully in the Server Explorer and SQLPLUS>>

5. Make sure there are no syntax errors anywhere in the TNSNAMES.ORA file. Look for unmatched parentheses or stray characters. Errors in a TNSNAMES.ORA file may make it unusable.
<<Ditto #4>>

6. Change the Oracle connection from "TNSNAME Mode" to "Standard mode".
<<not sure how to do this in code>>
7. "Allow inprocess" has to be checked in the provider options.
<<not sure how to do this in code>>

Any help would be VERY helpful. Been working on this most of the week.

Member_2_8054273

does your env match the service user for this?

ASKER

Which service? I only have the oracle client on this server and I don't see any oracle services.

You are using an EZConnect string in your application: server:port/instance. This does not use tnsnames.ora. The TNS string would be just WLCD as DATA SOURCE. You can try if just using that works.

ASKER

Qlemo, thank you! I tried that and I get another error but that may lead you to another answer.

wcConn.ConnectionString = "DATA SOURCE=WLCD;PERSIST SECURITY INFO=True;USER ID=xxxxx;Password=xxxxx"

The error I get is "ORA-12545: Network Transport: Unable to resolve connect hostname" so it must not be finding the TNSNAMES.ORA file.

ASKER

I didn't mention that I am using Managed Data Access

ASKER

Could it have something to do with the Oracle.key file and the entry: SOFTWARE\ORACLE\KEY_OraClient12Home1

slightwv (䄆 Netminder)

Go back to using the EZConnect method. Forget about TNS_ADMIN and the tnsnames.ora file.

If you are going to be using .Net and the Managed Client, EZConnect is the simplest way to go.

Make sure everything on the database server matches.

Log into xs-bhm-dbd-1 and run: lsnrctl status

Make sure the listener is using port 1521 and is listening for an instance named WLCD.

Do you have any other Oracle products installed on your machine?
You might also want to make sure your .Net app is compiling for 64Bit only and not any cpu since you downloaded the 64Bit ODAC.

ASKER

slightwv, Thanks! I changed it back to EZCONNECT. Now I am back to ORA-12154: TNS: could not resolve the connect identifier specified.

Could this be the schema not being specified? If so, how do I specify the schema in the EZCONNECT data source?

I had the DB manager confirm that the listener is working and remember that I can connect just fine using the Server Explorer and can view all the objects in the DB.

I do not have any other Oracle products on this server. It was just created last week for me to use for developing this application.

slightwv (䄆 Netminder)

>> If so, how do I specify the schema in the EZCONNECT data source?

You don't. That is the job of the username and password you provide on the connect string.

I'm not on a machine where I have IIS set up to provide a working example.

Take a look here to see if you might be missing something:
http://www.oracle.com/technetwork/issue-archive/2014/14-mar/o24odp-2147205.html

ASKER

slightwv, Looks like a very complete article. Working my way through it. Hopefully I can find the problem. Can't believe it is this hard! :(

slightwv (䄆 Netminder)

Nuclear Fission is hard until you do it. I know you don't believe me but it it is pretty straight forward once you understand all the moving parts.

I don't find it difficult but I've been using Oracle for about 25 years and Classic ASP/.Net for about 16.

99% of the time I copy Oracle.ManagedDataAccess.dll into the bin folder of my websites or into the base folder for my console apps. I rarely even ran the ODAC installer and almost never add the entries in the web.config/app.config specific to the driver.

ASKER

What I don't understand is why I can connect immediately from the VS 2017 Server Explorer and test the connection successfully but cannot connect using the same EZCONNECT in code. It has to be something simple that is not set.

slightwv (䄆 Netminder)

I'm not familiar with Server Explorer so cannot say. Is the explorer using the ODP.Net managed client or some other driver?

When I use ODP.Net, I don't set anything so unless there is some mis-match of things pointing to incorrect pieces, I'm not sure what it might be.

I would expect a different error but did you confirm you are compiling for 64Bit cpu and not anycpu?

I'll be able to access my old .Net code tomorrow. I'll see if I can find a simple test app I can post for testing.

ASKER

Server Explorer is in Visual Studio. See attached picture. I can connect with Add Connection either by TNS or by EZCONNECT. However, I cannot connect using code using TNS or EZCONNECT.
Server-Explorer-Add-Connection.png

ASKER

I checked and it is setup for Any CPU. When I changed it to 64 bit, I got hundreds of warnings but no errors. Could that be it?

Member_2_8054273

could be you have 64bit conflict with server or client

slightwv (䄆 Netminder)

>>When I changed it to 64 bit, I got hundreds of warnings but no errors. Could that be it?

You downloaded the 64Bit Managed Client. That isn't accessible with 32Bit apps. The Bit version of the driver needs to match the bit version of the app. You cannot mix and match.

>>I can connect with Add Connection either by TNS or by EZCONNECT.

That screenshot shows localhost not xs-bhm-dbd-1 and PDBORCL not WLCD. Those are pretty big differences.

Since you are using localhost and say it connects means there is a database running on the same machine. That means you have additional Oracle products installed which conflicts with a statement you made earlier.

ASKER

The screenshot was just an example i found. I will get a screenshot of the real thing.

ASKER

Here are screenshots of the real connections. Both TNS and EZConnect using Server Explorer. Sorry for the confusion. I will change to 64 bit and see what happens.
TNS.jpg
EZConnect.jpg

ASKER

I changed over to 64 bit using the Configuration Manager and I am getting an error with my oledb driver (I am access both an Access DB and Oracle with this app) which must be 32 bit. It looks like I need to upgrade the oledb driver or downgrade to 32 bit.

slightwv (䄆 Netminder)

So this isn't a stand-alone .Net app connecting to Oracle?

If your .Net code needs to connect to Access and Access 32Bit, then I'm leaning towards you needing the 32Bit Managed Client.

ASKER

slightwv,

That is what I thought I would try! I will let you know on progress.

ASKER

Because we have installed and uninstalled the ODP.NET so many times, we are going to re-create the virtual server and work very hard to get a clean install with 32 bit drivers only. Here is the order of install:

1. Install Visual Studio 2017
2. Install the 32 bit ODAC 12.2c Release 1 and Oracle Developer Tools for Visual Studio.
- I will uncheck ASP.NET, Oracle Transaction Service,
- I will add our default TNS setting during the install
3. Check to be sure the Path environment has the Oracle home path added
4. Add our ODBC DSNs for Access to attach to Oracle
5. Install Office 2016

Anything else suggested

It does not make sense to add a folder containing no executables into PATH. If anything, add the oracle\bin to PATH.

slightwv (䄆 Netminder)

If you plan on using the ODAC only with only using ODBC and ODP.Net Managed Driver you shouldn't need to change PATH at all.

ASKER

Got a new server and a clean install of VS 2017. Installed the 32 bit (only) ODP.Net that I circled in the attached image. Now ODP.NET does not show up as an option when I try to create a connection in Server Manager nor does it connect in code. No joy!!
Oracle-32-Bit-Client.jpg

ASKER

We installed Runtime option. See attached.
Installer.jpg

slightwv (䄆 Netminder)

The image you posted is the full Oracle client.

It has bee a LONG TIME since I installed it but I remember a default install of that doesn't install ODP.Net and ODBC drivers by default. You need to manually select it.

Anyway, for the Managed Client and EZConnect what you downloaded is overkill.

You probably wanted VStools with ODAC.

They recently changed their download layout for ODAC:
http://www.oracle.com/technetwork/topics/dotnet/downloads/net-downloads-160392.html

You can try the tools if you want. I'm not a Visual Studio person so cannot say for sure.

I pretty much always downloaded the XCopy versions. Those appear to now be under: ODAC Runtimes in the link above.

Then at the bottom of the page is ODAC XCopy. The Managed Client is one of the options. The bad part is the XCopy doesn't show ODBC as being included.

The ODAC OUI doesn't show 32Bit versions.

So, I might try:
http://www.oracle.com/technetwork/topics/dotnet/downloads/odacdev-4242174.html

ASKER

Slightwv,

I looked at the VS Tools and they did not offer a 32 bit or 64 bit option so I was hesitant to consider that option.

I will look at the Xcopy download.

Thanks for sticking with me BTW!!

Bob

slightwv (䄆 Netminder)

No problem. I'm with you until you get it working! Oracle really isn't that hard...

slightwv (䄆 Netminder)

I had an idea and was going to try some more direct help and am going to download it myself to see if what I had in mind would be a quicker way to assist.

I started here:
http://www.oracle.com/technetwork/topics/dotnet/downloads/odacdeploy-4242173.html

and actually read the fine print:

which can be configured to install ODAC software silently. 32-bit ODAC OUI can be downloaded from this ODAC developer download page.

32Bit has it's own page.

The old ODAC download page was a LOT less confusing...

slightwv (䄆 Netminder)

I don't really have access to a machine with IIS running but I remembered PowerShell can run .Net code.

My setup:
I created c:\ee for this question

From the link above I downloaded the 32Bit
Runtime ODAC Xcopy

Managed ODP.Net 12.2: ODP.NET_Managed_ODAC122cR1.zip

From the normal site: 32Bit Instant Client basic and ODBC:
http://www.oracle.com/technetwork/topics/winsoft-085727.html

instantclient-basic-nt-12.2.0.1.0.zip
instantclient-odbc-nt-12.2.0.1.0-2.zip

unzip both into same folder. Run ODBC_INSTALL.exe.

I can now see The Oracle Instant Client in my 32Bit ODBC admin tool. I didn't test the 32Bit ODBC connectivity.

ODP.Net managed client: Unzip to some folder. There is a readme.htm that tells you how and what to install. I've never run it.

I copied the DLL from the common folder to where I need it:
copy C:\12_2_managed_client\odp.net\managed\common\Oracle.ManagedDataAccess.dll c:\ee

I created a PowerShell script (c:\ee\ODP_Net_test.ps1).

Note: I borrowed a lot of the PS/ODP from the link below

#mostly from: https://blogs.technet.microsoft.com/heyscriptingguy/2012/12/04/use-oracle-odp-net-and-powershell-to-simplify-data-access/

#Just for me since the default red SUCKS...
$host.PrivateData.ErrorForegroundColor='Green'

#Tell Windows this stuff is OK to run
Set-ExecutionPolicy RemoteSigned -Scope CurrentUser
Unblock-File -Path C:\ee\Oracle.ManagedDataAccess.dll
Add-Type -Path 'C:\ee\Oracle.ManagedDataAccess.dll'

$con = New-Object Oracle.ManagedDataAccess.Client.OracleConnection("User Id=scott;Password=tiger;Data Source=localhost:1521/ORCL")
$cmd=new-object Oracle.ManagedDataAccess.Client.OracleCommand
$cmd=$con.CreateCommand()
$cmd.CommandText="Select 'Hello world!' Greeting from dual"

$con.Open()
$str=$cmd.ExecuteScalar()
$str
$con.Close()

Open in new window

Open a 32Bit PowerShell window:
Windows PowerShell (x86)

From the powershell prompt:
cd c:\ee

Now you have two options and I suggest the second one for your first few times.

Option 1: Execute the script: .\ODP_Net_test.ps1

Option 2: Copy each command from notepad of other editor and paste the lines into the PowerShell window one at a time.

This way you know line by line if things work

Here is my run of option 2(there is a prompt for the policy change):

User generated image

ASKER

Slightwv,

I ran the TNSPING and seems to indicate that a connection can be made ok. Do you agree? See attached.

User generated image

That would eliminate all networking or firewall questions and narrow it down to my VS code or settings

slightwv (䄆 Netminder)

Overall: Yes, there probably isn't a network or firewall issue. I never thought it was. 99% of the time firewall issues generate a a different error message.

Your VS explorer connecting seemed to prove it wasn't a network issue. It even went a step further: It actually connected to the instance.

tnsping only asks the listener if it knows about the instance. The database can be down and tnsping can still return OK.

Can you repeat my PowerShell and ODP.Net Managed client test?

ASKER

Let me try

ASKER

It looks like it all worked except for the policy change:

User generated image

slightwv (䄆 Netminder)

As long as you see 'Hello world!', you used ODP.Net Managed Client to talk to your database.

So, if you cannot connect from you code, the problem sort of has be in it or how you've configured the app to locate the Managed driver.

For wep apps, I copied the DLL into the bin folder. For console apps I think it was in the same folder as the .exe.

ASKER

I think the problem is the code cannot find the Oracle_Home folder or something similar. I reference the .dll just fine. If I unreference it, it won't compile.

slightwv (䄆 Netminder)

>>the code cannot find the Oracle_Home folder or something similar

Remember, I never install anything for my .Net code using the Managed Client so I'm leaning towards it isn't because of ORACLE_HOME.

Could the original TNS error be coming from the ODBC pieces of the code and not the ODP pieces?

ASKER

Created a new VB.Net application with a single form. Here is all the code:

Imports Oracle.ManagedDataAccess.Client
Imports Oracle.ManagedDataAccess.Types

Public Class Form1

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim o As New OracleConnection
'o.ConnectionString = "Data source=xs-bhm-dbd-1:1521/WLCD;USER ID=wellcore; Password=wellcoretest"
o.ConnectionString = "Data source=WLCD;USER ID=wellcore; Password=wellcoretest"
Try
o.Open()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
End Class

I click the button and get the ORA-12154 error.

I had a idea wondering of my problem might be that I was developing my application on a mapped server drive. I moved the app to the local drive and the application worked. Hmmmmm!!!!!

All of this struggle because I was developing the application on a mapped drive. Any idea why this is the case?

I am glad to just figure this out and can move forward.

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Slightwv stayed with me through this whole issue and I thank him for that.