Secure database authentication in ADO.NET applications

If you have developed database-driven desktop applications with ADO.NET, you have probably come across the password problem.  Whether your application was written in C#, VB.NET, C++ or other .NET language, the desktop side must always authenticate to the database in order to send and receive information.  Although there are many ways to accomplish this, the easiest and most popular, but also the least secure way, is to store a user name and password in a configuration file or the code itself.  Before doing that, however, consider this: What if a savvy end user opened the configuration file, read the user name and password and used it to create havoc in your database?  Would the ease of this method outweigh the risks?  Probably not.  That is where the following authentication alternatives may come in handy.

The Database Users Option

With this method, you would keep a collection of users in your database.  Before any data can be accessed, the user would need to authenticate into the database via the desktop application.  When working in an Active Directory environment, the database can be setup to grant access through security groups.  This way, any user who falls into an authorized group can access the database.  Of course, the problem with this option is the maintenance of users or group memberships, specially in a large organization.

The Middleware Option

This is method involves having a single account in the database server whose credentials are stored in a middleware application.  Users authenticate against the middleware server, who in turn processes the users’ queries in the database and returns the data.  A common middleware solution would be a web application that returns XML data.

The Hard Coded Option

A common solution is to hard-code the database user name and password.  Of course, if the user name and password ever changed, the application would have to be re-compiled and re-deployed (either completely or just the module/dynamic link library holding the hard coded values).  To make this method more secure against reverse engineering, you can obfuscate and further encrypt the database user name and password.  Unless encryption is used, anyone with access to the source code will be able to see the database credentials.

The Configuration File Option

In almost every .NET book and online example, you will likely see either this method or the hard-coded option.  This solution is similar to the hard-coded option, except that the user name and password are stored in the configuration file instead of being hard coded.  This makes it easy to replace the database credentials, since there is no need to recompile anything.  However, it also presents a great threat, since any user can read the credentials.

The Hard Coded + Configuration File Hybrid Option

This is a combination of hard-coding database credentials and placing them in a configuration file.  The method allows a database administrator to provide encrypted credentials to a single developer, without revealing the plain text values to other developers who may see the source code.  In addition, the end users would still be able to read the configuration file, but would only find encrypted values with little meaning.  This solution works as follows:

  • Two secure, long, random keys are created.  One is hard coded into the desktop application’s source code, while the other is kept in the configuration file.
  • The hard-coded key is used to encrypt the configuration-file key.  The result is used to encrypt the database user name and password separately.  These values are saved to the configuration file.
  • At run time, the application generates the third key using the hard-coded and configuration-file keys, and then decrypts the user name and password.

 The last method can be further combined with other methods to increase security.  However, as security increases, so does the complexity of development and maintenance.  The hybrid method is very simple to use, and provides a much safer alternative to simply hard-coding database credentials or throwing them in a configuration file.  The next time you see a code snippet online that uses an unsafe authentication method, remember that there is a simple way to make it more secure.

About Gabriel Mongefranco

Gabriel Mongefranco is your software developer for all things data: extraction, integration, analytics and security. He is also a blogger, a poet, a proud father and a faithful Christian. He is always eager to contract with faith-based nonprofits! Learn more.