Search code examples
sql-serverodbcfirebirdfirebird2.1

How to setup Linked Server with ODBC DSN for a 32-bit Firebird environment using the 64-bit MSSQL Provider

I am trying to make a Link Server to my running Firebird instance on SSMS however I am encountering the following error when I click "OK":

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "FIREBIRD". OLE DB provider "MSDASQL" for linked server "FIREBIRD" returned message "[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application". (.Net SqlClient Data Provider)

Here is how I filled up the Link Server wizard and my DSN: enter image description here

enter image description here

My Firebird version is 2.1.5.18497 at 32-bit, and the Firebird ODBC is at 2.0.5.156 at 32-bit.

The SQL Server I'm currently running on is Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)

I am not using the latest Firebird version since there are incompatibilities on the ODS version of my .FDB file, and it can only be understood by the current Firebird version I have. I tried changing the Firebird ODBC driver to 64-bit but I am getting an error:

Unable to connect to data source: library 'gds32.dll' failed to load

^ this is the error when testing the Firebird database connection to my .FDB file inside my DSN configuration if I used the 64-bit version of the Firebird ODBC driver.

I am thinking this happens because of the different bitness of providers on the MSSQL instance and the Firebird ODBC Driver. If this is the case, is it still possible to make a link server with my current Firebird and MSSQL setup? If not, is it still possible to upgrade the .FDB database to make the ODS version compatible to the newest version of Firebird so I can install the 64-bit version of it?

Any help would be appreciated!

Solution

  • Because of hardware architecture limitations 64 bits processes cannot load 32 bits libraries and vice versa. So if you want to use Firebird from 64 bits MS SQL server you must have 64 bits Firebird client and 64 bits Firebird ODBC driver installed.

    Note that 32 bits and 64 bits versions of these libraries can co-exist in system so you can install 32 bits versions as well if you need them for any other application.

    You must set "fbclient.dll" as a client library name in ODBC settings.

    Also it doesn't matter what bitness of Firebird server is. Only bitness of Firebird client library must match bitness of the application (MS SQL server in this case). Firebird client should be installed into system folder using "instclient i f" command from appropriate ZIP package from https://firebirdsql.org/en/server-packages/.

    Firebird allows quite big difference between client and server versions, but still it is better to match them up to minor version. For third number - higher is better. I.e. 2.1.9 is better than 2.1.5.