C# & SQL Server Authentication

Question

I'm currently developing a C# app with an SQL Server DB back-end. I'm approaching the point of deployment and hitting a problem. The applicaiton will be deployed within an active directory network. As far as SQL authentication goes, I understand that I have 2 options - Windows Authenticaiton or Server Authenticaiton.

If I use Server Authentication, I'm concerned that the username and password for the account will be stored in plain text in the app.config file, and therefore leave the database vulnerable.

Using Windows Authenticaiton will avoid this issue, however it would mean giving every member of staff within our organisation read/write access to the database in order to run the app correctly. Whilst this is ok, it also means that they can easily connect to the database themselves via other means and directly alter the data outside of the app.

I'm guessing there is someting really obvious I'm missing here, but I've been googling all evening to no avail. Any advice/guidance would be much appreciated!

Peter

---

Addition - my project is Windows Form based not ASP.NET - is encrypting the app.config file still the right answer? If it is, does anyone have any examples that are not ASP.NET based?

Answer 1

If I use Server Authentication, I'm concerned that the username and password for the account will be stored in plain text in the app.config file, and therefore leave the database vulnerable.

You could always create a connection string in your app.config and encrypt that connection string section. This works for ASP.NET as well as for console apps, Winforms apps, WPF apps - it's a .NET base technology, really.

See Jon Galloway's Encryping Passwords in a .NET app.config for a non-ASP.NET sample of how to do this.

Using Windows Authenticaiton will avoid this issue, however it would mean giving every member of staff within our organisation read/write access to the database in order to run the app correctly.

Yes - but you can ease the burden:

• you can authenticate a Windows / AD group - everyone who's a member of that group has access - no need to individually permissions each user

• if you use views for data retrieval and stored procs for insert / update / delete, you won't even need to give those users direct table access and you can shield your database from inappropriate manipuluation