Issue installing Firebird ODBC drivers -- driver eventually no longer found

Question

I am experiencing an issue with an SSIS job that connects to a Firebird DB using their ODBC driver (version 2.0.4.155).

• The SSIS package runs flawlessly in Visual Studio 2008, which is installed on Windows 7 32-bit (don't ask!)
• The package fails to execute when deployed to SSIS in SQL Server 2008 64-bit. The error:

  Data source name not found and no default driver specified.

This immediately made me think that a ODBC driver mismatch might be the issue, where SSIS is looking for the 32-bit drivers. So here is what I did:

• Uninstalled the Firebird ODBC 64-bit drivers
• Installed the Firebird ODBC 32-bit drivers
• Modified the Job schedule in SQL Server so it executes in 32-vbit
• Package runs successfully after that -- however there is a twist.

I will be able to execute the package again as long as I am still logged in to the SQL server (I logged in to install the Firebird drivers). I can execute it even from a remove SQL Server Management Studio connection -- but as long as I do not log off from the server.

If I log off from the server, then the SSIs job will no longer work (same error as before). This led me to think that this is not in fact a 32/64 bit mismatch, but perhaps some registry or environment variable is not being committed after I log off from thee server due to insufficient permissions (even though I was meant to be admin on that server).

So for my next test:

• I uninstalled the 32-bit driver
• Reinstalled the 64-bit driver
• Remained logged into the SQL server
• Executed the job time and again from a remote SQL Server Management Studio: It worked every time

As soon as I logged off from that SQL server, the behavior occurred again. This was hard to troubleshoot at first because the drive installs without throwing any errors and it even works at first.

But I am confident now that this has to do with permissions. Any ideas what I might be missing?

Answer 1

The issue here is that I had to create a System DSN, which is actually odd because the SSIS job is scheduled to run with the account for which the User DSN exists, and it does work as long as I am logged into the server with that account. Anyway I am way too tired to try to understand why the User DSN didn't work. So I decided to go for a System DSN instead.

It is not that I am against System DSN (on the contrary!), but it wasn't me that set this User DSN and I do not know the password for the credentials used in order to create a System DSN. So I had to be a little creative.

I had to use Registry Editor and notepad to convert a User DSN into a System DSN. Here is how I did it:

1. Log in with the user that has the User DSN entries that you with to convert to System DSN
2. Open the Registry Editor and navigate to HKEY_LOCAL_CURRENT\SOFTWARE\Microsoft\ODBC\
3. Right-click ODBC.INI and select export to save it as a file on the desktop (or anywhere else you fancy)
4. Open the .reg file with a text editor such as Notepad
5. Replace the text HKEY_CURRENT_USER with HKEY_LOCAL_MACHINE. Save your changes
6. Double click the .reg file and proceed to import it into the registry.

The process outlined above basically copies all User DSN entries as System DSN entries. If you to have just one entry copied over, then you will need to delete their corresponding keys under .\ODBC.INI as well as their corresponding entries in .\ODBC.INI\ODBC Data Sources.