I'm working on an application which requires user credentials and so on. So a database is required on the backend.

What is the best practice to connect to a database without hardcoding your password into it? The program could be reverse engineered and one might get the password which is something we obviously don't want.

  • Would be good if you could add to your question: (1) what is the nature of the application - web, Windows desktop, Silverlight, Windows Phone? (2) what is the database (assuming SQL Server)? and (3) who will the users be - internal to your organization (all within an AD domain?), or the general public? – Carson63000 Aug 3 '12 at 23:47

I just went though this myself. Assuming you're using SQL Server, I actually created user accounts on the database server for every user (you can do this from t-sql). I have one general user account that I can use to first verify that I can make a connection, and select from a Version table that makes sure the version of the software running matches the database schema version.

Users who have the ability to add/remove users in the application also need to have rights to administrate users on the database server. This is actually a problem since the users are defined at the database server level, not at the database level. That means someone with the ability to add/remove database server users could potentially create a new user that could access another database. For this reason, I suggest running a separate instance of the database server for this.

There are other ways to go about it:

  • If your users login to Windows with unique accounts, you can use integrated security where they just authenticate with the Windows credentials. Ideal if it works for you (we had some shared computers, so it didn't).
  • You can have a common user and hard-code the credentials in the connection string, but only give that user access to stored procedures, not tables. Then in the stored procedures you have to pass login credentials every time, and the stored proc does the authentication. This has the advantage of allowing more granular security (you can limit what rows they can get back, so for instance, they wouldn't be able to see another user's timesheet records, but they could see their own).
  • If you need only rudimentary protection of some data, just have two users: NormalUser and AdministratorUser. Only store the NormalUser's password in the connection string. When you need to do something that requires administrator level permissions, have them enter the AdministratorUser's password, and open a new connection.
  • Put the database into local-only mode (no remote connections) and create a web service that runs on that machine and acts as your data access layer. This will let you do really advanced security (gives you full control over what a user can see) and it means the rest of your application basically sees the data as business objects rather than rows and columns. The web service, obviously, needs to handle the authentication.

Put your credentials information in the <connectionString /> on the app.config file. Then, proceed to encrypt the <connectionStrings /> section.

See the following article for more information: Encrypting Passwords in a .NET app.config File

  • Unfortunately this method of "encrypting" the password only offers security from clueless users because, as it says in the article, the Windows user that is logged on has access to the key necessary to decrypt it. By doing a little thought experiment you can easily see it's impossibly to prevent a user from getting a password if the application they're using locally has access to it. – Scott Whitlock Aug 20 '12 at 11:44
  • No users should even have access to your web.config file. You can use the best encryption algorithms and still be unprotected if you don't take care of who access your server disks. – Luis Aguilar Aug 20 '12 at 15:29

The best practice is to give each user their own account on the database, and to set permissions on that account to restrict their access as you see fit.

The user's credentials go into a configuration file for the application.


Can you use integrated authentication? For example, in SQL Server 2008 R2, the following connection string accesses the local master database using Windows credentials.

Data Source=localhost;Initial Catalog=master;Trusted_Connection=true

And I would never recommend creating SQL users for an application. You are now giving direct database access to your users, who should always be using the application (unless you explicitly allow them direct access). Additionally you are restricted by what tools SQL offers you for authentication. A common problem is per-row access, your application can do the filtering pretty easily, but creating stored procedures to duplicate it is non-trivial.

To be fair though, you do need to worry about escalation of rights attacks when using a proxied account.

  • Persist Security Info should almost always be false. – Scott Whitlock Aug 20 '12 at 11:49
  • @ScottWhitlock: Pulled a sample connection string from a Dev environment, I will remove it. Although I wonder if it matters when using a trusted connection. – Guvante Aug 20 '12 at 15:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.