Why Error: "[ODBC Driver Manager] Data source name not found..."? (works fine on dev environment)

Question

I'm encountering problems in my ASP page. This is a login page which checks the user's credentials to the database before it allows access to the main page. I created a debugger page to find out what's the error details and this is the error message:

 Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified. 

That's the error message I' m encountering on the web server when I'm loging in to the site. The weird thing here is when testing the page locally on my machine (desktop) everything works fine (even with IIS and MSSQL server). I'm also using the same connection string when deploying and testing the page.

Please help, any suggestions or comments will be greatly accepted.

Thanks, Nhoyti

Answer 1

The problem is probably with the ODBC configuration on the Server itself. In other words the connection string [to the ODBC source] is ok (since tested on other server), but it makes reference to a source that is not configured on this particular server.

To remedy the problem, open the ODBC Data Source Administrator (typically one of the shortcuts in the "Administrative" tools menu), and check if indeed you have a source (typically a "System DSN") with the same name found in the connection string. Such Source is probably inexistant or improperly configured.

Aside from performance considerations, one may consider using more direct Data Providers (such as MS SQL Data Provider or Microsoft.Jet.OLEDB) at the level of the connection string; this avoids the extra configuration point (the ODBC config panel) on the server (but then this may require using two distinct connections strings, one for the test environment and one for the production environment...)

Answer 2

A typical ODBC connection string to connect to SQL Server might look like this.

DRIVER={SQL Server};SERVER=127.0.0.1\SQLEXPRESS;DATABASE=dbname;
UID=userid;PWD=password

Go to Control Panel -> Administrave Tools -> Data Sources (ODBC)

Try create a user DSN using the information provided in your connection string and test the ODBC connection from your production server to the SQL Server.