Invalid connection string attributes when connecting via odbcConnection-CodePudding

I'm trying to build a C# WPF application that can connect to multiple types of databases via DSN and I've hit a snag. Normally the connection info (username/password) is entirely stated in the ODBC connection but when trying to connect to an SQL server (that I set up for test purposes) I've run into some problems.

If I try

string connString = $"DSN={dsn};";
OdbcConnection cnn = new OdbcConnection(connString);
cnn.Open()

I get an error

"Login failed for user ''."

If I change the connection string to specifically state the username and password like

string connString = $"DSN={dsn}; UID = {username}; PW = {password};";

I get the same error plus

Invalid connection string attribute

I've tried different versions of this after searching for different connection string examples but I'm stumped. It appears like the server is returning an error that I passed it the username '' rather than the actual login name (CCLogin), and also that the connection string is in the wrong format but I can't find any problems with the format...

CodePudding user response：

Specifying the username with UID is correct, but the password attribute is PWD - you're missing the trailing D. That's what it complains of. The login failure is extemporaneous - it has no password, but that's a secondary problem. You'll likely also find that it's unhappy with DSN - ODBC favors Server={Server};Database={database}. Typically you'll also specify a driver so a full ODBC connection string would look like one of (assuming you're using Sql Server):

Driver={Sql Server};Server=servername;Database=db_name;Uid=username;Pwd=password;

or, if it's a trusted connection (using the same credentials as your windows login)

Driver={Sql Server};Server=servername;Database=db_name;Trusted Connection=Yes;

Start with whichever of those is appropriate and, assuming it works, if you want to then experiment to find what the base MINIMUM is, you can start by eliminating one key-value pair at a time until it breaks. As a bit of advice, though, I'd suggest using the long-form.

If your server is using other than SQL Server, look for the driver name by searching for "ODBC Data Source Administration". That will give you the appropriate application and the driver you're using should be listed there.

ODBC has the benefit of being old and very stable, but it tends to be fussy about how you set it up. The good news is that once set up properly, it'll behave for decades without a blip.