How to set up a secure database connection

Question

Similarly, but not the same:

• How to securely store database connection information
• Secure database connection in app

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

Now I have this problem: the user running the application does not need to know the username / password of the shared database user, because I do not want him to rummage through the database directly.

Alternatively, I could tightly bind 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.

Until then, I have found three possibilities:

• In the first question asked, he usually answered , making the file available only to the user running the application .

  But this is not enough in my case (the user running the application is human. The user / database pass is common and should not even be accessible to humans.)

• The first answer additionally suggested encrypting the connection data before writing it to a file .

  With this approach, the administrator will no longer be able to configure the connection string, because he cannot encrypt it manually.

• The second reference question provides an approach for this scenario itself, but it seems very complicated.

My questions to you:

• This is a very common problem, so there is no general “how-to-do this” way, somehow a “design pattern”?

• Is there any support in the .NET config framework?

• (optional, possibly out of scope) Can I easily combine this with the NHibernate configuration mechanism?

Update:

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

• (N) Hibernation can only be used with a database, not with webservices (am I right?)
• We plan to provide autonomous capabilities, i.e. if the database or network should be inaccessible, the user can continue his work. To manage this, I think about having a local database in proc, for example. SQL Server Compact and using MS Sync infrastructure to synchronize with the server database as soon as it reappears.

Do you have any further ideas considering this?

Answer 1

In fact, the WebService approach (mentioned in another answer) means that you are moving NHibernate and its logic to a web service. WebService then provides the db functionality available to the application using WebService methods.

There is only one user in the database that uses WebService, and if you want the application user to have different db privileges, you abstract it from the WebService level.

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

For battery life, it comes down to providing a reliable way to save data to local storage and provide a synchronization method via WebService

I really did this using a web service that exchanged data with the database and the WinForm application (.NET Compact Framework), which spoke only with the web service, and in the absence of a cellular network, it serialized the changes on the memory card (the data was not important , so for my case, obscure / obscene security measures if they are not taken)

UPDATE with a small example as requested (I'm really weird if I ask for an example)

You have configured your domain classes and nhibernate configuration and (for example) your repository materials in a project such as ASP.NET WebService Application. For simplicity, I will have only one Foo web services class (in Foo.asmx.cs), as well as one Bar domain class

so you get this (actual implementation changes):

 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 daobehaviour or just use nhsession directly, exposed as a web method.

Now, from the WinForm application, all you have to do is Add a WebReference , which makes all the necessary changes to the configuration and also generates all the necessary classes (in this example, it will create the Bar class, since the web service provides 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 some things you should take note of:

• You essentially lose lazy stuff, or at least lose it in your winform application. An XML serializer cannot serialize proxies and, as such, you either rotate the lazy selection in these collections / properties or use the [XmlIgnore] attribute, which in turn does what it implies when serializing.
• You cannot return interfaces in WebMethod signatures. They must be concrete classes. So the return of IList<Bar> should be converted to List<Bar> or something like that
• The web service is run by IIS and displayed in a web browser. By default, only local browser requests will be served (but this can be changed) so that you can test your data access level separately from what your winform does.
• The receiving side (winform application) does not know NHibernate at all.
• In the above example, I kept the same name for dao methods for web methods; Until you store the nhibernate-specific methods in your dao (say, as the NHibernate.Criterions.Order parameter), you probably won't find any problems. In fact, you can have as many .asmx classes in your web service as you want, maybe even “map” them to the corresponding Tao (for example, public class FooService : WebService , public class BarService : WebService , public class CheService : WebService , where each corresponds to a DAO).
• You may have to write some kind of polling method between your endpoints to keep your data fresh.
• WebService data is detailed; extremely. It is advisable to fix them or something before sending them over the wire (and, possibly, also encrypt them).
• the win application only knows the configuration entry: http://server/FWS/FooService.asmx
• Web Services has a disconnected session by default. remember that before you start using a session for user data.
• You may have to write some authentication for webservice

• In the above example, I am returning Bar[] with Bar mapped to nhibernate. Most often this may not be the case, and you may need to write a helper class WSBar , where it adapts the original Bar class to the needs of the web service and winform application. This class is actually a data carrier. Again, this depends on how much integration exists with your domain and nhibernate classes, as well as how muxh complicates your classes: some default data structures cannot be serialized.

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

Answer 2

First of all, allowing untrusted users to connect to the database is usually not a good idea. So many things can go wrong. Put a web service between them.

If you absolutely need to do this, make sure that it does not matter, even if they get a username and password. Limit your privileges in the database so that they can only execute a few stored procedures that have built-in security checks.

Whatever you do, you cannot specify the username / password of the privileged user to an unreliable person. He just asks for trouble. No matter how well you try to hide your credentials in an encrypted line inside a binary or something else, there is always a way to find them. Of course, whether someone will actually do this depends on how interesting your data is, but quietly hoping that people with debuggers will just leave you alone is not a good security measure.

Answer 3

It seems to me that this is difficult to do: it looks like you do not want the stackoverflow user to know his password. The user can always monitor his network traffic and see the user / password (you may have an encoding, but he still will not be 100% sure what I think).

I think you should add a web service between your user and your database with a unique identifier for each user.

Answer 4

This is why desktop applications for the database suck. There is no good way to cut it. The best bet would be to use stored procedures or web services. In principle, another level that can be blocked and control access to the database.