Store the ConnectionString

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 ConnectionStr开发者_开发百科ing? 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.

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.

With connection strings to a MS SQL server you basically have two options:

1. Either store a username/password pair in it. Your application has to be able to read it, so theoretically, any user of the application could do the same (he has access to your application, so he can decompile it). Practically, you can encrypt the connection string, with a key stored in your source code or application resources and be quite confident that your users won't be able to read the password.
2. If you have some users that should be able to use the application and you believe them, and other users that may have access to your application but that shouldn't be able to use it, you can use Windows Authentication, and set Integrated Security=True in your connection string. This assumes you configure your DB, so that the approved users have access to it.

Either way, your connection string should be bundled with the rest of the application. If you ever need to change it, you just release a new version. (This works even for the most primitive version of installation: “copy this bunch of files to a directory on your computer”).

Now, the connection string can be stored pretty much anywhere, the most convenient place probably being application settings file.

If you want to be certain that your users will be able to use only the functionality provided by your application and not access the database directly, you'd have to write something like a web service and connect to the database only indirectly through that.

If you need to store the connection string in a configuration file or somewhere else of your choosing, you might want to encrypt and base 64 encode the encrypted results back into a string. Check here for an Encrypt() and Decrypt() implementation.

It is possible to manipulate files (such as the .config file) during the install of the application. So it is acceptable to ask the user/admin (during setup) for the name of the server (and even for a user name and password provided you encrypt these but I'd rather use integrated security).

For updates of the connection string just make a new installer.

Alternative: store the connection string in active directory or in an other well-known store so all you need to do is change the value in one place.