4

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.

James Stephan
  • 113
  • 2
  • 6
  • 1
    Have the client console applications send the data to something running on a server. Have the server application talk to the database. That way, maintenance only has to be done on that server, not every client. – Dan Bracuk Aug 26 '13 at 12:20
  • @DanBracuk True, but the fundamental issue remains: the client should have some "identity" that needs to be validated by the server before actually carrying out any request. – Christian.K Aug 26 '13 at 12:34
  • @Christian.K, The client identity can be verified using the client's windows user name. – James Stephan Aug 26 '13 at 12:41
  • In principle yes, see "Integrated Security" option in my answer. However, if you want to take a different architectural approach (like hinted at by @DanBracuk rightfully so), you should ask a new question. – Christian.K Aug 26 '13 at 12:45
  • @Dan Bracuk , Can you please suggest anything on "something running on a server" – James Stephan Aug 26 '13 at 12:53

2 Answers2

4

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;
Community
  • 1
  • 1
Christian.K
  • 47,778
  • 10
  • 99
  • 143
  • As you said Option(c) is the best. But in my scenario I have client computers not less than 3000. Giving a read access to my table for all the users wont be a good option I think. – James Stephan Aug 26 '13 at 13:30
  • 1
    You could put them into Windows groups and grant those access. At least that would make it more managable. But as already commented on the question itself, you might want consider a different approach altogether: in short, provide an application service that handles the actual database work on behalf. You would then authenticate against this service using the (3000) user accounts. The service itself would authenticate against the SQL Server instance using a (single) technical user. – Christian.K Aug 26 '13 at 15:37
3

you should add app.config file to your project and store the connection string in it.

Project-->Add->New Item-->Application Configuration File

Add connection string as

<connectionStrings>
  <add connectionString="test" name="test"/>
</connectionStrings>

read it as

string connectionString = ConfigurationManager.ConnectionStrings["test"].ConnectionString;

But what about password being stored in the connetion string? Infact your whole connection string should be encrypted and considering that MS has provided a built-in solution for that as well. You need to look at this for Section protection.

Note: You will have to add reference to System.Configuration in your project

Ehsan
  • 31,833
  • 6
  • 56
  • 65