Reputation: 13659

Establishing database connection

First, forgive my english.

My group and I are planning to do an application. This application can be installed to other machines, and should connect to a server and the database is password protected.

As a student, we always do this in a naive way:

SqlConnection myConnection = new SqlConnection("user id=username;" + 
                                       "password=password;server=serverurl;" + 
                                       "database=database; " + 
                                       "connection timeout=30");

Always hardcoded.
What if we change the password of the database, or chage our server?
We have also to change the values in our code, recompile and reinstall the application in the pc. Is there something dynamic way of doing these?

We are thinking that in the first run of the application, the user will be prompted for the connection details and save that data into a file where the application will fetch it everytime it starts and use it for database connection, but there's a password involved.

Any suggestion, ideas, concepts, samples, etc...? How to do it in more professional way? Please help... Thanks.

Upvotes: 0

Answers (4)

Pilgerstorfer Franz

Reputation: 8359

There are several ways to do this. First off all you may save your connectionString in an app.Config/web.config file. Your connection objects may access this string by using

PROJECTNAME.Properties.Settings.Default.YOURCONNECTIONSTRINGNAME

Your app.config file may look something like this

<?xml version="1.0" encoding="utf-8" ?> <configuration> <configSections> </configSections> <connectionStrings> <add name="Winforms_Demo.Properties.Settings.dbNordwindConnectionString" connectionString="Data Source=(local)\SQLEXPRESS;Initial Catalog=dbNordwind;User ID=sa" providerName="System.Data.SqlClient" /> </connectionStrings> </configuration>

As you can see this possibility still saves any user credentials hardcoded (although you may change them by manually editing the config.file (even after compiling). You may create such a config file by adding a new datasource to your project (e.g. sql server datasource). The wizard will then ask where to save your connectionString.

Another possibility will be connectionStringBuilder. This class offers some properties:

SqlConnectionStringBuilder conbuild = new SqlConnectionStringBuilder();
conbuild.InitialCatalog = "dbNordwind"; // database name
conbuild.IntegratedSecurity = false;  // true if you use winAuthent
conbuild.UserID = "sa"; // e.g get this info by showing a authent form
conbuild.Password = "123";
conbuild.DataSource = "servername";
SqlConnection con = new SqlConnection(conbuild.ConnectionString);

Using this method you may even access a file and read any required data. In this case you have to look into security measures for your file!

Securing your file may be done by encrypting it (System.Security namespace) or saving data into any isolatedStorage (user specific - windows security will be used) or by using "aspnet_regiis -pef" to crypt any config-file.

Upvotes: 0

Marcelo Cantos

Reputation: 186118

Windows lets you encrypt files, so that only processes running as the owner can read them. You could store the passwords in a file and encrypt it. See File.Encrypt on MSDN.

This would only be one factor in the security model. You probably also want to encrypt the file at the application level so malicious software that the users run doesn't sniff around for passwords.

Upvotes: 0