Getting Connected to Oracle Database Connections (ODBC)

Background

Traditionally, Microsoft Office at the U of I has been running from 32-bit installs and required 32-bit data sources for their Oracle Database Connections. However, the transition to Office 365 moved to a 64-bit environment and thus requires 64-bit data sources.

Installing the Oracle Instant Client (x64)

Users needing to use an Oracle Database Connection (ODBC) will have the 64-bit Oracle Instant Client 19.3 available to download in Software Center. This should put all of the necessary setup components in place for you to save time on setup. (**If you use Stellet/Capture, you will need the 32-bit version instead)

1. Open Software Center and install the latest version of Oracle Instant Client (x64) available. 
2. Go to ODBC Data Sources (64-bit), click on the System DSN tab and click on the Add button. 
3. In the new Create New Data Source window that opens, select the Oracle in instantclient_19_3_x64 (version number may be different depending on what was installed). Click Finish
4. In the new Oracle ODBC Driver Configuration window that opens, type the following
   • Data Source Name: PROD (STAG or PPRD)
   • Description: PROD (STAG or PPRD)
   • TNS Service Name: PROD (STAG or PPRD)
   • User ID: Your U of I Username without the @uidaho.edu
5. Repeat steps 3-4 if user is accessing DFA Access Database (Note: Data Source Name is important for this connection)
   • Data Source Name: DFA Warehouse
   • Description: DSS
   • TNS Service Name: DSS
   • User ID: Your UofI Username without the @uidaho.edu
6. Test the connection to ensure the client can connect
   • If the user needs to sync their password with the database(s) they use, then reach out to the ITS Database Administrator for assistance.

Troubleshooting the Oracle Instant Client

Sometimes users can have some connection problems. If you have trouble connecting, here are troubleshooting steps we can take

1. Ensure the newest versions of TNSnames.ora and sqlnet.ora are up to date in C:\Oracle\instantclient_19_3_x64\network\admin
   • The most up-to-date versions of these files should have been automatically downloaded if you installed version 19.3 from Software Center
   • If they are old or outdated, your TSP may be able to find more up-to-date ones in W:\downloads\Imaging or W:\downloads\Support\Oracle
   • These two files ensure that the database connections point to the proper U of I servers
2. Ensure that there is a TNS_Admin object in Environment Variables (Control Panel\System and Security\System -> Advanced System Settings -> Environment Variables)
   • This should have automatically happened during the install if you installed version 19.3 from Software Center after 1/27/2020
   • If this object doesn't exist, add it with the name "TNS_Admin" and the path "C:\Oracle\instantclient_19_3_x64\network\admin"
   • This ensures that the TNSnames.ora and sqlnet.ora are properly loaded
     • If using both the 11.2 and 19.3 client, it doesn't matter where the Environment Variable points to as long as the two .ora files are in the specified directory
3. Ensure you are using the correct odbcad32.exe for the connection
   1. The 64-bit version is in C:\Windows\system32, and the 32-bit version is in C:\Windows\SysWOW64
   2. If you can't remove an entry in the 64-bit Window then it is a 32-bit connection and will need to be opened from the 32-bit odbcad32.exe
   3. 32-bit and 64-bit entries can have the same name, but still need to be edited in their respective version of odbcad32.exe

If you are still having problems signing in, your account may have fallen out of sync with PROD. Please work with your TSP and/or the ITS Database Administrator to sync this back up.