How to configure database connection securely

Question

Similar but not the same:

• How to securely store database connection details
• Securely connecting to database within a application

Hi all, I have a C# WinForms application connecting to a database server. The database connection string, including a generic user/pass, is placed in a NHibernate configuration file, which lies in the same directory as the exe file.

Now I have this issue: The user that runs the application should not get to know the username/password of the general database user because I don't want him to rummage around in the database directly.

Alternatively I could hardcode the connection string, which is bad because the administrator must be able to change it if the database is moved or if he wants to switch between dev/test/prod environments.

So long I've found three possibilities:

1. The first referenced question was generally answered by making the file only readable for the user that runs the application.

   But that's not not enough in my case (the user running the application is a person. The database user/pass are general and shouldn't even be accessible by the person.)

2. The first answer additionally proposed to encrypt the connection data before writing it to the file.

   With this approach, the administrator is not able anymore to configure the connection string because he cannot encrypt it by hand.

3. The second referenced question provides an approach for this very scenario but it seems very complicated.

My questions to you:

1. This is a very general issue, so isn't there any general "how-to-do-it" way, somehow a "design pattern"?

2. Is there some support in .NET's config infrastructure?

3. (optional, maybe out of scope) Can I combine that easily with the NHibernate configuration mechanism?

Update:

In response to the first answers: There are several reasons why I would want to connect to the database directly and not use a web service:

• (N)Hibernate can only be used with a database, not webservices (am I right?)
• We plan to provide offline capability, i.e. if the database or network should be down, the user can continue his work. To manage this, I'm thinking of having a local, in-proc database, e.g. SQL Server Compact, and using MS Sync framework to synchronize it with the server database as soon as it is up again.

Do you have any further ideas taking this into account?

Answer 1

First of all, letting untrusted users connect to a database is generally not a good idea. So many things can go wrong. Put a web service inbetween.

If you absolutely have to do it, make it so that it doesn't matter even if they get the username and password. Limit their privileges in the database so that they can only execute a few stored procedures that have built-in security checks.

Whatever you do, you can't give the username/password of a privileged user to an untrusted person. It's just asking for trouble. No matter how well you try to hide your credentials within an encrypted string inside a binary file or whatnot, there's always a way to find them out. Of course whether anyone'll actually do it depends on how interesting your data is, but silently hoping that mean people with debuggers will just leave you alone is not a very good security measure.

Answer 2

Actually the WebService approach (mentioned in some other answer) means that you move NHibernate and its logic to the web-service. The WebService then, exposes the db functionality available to the application using the WebService's methods.

There is practically only one user for the database, the one the WebService uses and if you want the application user to have different db privileges you abstract it from the WebService layer

In the end, the WinForms application is only aware of the location of the WebService where it requests data through the WebService's methods and you can apply any required security measure between these two endpoints.

For off-line capability it all boils down to making a secure way to persist your data to local storage and providing a synchronization method via the WebService

I have actually done this using a webservice that communicated with the DB and a WinForm application (.NET Compact Framework) that only talked to the webservice and in case of no cellular network coverage it would serialize the changes to the memory card (the data was not important so for my case obscure/obscene security measures where not taken)

UPDATE with a small example as requested (i do find it strange though to ask for an example on this)

you have set up your domain classes and nhibernate configuration and (for example) your repository stuff in a project of type ASP.NET WebService Application. For the sake of simplicity i'm only going to have a single web-service class Foo (in Foo.asmx.cs) and well as a single Bar domain class

so you get this (actual implementation varies):

namespace FWS
{
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    // [System.Web.Script.Services.ScriptService]
    public class FooService : WebService
    {
        private readonly ILog errorLogger = LogManager.GetLogger("ErrorRollingLogFileAppender");
        private readonly IDaoFactory daoFactory = new DaoFactory();
        private readonly ISession nhSession =  HibernateSessionManager.Instance.GetSession();
    }

    [WebMethod]
    public Bar[] GetFavoriteBars(string someParam, int? onceMore){
        return daoFactory.GetBarDao().GetFavoriteBars(someParam, onceMore); //returns a Bar[]
    }
}

and we abstract the daobehaviour, or just use the nhsession directly, exposed as a webmethod.

Now from the WinForm application all you need to do is Add a WebReference which makes all necessary changes to configuration but also generates all necessary classes (in this example, it will create a Bar class as the web-service exposes it).

namespace WinFormK
{
    public class KForm(): System.Windows.Forms.Form
    {
        public void Do()
        {
            var service = new FWS.FooService();
            string filePath = "C:\\temp\FooData.xml";
            Bar[] fetched = service.GetFavoriteBars("yes!", null);

            //lets write this to local storage
            var frosties = new XmlSerializer(typeof(Bar));
            TextReader reader = new StreamReader(filePath);

            try
            {
                var persisted = (T)frosties.Deserialize(reader);
            }
            catch(InvalidOperationException)
            {
                //spock, do something
            }
            finally
            {
                reader.Close();
                reader.Dispose();
            }
        }
    }
}

there are certain things you have to take note to:

• You essentially lose lazy stuff, or at least you lose it in your winform application. The XML serializer cannot serialize proxies and as such you either turn of lazy fetching on those collections/properties or you use the [XmlIgnore] attribute which in turn do what it implies on serialization.
• You cannot return interfaces on the WebMethod signatures. They have to be concrete classes. So, returning IList<Bar> will have to be transformed to List<Bar> or something of the like
• The webservice is executed by IIS and is visible from a web browser. By default, only local browser requests will be served (but that can be changed) so you can test your data access layer separately of what your winform does.
• The receiving end (winform app) has no knowledge of NHibernate whatsoever.
• In the example above i've kept the same name for the dao-methods for the web-methods; As long as you didn't keep nhibernate--specific methods in your dao's (lets say like a NHibernate.Criterions.Order parameter) you will probably find no problem. In fact you can have as many .asmx classes in your webservice as you want, probably even 'map' them to the corresponding dao's (like public class FooService : WebService, public class BarService : WebService, public class CheService : WebService where each corresponds to a DAO).
• You will probably have to write some kind of polling method between your endpoints to keep your presented data fresh.
• WebService data is verbose; extremely so. It is advisable to zip them or something before sending them over the wire (and maybe encrypt them as well)
• the win application only knows a configuration entry: http://server/FWS/FooService.asmx
• Webservices have Session disabled by default. remember that before starting using the session for user data.
• You will probably have to write some kind of authentication for the webservice

• In the example above i am returning a Bar[] with Bar being mapped with nhibernate. More often than not this may not be the case and you may be required to write an auxiliary class WSBar where it adapts the original Bar class to what the webservice and the winform application can consume. This class is actually just a data carrier. Again this depends on how much integration exists with your domain classes and nhibernate as well as how muxh complicated your classes are: Certain data structures cannot be serialized by default.

• This model may not suit what you have already done with your application

Answer 3

I think it's hard to do : it's like you don't want a user of stackoverflow to know his password. A user can always trace his network traffic and see the user/password (you can had an encoding, but it still won't be 100% sure I think).

I think that you should add a webservice between your user and your database with a unique id for each user.

Answer 4

This is why database desktop apps suck. There is no good way to slice it. Best bet would be to use stored procedures or web services. Basically, another layer that can be locked down and control access to the database.