I have only been programming for the better part of 1-2 years, C# the last 7 months or so, Up til now I have used the .config file to store needed settings that cannot be stored in the database, and it was okay to do so.

Now I have a client where there are many users that will access a database, and part of the spec is that the application must log into sql using the sa username, obviously if anyone gets hold these settings it would be a problem.

I want to know what the best practice for something like this would be, I can encrypt the password and server address, but I still feel uneasy about this.

What is the best practice in the industry for storing settings that cannot be in the database, especially the ones that are sensitive configurations

5 Answers
5

You can put settings into a local database. I prefer MS SQL Server Compact 3.5, which is free. This way you can store your settings into a local SDF database file, which can be encrypted and password protected. The data stored in the SDF file can be accessed using e.g. ADO.NET+SQL, but I prefer Linq-to-SQL.

EDIT:

Please take into account that although SDF files can be encrypted and password protected, if the file is stolen, it can definitely be cracked by a brute force method. The same is true for any other solution, which stores sensitive data on client machines.

I can't recommend highly enough to revisit the requirement to have the sa account used by the application. That is a HUGE security hole. Given the information provided, I would recommend encrypting the connection (won't really matter where you store it). Make sure that if your database connection methods fail, the error result won't display the user name and password.

The best practice method (I think) would be to use SQL server integrated security: (you authorize a Windows/Active Directory user to access the server, the security aspect is now handled by Windows and your Domain configuration) - however this is not always practical, and you might not want to give the windows user that much access to the database outside the client software (eg, the application must insert/update/delete records in the database, something you wouldn't necessarily want the user to be able to do if they logged into the DB via SSMS)

Another method would be to use the 'Protect' and 'Unprotect' methods of the System.Security.Cryptography.ProtectedData class to encrypt and decrypt the password, and/or the connection string. (you will need to set a reference to System.Security.dll).

This gets around the "where do you hide the key" issue - the ProtectedData class uses your Windows machine's entropy pool to generate a key. You can add your own salt (by way of a byte-array as "additional entropy") to ensure that the data cannot be retrieved by another .NET program running under the same user-context also using the ProtectedData class. You can 'protect' the password/data so it can only be 'un-protected' by the same user on the same machine that protected it.