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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Shaun VermaakTechnical SpecialistCommented:
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
Qlemo"Batchelor", 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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Shaun VermaakTechnical SpecialistCommented:
The DSN for SQL Server does not contain credentials
Yes, it can
0
Qlemo"Batchelor", 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 SpecialistCommented:
Are we talking about the same thing?
DSN.png
0
Qlemo"Batchelor", 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 SpecialistCommented:
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
Qlemo"Batchelor", 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 SpecialistCommented:
No, credentials only available if you select SQL authentication
0
Qlemo"Batchelor", 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 SpecialistCommented:
Thank you, will test
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.