Secure database connection. DAL .net architecture best practice

Question

We have several applications that are installed in several departments that interact with database via Intranet. Users tend to use weak passwords or store login/password written on a sheets of paper where everybody can see them. I'm worried about login/password leakage & want to minimize consequences. Minimizing database-server attack surface by hiding database-server from Intranet access would be a great idea also.

I'm thinking about intermediary data access service method-based security. It seems more flexible than table-based or connection-based database-server one. This approach also allows to hide database-server from public Intranet.

What kind of .net technologies and best practices would you suggest?

Thank in you in advance!

Answer 1

Depending on the type of access your users have to the database, you might be looking at a significant uphill battle. Before you start layering additional security on the intermediaries, start by taking a long in-depth look at what you are allowing your users to do with their credentials:

1. Do users have user accounts on the DB machine? Remove these if possible
2. Are users granted least privileges access? Using whatever mechanisms you deem appropriate remove as much of the users [exec] rights as possible. One thing I have seen on the extreme end of this is to have stored procs that actually had the appropriate exec rights and the user only had exec rights on the procs.
3. Have you removed user rights to run any table altering commands? Including the ability to run system stored procedures. This can cut down on incidents (accidental and malicious) substantially
4. Do users have direct access to raw business data? If so think about moving access into views, and sps and removing user rights on the tables themselves. It solves some maintenance headaches as well
5. Do you have auditing in place? Table level or custom.

If you feel you have a solid design there, then you can start looking at wrapping your DB inside of a broker/facade for access. Beware that this can be costly in terms of performance, deployment, and security and is not an easy thing to do. A few suggestions on patterns for this:

1. Look into a service broker: WSO2 for instance, and their are others which can be used to hide critical business apps (though its intention is to publish access to them) by providing caching and proxy services to them. It might reduce your attack surface some, but configuration and management will easily overtake any gains there.
2. You could roll your own broker services (based on WCF, or the like) you will need to ensure that you adequately take into account load, and projected growth but it is not impossible or even unobtainable if you really want to do it and have the time to design it out correctly.

Answer 2

I do not believe it is necessary to secure the database by moving it to an outside network. Rather, you can solve these practices by limiting the privileges of the accounts themselves. The data access should not be user specific, and instead leverage a system account with an encrypted connection string configured in the web or app config file.

User authentication and authorization should be handled separately from DB access.

The system DB account itself should ONLY have privileges to perform the tasks necessary to operate the system. This means only granting access to the procedures that the application executes, and possibly read access to any tables or views read through LINQ to SQL.