Storing database connection settings in .NET

Where does it go?
Connecting to a database requires knowledge of the database server, as well as the username and password to use when accessing it. Also, you can specify parameters such as which database to use based upon your database platform.
The following VB.NET code demonstrates a sample connection string for working with SQL Server:

Dim sConn As String
Dim conn As SqlConnection
sConn = "server=(local);Initial Catalog=Northwind;UID=tester;PWD=123456"
conn = New SqlConnection(sConn)
conn.Open()

Here's the C# equivalent:

string sConn;
SqlConnection conn;
sConn = "server=(local);Initial Catalog=Northwind;UID=tester;PWD=123456";
conn = new SqlConnection(sConn);
conn.Open();

This code works but there are potential problems. First, including database connection information in your code is a security risk; a malicious user could use this information to gain access. Also, problems may arise if the connection string changes—you must recompile the code.
For these reasons, developers often choose to store database connection information outside of the code. There are many options for accomplishing this task, but two popular choices are using an XML configuration file or utilizing the Windows registry.
Choosing XML
Storing database connection properties in an XML file allows you to change the settings without recompiling the code. It's easy to edit the XML file with any standard text editor. A good example of this approach is utilizing the XML-based web.config file in an ASP.NET application.
This file allows you to add custom elements (which are accessible from the code) to the application. In addition, this file isn't viewable from a Web browser so its contents remain hidden. You can add new items using the add element with key and value attributes. The following sample web.config shows a possible way to add the connection:

<configuration>
<add key="dbconnection" value=" server=(local);
Initial Catalog=Northwind;UID=tester;PWD=123456"/>
</configuration>

With this data stored in the web.config file, you can use it in your code with the ConfigurationSettings class. The following VB.NET reads the connection string from the application's web.config file. It uses the AppSettings property and passes the key to be read to it.

Dim sConn As String
Dim conn As SqlConnection
sConn = ConfigurationSettings.AppSettings("dbconnection")
conn = New SqlConnection(sConn)
conn.Open()

Here is the code in C#:

String sConn;
SqlConnection conn;
sConn = ConfigurationSettings.AppSettings("dbconnection");
conn = new SqlConnection(sConn);
conn.Open();

You may also use this straightforward approach with a simple XML file (for a non-ASP.NET application). The problem is that it's still open to security threats. If a malicious user gains access to the server, the web.config file is a simple text file that they can easily read to gain database access.
To provide tighter access control, many developers utilize the Windows registry to store connection information. With the information in the registry, it's easy to read it in the code.
Choosing the registry
The Microsoft.Win32 namespace provides everything necessary to interact with a Windows registry. You can use its CreateSubKey method to create registry entries.
The following C# snippet will do the trick. It uses the Registry class to access a machine's Windows registry, and it uses the RegistryKey class to work with individual elements within the registry.

Using Microsoft.Win32;
class Class1 {
static void Main(string[] args) {
Registry.LocalMachine.CreateSubKey("SOFTWARE\\MyApp\DBConn"];
RegistryKey regKey;
regKey = Registry.OpenSubKey("SOFTWARE\\MyApplication\\DBConn");
if (regKey != null) {
regKey.SetValue("Server","(local)");
regKey.SetValue("Database","Northwind");
regKey.SetValue("UserID","tester");
regKey.SetValue("Password","123456");
} } }

This is the VB.NET equivalent:

Imports Microsoft.Win32;
Module Module1
Sub Main()
Registry.LocalMachine.CreateSubKey("SOFTWARE\\MyApp\\DBConn")
Dim regKey As RegistryKey
If Not (regKey Is Nothing) Then
regKey.SetValue("Server","(local)")
regKey.SetValue("Database","Northwind")
regKey.SetValue("UserID,"tester")
regKey.SetValue("Password","123456")
End If
End Sub
End Module

This approach is inherently more secure since a person will need full server access to work with its registry. With the necessary values stored in the registry, you may utilize them to connect to the database. You can perform this with the GetValue method of the ResourceKey class.

using Microsoft.Win32;
using System.Data.SqlClient;
class Class1 {
static void Main(string[] args) {
RegistryKey regKey;
string server, db, uid, pwd
string sConn
SqlConnection conn;
regKey = Registry.LocalMachine.OpenSubKey("SOFTWARE\\MyApp\\DBConn");
if (regKey != null) {
server = regKey.GetValue("Server").ToString();
db = regKey.GetValue("Database").ToString();
uid = regKey.GetValue("UserID").ToString();
pwd = regKey.GetValue("Password).ToString();
sConn = "server=" + server + ";Initial Catalog=" + db + ";UID=" + uid + ";PWD=" + pwd;
conn = new SqlConnection(sConn);
conn.Open();
} } }

This is the VB.NET equivalent:

Imports Microsoft.Win32;
Imports System.Data.SqlClient
Module Module1
Sub Main()
Dim regKey As RegistryKey
Dim server, db, uid, pwd As String
Dim sConn As String
regKey = Registry.LocalMachine.OpenSubKey("SOFTWARE\\MyApp\\DBConn")
If Not (regKey Is Nothing) Then
server = regKey.GetValue("Server").ToString()
db = regKey.GetValue("Database").ToString()
uid = regKey.GetValue("UserID").ToString()
pwd = regKey.GetValue("Password").ToString()
sConn = "server=" + server + ";Initial Catalog=" + db + ";UID=" + uid + ";PWD=" + pwd
conn = New SqlConnection(sConn)
conn.Open()
End If
End Sub
End Module

Peppering the registry calls throughout an application is tedious--especially if you need to change anything. Many developers place these calls in the Global.asax (for ASP.NET applications) or create a special class for it. Note: Editing the registry is risky, so make sure you have a verified backup before making any changes.