Using DSN to connect to SQL Server

I'm using VS2010 VB.net with SQL Server back end and would now like to start using Data Source to connect rather then coding credentials in appconfig. I'm also worried about exposing SQL passwords. Is there a safe way to do this and can you refer me to an example please.
Frank De CarpenterIT DirectorAsked:
Who is Participating?
 
Ryan ChongCommented:
Is there a safe way to do this and can you refer me to an example please.
we can actually encrypt part of the app.config setting when necessary. I have done similar encryption for my web.config file. same concept should be applied.

you can read this through:

Walkthrough: Encrypting Configuration Information Using Protected Configuration
https://msdn.microsoft.com/en-us/library/dtkwfdky.aspx

also check the discussions here:

Encrypt connection string in app.config
https://stackoverflow.com/questions/11637348/encrypt-connection-string-in-app-config
0
 
Shaun VermaakTechnical Specialist/DeveloperCommented:
Why use DSN or App/Web config with credentials if you can just use the context of the user running this application?

With Windows authentication, users are already logged onto Windows and do not have to log on separately to SQL Server. The following SqlConnection.ConnectionString specifies Windows authentication without requiring the a user name or password.
"Server=MSSQL1;Database=AdventureWorks;Integrated Security=true;

Open in new window

 

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/authentication-in-sql-server
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
The DSN for SQL Server does not contain credentials, so the application needs to add them on connect. Encrypting or obfuscating your location containing that info is your only choice, if you need to use explicit credentials.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Shaun VermaakTechnical Specialist/DeveloperCommented:
The DSN for SQL Server does not contain credentials
Yes, it can
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
The DSN itself cannot (in ODBC Administrator). You can use credentials together with a DSN.
"Server=MSSQL1;Database=AdventureWorks;Integrated Security=true;" is no DSN.
0
 
Shaun VermaakTechnical Specialist/DeveloperCommented:
Are we talking about the same thing?
DSN.png
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Shaun, that is a common misunderstanding. The credentials provided there are just for "Connect to SQL Server to obtain ...", and only used for the next setting dialog where you are able to provide the default database etc. Those credentials are not used for the connection itself later.
0
 
Shaun VermaakTechnical Specialist/DeveloperCommented:
Hmmm, are you sure? I have migrated applications that break if you change authentication on DSN file...

Very good to know (and confusing) if this is the case
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Indication is that the credential fields are only available if you check "Connect to SQL Server to obtain ...".
0
 
Shaun VermaakTechnical Specialist/DeveloperCommented:
No, credentials only available if you select SQL authentication
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
That is another requirement. You can have checked SQL Authentication and still not checked "Connect to SQL Server to obtain ...", and the credentials are still locked from being entered.
0
 
Shaun VermaakTechnical Specialist/DeveloperCommented:
Thank you, will test
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.