Fix ODBC Connections to your SQL Server

We've recently run into more connectivity issues with Open Database Connectivity (ODBC) and SQL Server. Most of the time it's either a 32/64 bit ODBC driver issue or something related to 32 versus 64 bit ODBC drivers. Or course you want to perform the basic troubleshooting steps to make sure the connectivity issue is not related to basic network connectivity. These troubleshooting steps include:

1. Server Name Resolution. Make sure you can ping the SQL Server by name and it resolves to the correct IP address.
2. SQL Server Instance Name. If SQL Server was not installed with the default instance name, you have to access to the server by <sql_server_name>\<sql_server_instance_name> when specifying the server in the ODBC, System Data Source Name (DSN) Properties. To determine the SQL Server Instance Name:
1. Remote into your SQL Server.
2. Start the SQL Server Management Studio.
3. Expand Databases, System Databases.
4. Right click on Master, New Query.
5. In the query field type in select @@ServerName.
6. Click on Execute.
7. The Instance name will be displayed in the results window. If the result is the server name, you have installed SQL Server with the default instance name, so just specify the server name in the System DSN.
3. Firewall. If the firewall is enabled on the SQL Server, make sure to have the following ports open inbound on the SQL Server:
1. SQL Server Port TCP 1433 (or whatever port you've configured SQL Server on).
2. SQL Server Browser UDP 1434.
4. Test the System DSN. After you've configured the System DSN with the ODBC Administrator program, make sure to test the connectivity and verify you get a successful connection.

If you've successfully completed all of these troubleshooting steps it may be a 32/64 bit ODBC connectivity issue. The ODBC Administrator Program for both the 32 and 64 bit programs are named the same, but are located in different folders:

1. 32-bit ODBC Administrator Tool. Odbcad32.exe file is located in the %systemdrive%\Windows\SysWoW64 folder
2. 64-bit ODBC Administrator Tool. Odbcad32.exe file is located in the %systemdrive%\Windows\System32 folder.

Check with your Application Vendor to determine if you need to use the 32-bit or 64-bit of the ODBC Administrator Program. As a general rule, if the application is a 32-bit application use the 32-bit ODBC Administrator Tool. If the application is a 64-bit application use the 64-bit ODBC Administrator Tool. If you don't know if the application is 32-bit or 64-bit you can check the Program Folder where the application was installed.

1. 32-bit. If the application is installed under the \Program Files (x86) folder it's usually a 32-bit application.
2. 64-bit. If the application is installed under the \Program Files folder it's usually a 64-bit application.

We recently ran into an issue where a 64-bit ODBC DSN was defined called SQLServer1, but when the user tried to connect from an Access Application and specified SQLServer1 in the connection string the connection would fail. We knew that the connection was working properly, because we had a successful test using the 64-bit ODBC Administrator. It turned out that the 32-bit version of Access was installed on the workstation, so it was attempting to use a 32-bit ODBC DSN which didn't exist. The SQLServer1 DSN was only defined as a 64-bit ODBC DSN. When we removed the 64-bit SQLServer1 DSN and created the 32-bit SQLServer1 DSN, Access was able to connect. You can download the ODBC SQL Server drivers from https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-2017

If you run into the ODBC connectivity issues and have already performed the basic troubleshooting steps, take a look at the 32-bit versus 64-bit ODBC Administrator Program – this may resolve your connectivity issues. Happy troubleshooting!