Where to save SQL Server connection string in a client console application?

Question

I am creating a windows console application to monitor the attendance of users. When a user is logging in to his computer(connected to network) the windows login time should be updated in an SQL Server database.

All the client computers are connected using network.

The question is where will I save the database connection string to the server? If I save the connection string (encrypted) in the console application which is running in the client application, it will create problem if there is a change in the server name/username/password after few months.

Please advice the best approach for this problem.

Answer 1

You could indeed store it in a application configuration file (see @NoOne's answer for more details). However, mind that you should probably not store the username and password there.

You have the following options:

(a) encrypt the connectionStrings configuration section. That has some administrative "issues" as you have discovered YMMV.

(b) don't store the username and password, but query them from the user during runtime; optionally allowing them to be specified as command line parameters - although that has its issues of its own: the parameters (and thus username/password) would be visible using process viewer tools like Task Manager or Process Explorer.

(c) see if you can change the connection credentials from username/password to integrated security (thus you don't need to put a username/password in the connnection string and thereby not in the configuration file).

Option (c) is the best. It removes the necessity of a username (and more importantly) password altogether. However, your application (and environment) must be prepared for that (more here).

If you go for option (b), you could specify the connection string in the configuration without the User and Password keys. You would let the user specify them and then buildup a real connection string to use further on by utilizing the SqlConnectionStringBuilder (or DbConnectionStringBuilder) class:

 var builder = new SqlConnectionStringBuilder(
    ConnectionManager.ConnectionStrings["test"].ConnectionString);
 builder.UserID = /* Username specified by user */
 builder.Password = /* Password specified by user */

 string realConnectionString = builder.ConnectionString;