Wed, 21 Feb 2018
Does Your ODBC Driver Support User Data Sources?
ODBC data source stores the connection details for a target database (e.g. Salesforce.com) and the ODBC driver that is required to connect to it (e.g. the Salesforce.com ODBC driver). System data sources are available to any user. User data sources are specific to a particular user.
You create a data source for a particular user and only that user can connect to that data source. User data sources enable you to control access to your database. For example, you could create two data sources, one for UserA and one for UserB, each connecting to a database with a different role-specific login. UserA and UserB's access to the database is policed by their particular database login.
In answer to the initial question posed by this blog, it's the application not the ODBC driver that determines whether you can use User Data Sources. (Our ODBC-ODBC Bridge product is both an ODBC driver and an application and we'll come to that later in the blog.)
If, after configuring a User data source, you aren't presented with an opportunity to connect to it (i.e. for GUI-based applications, it's not in the list of available data sources) or you get a "data source not found" error, your application does not support User Data sources. One reason for this is when an application runs as a service under a particular account that does not have access to another user's resources. For example, when SQL Server runs under the local system account, it cannot access User data sources, and for this reason only lets you use System Data Sources when creating ODBC-based linked servers.
If your User data source is missing from the list of available data sources in a Windows application, it could also be that you have configured it in the wrong version of ODBC Data Source Administrator. If you configure a data source (either System or User) in the 64-bit version of ODBC Data Source Administrator and your application is 32-bit (or vice versa), the data source will not be listed. If you search for either "32-bit ODBC Data Source" or "64-bit ODBC Data Source" in the Windows search facility, you will know which application architecture you are configuring a data source for.
The ODBC-ODBC Bridge is a client server product. The client part is an ODBC driver, and you can therefore configure a User data source for the ODBC-ODBC Bridge client. The server part is an ODBC application that runs as a service and doesn't support User data sources.