DSN Tests successfully, but unable to use in C# application

Question

DSN Tests successfully, but unable to use in C# application

ram302

SSCommitted

Points: 1618
More actions
July 13, 2017 at 2:42 pm

#338216

I've created a DSN, set the username/password, test the log-in and it's successful. However, when I try to use the DSN from the C# application, I get an error that reads something like "Unable to login as '' ". I was able to get the application working when I added UID="userlogin"; PWD="blahpassword" to the DSN connection string in my .config file.
I'm trying to use the DSN to avoid having the username/password values in the config file. The database is set to allow logins from windows authentication and SQL users too.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Mr. Brian Gale SSC-Insane Points: 24692 More actions · Answer 1

When you say DSN, I am assuming this is an ODBC connection? If so, those do not store the password for the credentials. You can verify this by looking at your registry under:
HKLM/Software/ODBC/ODBC.INI
or
HKLM/Software/Wow6432Node/ODBC/ODBC.INI
That is where Windows stores all of that infomration, and you can see the password is not stored.

If your database is set to allow logins from Windows Authentication, why not use that in your C# conenction string? That is, you set your connection string to something like:
Data Source=Server\InstanceName;Initial Catalog=Database;Provider=SQLNCLI11;Integrated Security=SSPI;Application Name=YourApplication;

replace Server/InstanceName with your server and instance name and you can optionally add the port by specifying it as Server\InstanceName, Port.
replace Database with the database you want to connect to.
Change the Provider if you use a different provider. That is the one we use for most of our stuff, some things use the SQLNCLI10 provider
Integrated Security=SSPI is what allows you to use windows authentication.

If you DO need to use SQL Authentication, then I would recommend storing that in the compiled version of the application, preferrably with some sort of encryption just to be sure that it is semi-secure.
If you are required to use ODBC, everythign I'm reading online says you need to hard-code the username and password in with that. https://www.dotnetperls.com/odbcconnection

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

ram302 SSCommitted Points: 1618 More actions · Answer 2

bmg002 - Thursday, July 13, 2017 3:18 PM
When you say DSN, I am assuming this is an ODBC connection? If so, those do not store the password for the credentials. You can verify this by looking at your registry under:
HKLM/Software/ODBC/ODBC.INI
or
HKLM/Software/Wow6432Node/ODBC/ODBC.INI
That is where Windows stores all of that infomration, and you can see the password is not stored.
If your database is set to allow logins from Windows Authentication, why not use that in your C# conenction string? That is, you set your connection string to something like:
Data Source=Server\InstanceName;Initial Catalog=Database;Provider=SQLNCLI11;Integrated Security=SSPI;Application Name=YourApplication;
replace Server/InstanceName with your server and instance name and you can optionally add the port by specifying it as Server\InstanceName, Port.
replace Database with the database you want to connect to.
Change the Provider if you use a different provider. That is the one we use for most of our stuff, some things use the SQLNCLI10 provider
Integrated Security=SSPI is what allows you to use windows authentication.
If you DO need to use SQL Authentication, then I would recommend storing that in the compiled version of the application, preferrably with some sort of encryption just to be sure that it is semi-secure.
If you are required to use ODBC, everythign I'm reading online says you need to hard-code the username and password in with that. https://www.dotnetperls.com/odbcconnection

Yes, you're correct, I'm using the connection string in my code for OdbcConnection , but I didn't think to use that part of the connection string that tells it to use a trusted connection. Thanks!!!