Redshift ODBC 64-bit driver error

Question

My system has both 32-bit and 64-bit Redshift ODBC drivers installed. It shows up in the ODBC Data Sources under System DSN tab. However, only 32-bit driver shows up in the Drivers tab.

Also, when I attempt to make a Redshift connection by specifying 64-bit in the connection string Driver={Amazon Redshift (x64)}, I get this error: "ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".

Only the following in the connection string seems to work Driver={Amazon Redshift (x86)}.

I tried

1. Re-installing and repairing 64-bit driver.
2. Removing 32-bit driver from the System DSN tab

But none seemed to work.

Am I missing something?

Answer 1

We had the same problem using the Amazon Redshift driver in our ASP.NET application at our company.

If you're also using the Amazon Redshift driver in an ASP.NET application, I hope what we figured out (the hard way) might help you.

The solution to the problem is to use the 64-bit driver if you're running the application with the 64-bit IIS, and to use the 32-bit driver if you're using the 32-bit IIS. The thing that caught us off guard is that even on a 64-bit machine running 64-bit Visual Studio, the default IIS is 32-bit, not 64-bit. (https://www.jc-tech.info/2016/09/24/running-32-bit-or-64-bit-iis-express/)

This explains why only the 32-bit driver is found by your application. Regarding why the 64-bit driver does not appear in the Drivers tab of the ODBC Data Sources Manager, there are actually 2 versions of the ODBC manager: a 32-bit version, and a 64-bit version. The 64-bit driver will only show up in the 64-bit ODBC manager.

When debugging your application, if you want to make sure that Visual Studio is using the 64-bit IIS, go to Tools>Options>Projects and Solutions>Web Projects, and tick the "Use the 64 bit version of IIS Express" checkbox.

Similarly, when you deploy your application, make sure that your driver version (32 or 64 bit) matches the version of your production environment.

Answer 2

Had same problem,

I kept only 32 bit driver and uninstalled 64-bit and I put Driver={Amazon Redshift (x86)}. It worked fine.

More info can be found from below link.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/480db31c-b583-4c22-a7eb-bc37ad3a3e6d/data-source-name-not-found-and-no-default-driver-specified-error-in-report-builder-when-adding-an?forum=sqlreportingservices

Answer 3

If you are building a .NET application check the project properties for Build option to target x64 platform instead of using Any CPU option to target all