Why can't I get my CDatabase object to understand my Data Source Name?

Question

I'm using the MFC class CDatabase. To establish a connection to SQL Server, I'm calling OpenEx() with a connection string. My problem is that the object seems unable to interpret the DSN part of the string. The connection string looks like this:

 ODBC;DSN=mySystemDSN;UID=myUsername;WSID=myMachineName;DATABASE=myDatabaseName;Trusted_Connection=Yes

That ought to be right because I got it by using OpenEx( NULL ), choosing the data source manually and then calling GetConnect().

But this string doesn't seem to contain enough information: OpenEx() always pops up a dialogue asking for more. It doesn't seem to matter what I choose from this dialogue - I can pick a DSN associated with a completely different database and things still work (a call to GetConnect() in that situation shows that it's using my connection string except for the DSN part, which is borrowed from the other data source).

I need my application to be able to connect to the database automatically - dialogue boxes are a deal-breaker. I've tried a DSN-less connection with similar results. What's going on here, and what can I do about it?

edit in answer to Neil Butterworth's question:

The information I provided when I created the DSN in the ODBC Data Source Administrator was as follows:

driver: SQL Server
name: mySystemDSN
server: myMachineName
authentication type (can be Windows or SQL Server): Windows
checkbox "Connect to SQL Server to obtain default settings for the additional configuration options.": ticked
checkbox "Change the default database to": ticked and myDatabaseName chosen from drop-down menu
checkbox "Use ANSI quoted identifiers": ticked
checkbox "Use ANSI nulls, paddings and warnings": ticked
checkbox "Perform translation for character data": ticked

Answer 1

You seem to be missing a password and the hostname of the server. I suppose the interesting question is what information did you enter in the dialog to create the DSN?

Edit: You may also want to take a look at this site, which has a lot of connection string examples.

Edit2: I would remove the "ODBC;" at the start of the string. If that doesn't work, I'd change the authentication type in the DSN to SQL server and supply a user id & password explicitly, just to check that authentication is not the problem. And then I'd probably give up :-)

Answer 2

Some options:

• Is it a system or user DSN? That is, is the DSN visible to the executing user?
• Try removing the UID=myUsername
• Try Driver=ODBC instead of ODBC

Why are you using a DSN and not a more conventional, self contained connection string? Such as Driver=SQLOLEBD;WSID=myMachineName;DATABASE=myDatabaseName;Trusted_Connection=Yes;

Answer 3

Thanks to Neil Butterworth, I found a working answer here:

"Driver={SQL Native Client};Server=myMachineName;Database=myDatabaseName;Trusted_Connection=yes;"

I'm still baffled as to why calling GetConnect() when the connection is working doesn't produce a perfect DSN string, but now that I've got a DSN-less solution I don't care as much!

Answer 4

for creating connections strings i use www.connectionstrings.com. Fantastic for creating connection strings on all variants and database enginges