Connection string in ODP.Net

Question

Initially I was using oraoledb.oracle provider in order to connect to Oracle database and it was easy to build a connection string:

Provider=OraOLEDB.Oracle;User Id=myId;Password=myPassword;Data Source=data.customer.com

and everything works as it expected, but currently I switched to ODP.Net in order to get rid of installing oracle client and I get an error ORA-12154: TNS:could not resolve the connect identifier specified with the following connection string:

Data Source=data.customer.com;User Id=myId;Password=myPassword

So could someone tell me, where I made a mistake?

And `data.customer.com` is in the tnsnames.ora for the specific home you are running through (check the bits)? — Patrick Hofman, Dec 10 '14 at 12:40
Also, are you using the managed ODP.NET or the unmanaged version? — Patrick Hofman, Dec 10 '14 at 12:41
@Patrick Hofman, yep, I'm using managed ODP.NET and unfirtunately I couldn't check tnsnames.ora file, because I have no access to that server. All I know is this 'data.customer.com' — ChernikovP, Dec 10 '14 at 13:24
Managed ODP.NET doesn't work with tnsnames.ora. That's the problem. — Patrick Hofman, Dec 10 '14 at 13:25
@Patrick Hofman, and unmanaged ODP.NET requires Oracle client to be installed, am I right? — ChernikovP, Dec 10 '14 at 13:34
True. That one does support tnsnames.ora. But it is a pain to get it working. — Patrick Hofman, Dec 10 '14 at 13:34
@Patrick Hofman, thanks a lot for your help! I guess, it's time to speak with customers=) — ChernikovP, Dec 10 '14 at 13:35
No problem. Added it as answer as comments might be deleted over time. — Patrick Hofman, Dec 10 '14 at 13:37
Correction to the above: ODP.NET, Managed Driver DOES support TNSNAMES.ORA. See the discussion in the answers below. — Christian Shay, Dec 12 '14 at 20:49
https://www.connectionstrings.com/oracle is a good reference. — vapcguy, Jun 22 '16 at 19:27

score 4 · Answer 1 · edited May 23 '17 at 12:08

4

Personally, I prefer not to depend on defined tnsnames.ora on a machine. Using longer connection string(see example below) you could deploy your program not thinking about tnsnames.ora that may not exist on a target system.

Example of using ODP.NET without tnsnames.ora:

Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MyOracleSID)));
User Id=myUsername;Password=myPassword;

See also.

edited May 23 '17 at 12:08

Community

1
1

answered Dec 10 '14 at 12:47

alekseevi15

1,732
2
16
20

1

Using EZ config format is simpler than that if you would prefer to embed all the details in the connect string. For example: Data Source=MyHost:MyPort/MyOracleService;User Id=myUsername;Password=myPassword; – Christian Shay Dec 11 '14 at 01:24

Patrick Hofman · Accepted Answer · 2014-12-12T21:09:36.920

3

Managed ODP.NET works with tnsnames.ora, but it needs some additional configuration to work. That's your problem. You are trying to use a name from your tnsnames.ora which only works when setting the TNS_ADMIN environment variable or config section in your machine.config, Web.config, or app . config. Another option to be able to use the tnsnames.ora is to the unmanaged ODP.NET driver, which is a pain to use in my opinion.

Try to use EZCONNECT names, which are supported by both the managed and unmanaged driver without the need for a tnsnames.ora file or any other configuration.

edited Dec 12 '14 at 21:09

answered Dec 10 '14 at 13:37

Patrick Hofman

153,850
22
249
325

1

It is NOT true that ODP.NET, Managed Driver does not work with TNSNAMES.ORA. It does, but it is picked up from the TNS_ADMIN variable which is set by default in the machine.config by the installer (or you can set it in your app or web config). You can also put the TNSANAMES.ORA in the working directory of the executable, and if you have not set TNS entires in your config file already, it will be picked up. – Christian Shay Dec 11 '14 at 01:21
@Chr: you are contradicting your own answer : http://stackoverflow.com/a/26672511/993547 – Patrick Hofman Dec 11 '14 at 08:01
That post you reference was a question about supporting the IFILE parameter inside of TNSNAMES.ORA. It was not saying that we do not support TNSNAMES.ORA. It said that we don't support IFILE inside of TNSNAMES.ORA when using ODP.NET, Managed Driver. – Christian Shay Dec 12 '14 at 07:17
@Chr: agree. Can you check if the revised post is accurate? – Patrick Hofman Dec 12 '14 at 07:20
1

No that is not correct. Managed ODP.NET does indeed work with TNSNAMES.ORA. You can either point to it using TNS_ADMIN or (if you do not put aliases in your machine/app.config which will take precidence) you can put it in the working directory of the executable. – Christian Shay Dec 12 '14 at 07:23
@Chr: so you have to out the tnsnames.ora file in the exe die? – Patrick Hofman Dec 12 '14 at 07:25
Yes, in the exe working directory or in the location pointed to by TNS_ADMIN (which can be set in app/web/machine.config.). By default, the installer will set TNS_ADMIN for you in machine.config and it will point to the /NETWORK/ADMIN of the home where ODP.NET was installed. – Christian Shay Dec 12 '14 at 07:27
@ChristianShay: But the Managed driver is just a zip, it isn't installed. Which `home/network/admin` does it take then? – Patrick Hofman Dec 12 '14 at 07:40
1

It's true, if you just download the dll itself then yes you need to perform the configuration yourself including setting the TNS_ADMIN value to wherever you place the TNSNAMES.ORA, moving the TNSNAMES.ORA to the working directory, or setting up connect aliases in your config file. However if you are not in a deployment scenario, you can download the installer version of it and that will do more for you. You'll have to use the installer if you are doing design time work with Entity Framework Designers. http://www.oracle.com/technetwork/topics/dotnet/downloads/index.html – Christian Shay Dec 12 '14 at 19:15
Please correct your answer to remove "Managed ODP.NET doesn't work with tnsnames.ora." – Christian Shay Dec 12 '14 at 19:23
1

@Chr : I did. Please review the post. Feel free to edit it to improve it. – Patrick Hofman Dec 12 '14 at 21:10

score 2 · Answer 3 · answered Dec 10 '14 at 12:52

I have recently ventured down this road, only to run into 32x/64x issues when running attempting to run a web site using ODP.net.

Here is my working connection string: Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ip/server)(PORT=port)))(CONNECT_DATA=(SERVER=DEDICATED)(SID=service_id)));User Id=user_id;Password=p_word"

EDIT: Darn, @ialekseev is too fast..

score 0 · Answer 4 · answered Dec 11 '14 at 01:28

0

Copy over the TNSNAMES.ORA that contained data.customer.com into your {ODP.NET ORACLE HOME}/NETWORK/ADMIN directory if you used the Oracle installer to install ODP.NET and it should work because the installer sets TNS_ADMIN variable in the machine.config, (for Managed ODP.NET).

You can also use SQLNET EZ Config connection string syntax if you want to do without the TNSNAMES.ORA entry altogether.

For example: Data Source=MyHost:MyPort/MyOracleService;User Id=myUsername;Password=myPassword;

answered Dec 11 '14 at 01:28

Christian Shay

2,570
14
24

Can also just create the `TNS_ADMIN` environmental variable, and create a registry string value of `TNS_ADMIN` at `HKLM\Software\Oracle\KEY_OraClient11ghome1`, both with the value of the folder path where you will keep the tnsnames.ora, which should be `{ORACLE HOME}/NETWORK/ADMIN`. as stated. – vapcguy Jun 22 '16 at 19:30
@vapcguy It turns out the original poster is using Managed ODP.NET which does not support registry entries nor environment variables. – Christian Shay Jun 27 '16 at 23:26
Um, that's what I'm using, too, and had to implement, including the Environment Variables, in order to get mine working. Was getting errors without them, and was why I mentioned it. You might want to re-read the accepted answer again, because it even states where `TNS_ADMIN` needs to be found - either machine.config, like you state (though I can't vouch for it), but also can be Environment Variables and registry (Oracle needs the registry entry - not .NET) or web.config (which I also didn't try). – vapcguy Jun 28 '16 at 15:02

Connection string in ODP.Net

4 Answers4