ODBC DSN Primer

Glossary:

• ODBC: "Open Database Connectivity;" A C programming interface that abstracts the actual connection information from database-native languages (such as a particular type of database). This allows for applications to take advantages of commands to a particular data connection and treat it the same as any other data connection. The same commands from an application can be used across different data systems without having to use specific languages and commands to do so. More information can be found at Microsoft's website here. 
• DSN: "Data Source Name;" the given connection using ODBC or any other data name. It's the actual piece that connects an application to a data source using connection-appropriate commands (such as an Oracle client, Microsoft file formats, network locations, etc.) You can find the DSNs installed on a machine using the ODBC Data Sources application on a Windows machine. For reference, until we phase out Microsoft Office 2019, we will be almost exclusively dealing with the ODBC Data Sources (32-bit) application to perform ODBC Administration.
• TNS:  "Transparent Network Substrate;" the connection technology at the network layer for Oracle databases. This also references TNSNAMES.ORA, a file that translates a "database name" in Oracle-speak ("TNS Name") to its server via defining server, port, and Service Identifier (SID).

Basic Connection Pathing:

Without belaboring it too much, or getting too in-the-weeds, a typical Oracle database path for ODBC-style applications (Microsoft Access, Tableau Desktop, Tableau Data Prep, and Brio, among others) follows this line of connectivity:

^{(Click to view full size)}

In a real world context, it translates to this (as an example):

^{(Click to view full size)}

Lehigh University ODBC Connections to Banner

Some basic notes about ODBC at Lehigh:

• ODBC DSNs can only be created by Administrators on Windows machines. Those without admin access on their machines will need elevation through LTS (via a Computing Consultant) to add new DSNs.
• All Banner ODBC DSNs are managed by LTS Endpoint Engineering and are, by default, installed via Windows Updates to all faculty and staff Windows machines. LUapps VDIs also have these DSNs installed.
• TNSNAMES.ORA is maintained by LTS Enterprise Systems and any changes/modifications must be submitted to the DBAs of the group.
• Mac OS does not use ODBC DSNs; all connections must be created directly for things like SQL Developer. Connections will have to be connected directly via server name, port, and SID on Mac OS.
• "Banner Database Passwords" are distinct from the Lehigh network password (SSO/SAML), and are held to different standards and change requirements.

Some basic notes about requirements for things to work via ODBC:

• A user must be connected to the LTS Network, either directly (via hard line or wifi on-campus) or via VPN. All Banner resources are behind the Lehigh firewall, so one must be authenticated via the Cisco AnyConnect client to campus.
• A user must have the DSN installed. 
• The DSN must be correctly pointed to a TNS name. (DSN configuration)
• The TNSNAMES.ORA file must be available to the user's endpoint. 
  • Generally this means that the J:\ drive must be mapped and available
  • The TNSNAMES.ORA is located at J:\banner\ (direct location: \\common.cc.lehigh.edu\banner\banner\)

Basic ODBC Troubleshooting

Basic Access Troubleshooting

Access connections to Banner fall under one of two flavors: Database Application Consultant (DBAC) code, and Microsoft Access native connections.

DBAC Code

You can determine that these are old DBAC code when you see this login prompt:

^{(Click to view full size)}

This particular code is a form with Visual Basic for Applications (VBA) as its back-end. Because of the complexity and the one-off nature of these databases, troubleshooting Microsoft Access databases with these prompts will be most comprehensively handled by Enterprise Systems Information Specialists. Please forward any tickets/calls to Enterprise Systems for resolution.

Microsoft Access Native Connections

You can determine that these are Microsoft Access native connections when you see this login prompt:

^{(Click to view full size)}

You will be able to troubleshoot most of these with basic Microsoft Access information via Linked Table Manager.

Updating Linked Tables for the Cloud

Before a user can connect to the Ellucian Cloud, one must re-point any Linked Tables in Microsoft Access to the Ellucian Cloud ("EC"). 

The following instances will need to be re-pointed:

In order to update the Linked Tables in Microsoft Access, follow this procedure:

Object (Table/View) Does Not Exist Errors

Issue 

In some instances the following error will occur (with variations on what the object is):

^{(Click to view full size)}

Underlying Cause 

In this case, if it worked before B2C go-live, this means that the table is not visible to the user. There was a security re-alignment for older legacy users that provides more sustainable, secure solutions to accessing data directly on the database. Because of the complexity of the almost 20-year build up of Banner accounts, some users lost access to some of their tables when this change was implemented. 

During the Community UAT phase of the project, we identified a majority of the issues with this change, but we are aware that not all may have been resolved.

Resolution

The user will need to be added to a role that includes the objects required in the Access database.

During Stabilization Efforts: Please submit a ticket to Enterprise Systems for realignment in the proper roles (as this might need additional role validation).

After Stabilization Efforts: Please submit a ticket for IAM to adjust the roles in Banner as per our regular process.

Macro Single Step/Error 3146

Issue

Sometimes when running a macro in Microsoft Access, the following error will occur:

^{(Click to view full size)}

Underlying Cause

Microsoft Access error 3146 is a generic unhandled exception error. We have not yet identified a root-cause of this issue. It has had various resolution steps, none of which have pointed to a reliable root-cause for all of them. We suspect it may be deprecated code in a Microsoft Access database causing issues as the 32-bit version of Access is being phased out in favor of its modern counterpart (64-bit).

Resolution

Each error of this type has functioned differently from each other. Please submit a ticket to Enterprise Systems under Access for departmental review and resolution. 

ODBC–Call Failed error

Issue

While retrieving data from the Oracle database, sometimes this issue appears:

^{(Click to view full size)}

Underlying Cause

The main issue that returns an "ODBC–call failed" error is that something broke down in the process of Microsoft Access executing a query across the ODBC connection. As referenced at the top of this document, there are several steps that may break down in this process. Identifying the issue will be related to what the issue is in the rest of the error message.

Resolution

• • TNS Could not resolve the connect identifier specified:
    • Make sure the J:\ drive is mapped.
    • Make sure the DSN refers to an actual entry in TNSNAMES.ORA on the J:\banner\ location. This will be as easy as making sure it is a referenced TNS Name (ECPROD, ECRPTP, etc)
  • Table or View Not Found
    
    • This is either related to the aforementioned security issue or there is a more severe issue at work. Please create a ticket for Enterprise Systems.
  • Macro Exceptions
    • This is a more severe issue related to Microsoft Access and ODBC. Please create a ticket for Enterprise Systems to resolve the VBA/ODBC interaction.
  • Generic Oracle Error
    • The "ORA-x" error specifies what the Oracle-native exception is. Resolution steps can be provided via an internet search and resolution steps. This can also be forwarded via ticket to Enterprise Systems.
  • No additional text provided besides the "ODBC-call failed" error.
    • Sometimes Microsoft Windows Updates corrupt the ability for 32-bit Microsoft Access to work. Sometimes a root cause has not been found. Either way, please forward the information via ticket to Enterprise Systems.