Introduction
Enterprise Tester is a .Net Framework application and makes use of ODP.Net to connect to Oracle databases.
...
ODP.Net comes as part of ODAC (Oracle Data Access Components) - which can be downloaded from the Oracle website. In most organisations it's normally advised that you have your Oracle DBA install these drivers and configure the settings for Enterprise Tester to connect to Oracle, as they will be better equipped to troubleshoot connectivity issues.
Preparation
Prior to installation you need to determine if you are going to use the 64bit or 32bit version of ODAC - here is how you can determine which you should use.
...
Go to Control Panel -> Administrative Tools -> Internet Information Services (IIS) Manager.
On the left hand side select Application Pools.
Click on the Application Pool being used by Enterprise Tester (this will normally be "DefaultAppPool" or "EnterpriseTester").
Select on the right-hand side "Actions" panel "Advanced Settings...".
In the general section of "Advanced Settings" change "Enable 32-Bit Applications" from "False" to "True".
Download
For ODAC you can either download and install it, or, download a zip file of the binaries and then copy the specific files required by Enterprise Tester into the bin folder for the Enterprise Tester installation directory.
...
Install
If you have downloaded an ODAC release with an installer, you can now install it (as per instructions accompanying the download) - each release changes the installer slightly, so I won't document this process here. More details are here on the Oracle site.
...
Original Value | New Value |
|---|---|
<add key="migration.providerName" value="Migrator.Providers.SqlServer.SqlServer2005Dialect" /> | <add key="migration.providerName"value="Migrator.Providers.Oracle.OracleDialect"/> |
<add key="connection.driver_class" value="NHibernate.Driver.SqlClientDriver" /> | <add key="connection.driver_class" value="NHibernate.Driver.OracleDataClientDriver"/> |
<add key="dialect" value="NHibernate.Dialect.MsSql2005Dialect" /> | <add key="dialect" value="NHibernate.Dialect.Oracle10gDialect" /> |
Connection String
Next you will need to setup the connection string (also in the web.config file)
...
For ODP.Net there are a number of possibilities as to how the connection string can be formatted, here are options/examples (pulled from connectionstrings.com).
Using TNS
Data Source=TORCL;User Id=myUsername;Password=myPassword; |
Using Integrated Security
Data Source=TORCL;Integrated Security=SSPI; |
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; |
Using the Easy Connect Naming Method (aka EZ Connect)
The easy connect naming method enables clients to connect to a database without any configuration.
...
'//' in data source is optional and is there to enable URL style hostname values
Easy Connect Naming Method to connect to an Instance
This one does not specify a service or a port.
Data Source=username/password@myserver//instancename; |
Easy Connect Naming Method to connect to a dedicated server instance
This one does not specify a service or a port.
...
Other server options: SHARED, POOLED (to use instead of DEDICATED). Dedicated is the default.
Appendixes
Appendix A - Application unable to load Oracle.DataAccess.dll
The above changes are normally enough to get connectivity to Oracle working, especially if you use the "EZ Connect" or "ODP.Net with TNSNames.ORA" methods.
...
Where the "DllPath" points to your native drivers i.e. oraociei11.dll, oci.dll etc.
Appendix B - Fixing tnsnames.ora resolution issues
One issue you can have after making these changes (depending on how your machine is configured) is that ODP.Net client cannot locate the tnsnames.ora file and it's entries - you can normally establish this is the problem if connecting via the method "ODP.Net without tnsnames.ora" works, but if using a connection string that requires a tnsnames.ora entry, then it can't connect.
...