Store the ConnectionString

Question

Hi i am trying to find the best way (or a good one) to store a ConnectionString (my App will connect to a Database in a Server, it will work with it continuosly) and how to handle with the Admin account. For default all the applications with Login must have an Admin account, right?

I think in that way...

So, you install an App on the Machine, how you will configure the ConnectionString? I think that is wrong asking the user to configure such thing if doesn't understand what it is or it really have to be?

And imagine one day that the ConnectionString have to be changed for any reason, if the Admin acoount credentials to Log in the App are in a Table in the Server, how then it will possible to Log in the App to change the ConnectionString?

This is my problem now... I don't know how this type of things is handled, if are there any rules to handle this, any common way because i am starting to work more seriously with this type of things.

Note: I think that is not important but my App is being developed in WPF.

Answer 1

A really easy way to do DB access security with MS SQL Server is use "Integrated Security = SSPI". With that, MS SQL server authenticates against the Windows user entity under which the database accessing process runs. It all happens seemlessly using MS SQL Windows Authentication without the need to store any usernames or passwords within the application.

Another way to do it is to distribute a config file with the database connection string in it, with at least the password encrypted.

We have many MySQL databases on several servers too, so MS SQL SSPI doesn't cover us.

For server side software I store database connection strings and credentials in a separate XML config file that all the server side software uses. The credentials are arranged topically with all database information encrypted. I use a common file for all the server processes and developed a little encryption / DB config file management GUI so management is simplified and access is universal to all the software using the same code.

For end user software it is too difficult to maintain with local config files because of user maintenance. For "fat client" software I maintain a separate database with encrypted credentials on a server in our DMZ that is universally accessible to anyone havinng that software (there is not that much distributed fat client software.) Our web app software has a consolidated user database that controls access for all users/roles for all apps, and everything is portaled through the one system so it is far easier to mantain the user database.

Frankly, the polyglot system is increasingly ugly to maintain. I would use our main domain LDAP server (Windows ADS), but our company policies keep ALL domain member servers completely firewalled off from access outside our most trusted VPN and thus inaccessible in too many circumstances.

I hope someday to have time to setup a *nix based LDAP server in our DMZ and centralize all credential information there.