A Secure Way of Building Connection Strings

Introduction

The connection string is built using the name/value pairs stored in a secure location by concatenating the individual name/value pairs, the attributes, together. Sometimes parts of the connection string may come from the information entered by the user in some kind of a dialog box. In the earlier ADO.NET 1.1 version you could just concatenate the individual name/value pairs, which sometimes can result in a run time error because the connection string is not parsed at compilation.

The other serious objection was the process of building the connection string. Pure concatenation easily gave access to spurious insertions -- the SQL injection. This is where the string builder class comes in handy. Simply stated, this class takes user input and comes up with a tamper-proof connection string. The connection string builder greatly reduces injection, thereby providing increased security. However for each of the data providers you will need a different builder because the connection information is different. The present tutorial looks at the SqlConnectionStringBuilder for MS SQL Server.

Connection Strings

Connection string formulation depends on the database product. The MS SQL Server 2005 connection string may use a different set of name/value pairs than, say, Oracle Server 10g. While constructing and managing the string is one of the concerns, securing and protecting it is another. Connection string key/value pairs are the same as those used in the earlier version with a couple of additional key/value pairs added in ADO.NET 2.0. They are not case sensitive.

The reader is advised to look up some 20 or so Key Value pairs used in SQL 2005 server at MSDN. You will come across these later in this tutorial while reviewing the Object Browser. Typical information contained in the connection string includes the Server (or the Data source), UserID, Password, and the Database. It may also include other related information such as the connection timeout, whether asynchronous processing is allowed or not, and so forth.

Overview of the tutorial

The tutorial consists of two parts. In part 1, the SqlConnectionStringBuilder class will be introduced and the class members will be reviewed with an example. The typical connection used to access the SQL 2005 Server will be used. In part 2, another example will be used to create a connection string by supplying user inserted information in a form. The connection to SQL Server will be opened and closed to verify the correctness of the connection string. To test this, a user will be created in the SQL server with dbo permissions to a database on the server.

Reviewing member properties of the class

Start a Windows application project, CStringBuilder, which adds the default Form1 to the project. Form1 was renamed Properties.vb in this tutorial. The next picture shows the project consisting of the two forms Properties.vb and WriterConnect.vb. Proper references have been made for the System.Data and other classes.

Before proceeding any further it is instructive to look at the Object Browser to review some of the features of the SqlConnectionStringBuilder. The next picture shows the SqlConnectionStringBuilder class which is hierarchically related to System.Object. It has two class constructors, one of those taking in a string as an argument. There are a large number of properties and a couple of methods in this class.

System.Object 
System.Data.Common.DbConnectionStringBuilder 
System.Data.SqlClient.

SqlConnectionStringBuilder

Reviewing properties using an example

Add a command button to the Properties.vb form. Insert the contents of the following listing to the click event of the button as shown. The code is adequately commented to explain the various statements in this listing. An existing connection string provides the argument for the constructor of the class, and then looks at the default values of other related properties of the string. Then a complete string is provided and the constituent key/value pairs are accessed programmatically.

Imports System
Imports System.Data.SqlClient
Imports System.Collections Public Class Properties Private Sub 

Button1_Click(ByVal sender As System.Object, _ ByVal e As System.



EventArgs) 

Handles Button1.Click 'Instantiate a SQL connection string builder 
'called() 'myBuilder' with an argument that is
'provided by the function, GetConnString() Dim myBuilder 

As New _
SqlConnectionStringBuilder(GetConnString()) 'print out the 

connection string Debug.Print(myBuilder.ConnectionString) ' provide

an existing connections string to 
' SqlConnectionStringBuilder and you can retrieve and
' modify any of the elements. myBuilder.ConnectionString = _
"server=(local); user id=sa;" & _
"password=XXXXXXX; initial catalog=Northwind"
Debug.Print ("Password: " & _
myBuilder.Password & vbCrLf)
Debug.Print ("Security Info Persist: " & _
myBuilder.PersistSecurityInfo & vbCrLf)
Debug.Print ("Connection Timeout: " & _
myBuilder.ConnectTimeout & vbCrLf)
Debug.Print ("Current Language: " & _
myBuilder.CurrentLanguage & vbCrLf)
Debug.Print ("Initial Catalog: " & _
myBuilder.InitialCatalog & vbCrLf)
Debug.Print ("Asynchronous Processing(yes/no): " & _
myBuilder.AsynchronousProcessing & vbCrLf)
Debug.Print ("Data Source: " & _
myBuilder.DataSource & vbCrLf) End Sub  Private Function 

GetConnString() As String
Return "Server=(local); Integrated Security=SSPI;" & _
"Initial Catalog=Northwind" End Function
End Class

The coding is made easier since you have complete intellisense support by way of a drop-down listing of object related information as shown in the next picture.

The Debug.print output from the code is run when the button is clicked, as shown in the next paragraph. The password came out in clear text, which has been replaced by 'XXXXX' in this document.

Data Source=(local);Initial Catalog=Northwind; Integrated Security=True
Password: XXXXXX Security Info Persist: False Connection Timeout: 15 

Current Language: Initial Catalog: Northwind Asynchronous 

Processing(yes/no): False Data Source: (local)

Building a ConnectionString using the class

Creating a new login in SQL 2005 Server

In the SQL 2005 Server management Studio, right click on the Logins folder in the Security node to create a new login. Create a new login, TechnicalWriter as shown.

TechnicalWriter can log in with SQL authentication with a Username and a Password. He has dbo permissions for the Northwind Database. TechnicalWriter has Login enabled and has Permission to connect to the database granted as shown.

Building a Connection String for the user TechnicalWriter: User interface for inserting the connection string information

Add a new form to the project, which is renamed WriterConnect.vb here. Add a number of controls as shown in the picture below where a user can insert the different values for the necessary items in the connection string. The password text box has the PasswordChar property set to show a * whenever some character is inserted. This will only show in the display, but the characters themselves will be present in the ConnectionString. Although various validation rules can be set up, there are no other items configured.

The next picture shows the WriterConnect.vb form in design. The user will insert the values and then hit the button Create a Connection String. The connection string will be displayed in the box below, and an attempt will be made to connect using that string. The success or failure will be reported in the message box, and some message boxes will be shown in the code listing.

The code listing for this form

The code listing for this form is shown in the next paragraph in the click event of the button Create a Connection String. Here a new SqlConnectionStringBuilder is instantiated without any arguments. The user input to the form is used by the builder to build the string. After the string is built the connection will be opened to verify its state (Open=1, Closed=0). If there is a problem, a message will be given to the user in the message box.

Imports System
Imports System.Data.SqlClient
Imports System.Collections
Public Class WriterConnect Private Sub Button1_Click(ByVal sender As 

System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click 

'Instantiate a New() SqlConnectionStringBuilder.  Dim mybuilder As New 

SqlConnectionStringBuilder()
 'transfer User input to the builder mybuilder.DataSource = TextBox3.Text
mybuilder.UserID = TextBox1.Text
mybuilder.Password = TextBox2.Text
mybuilder.InitialCatalog = TextBox4.Text 'Debug.Print 

(mybuilder.ConnectionString & vbCrLf)
'Debug.Print (mybuilder.ConnectTimeout) 'Print connection string 

to the user form TextBox5.Text = mybuilder.ConnectionString Try Dim 

myConn As New SqlClient.SqlConnection _
(mybuilder.ConnectionString)
myConn.Open()
MessageBox.Show(myConn.State)
MessageBox.Show("Connection Open")
myConn.Close()
MessageBox.Show(myConn.State) 'Print to Message box on User 

form if connection is OK TextBox6.Text = "Connection is OK" Catch ex As 

System.Data.SqlClient.SqlException 'MessageBox.Show ("This is the 

message:  " & _
'vbCrLf & ex.Message) ''Print to Message box on User form if 

connection is NOT OK TextBox6.Text = ex.Message  End Try End Sub
End Class

Entering valid connection information

The TechnicalWriter is typing in the correct values for the various items on the ConnectionString builder form as shown. When he clicks the button he will see the following display. You can see that the clear text password is easy to read.

User types in wrong UserID

In this case, while he types in all other information correctly, he types the UserID incompletely as shown. Of course there is no user called Technical in the server.

User types in the server name incorrectly

In this case he incorrectly types in the server name which has the syntax Domain/Server. All other information is correct. He sees the following display. Notice the spelling error in the SQL Server. The error message is the most frequent one that you see, which does not really show the exact error that might have occurred, although in this case it points to failure due to locating the server. The complete message is shown here:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Summary

This SqlConnectionStringBuilder class will generate a connection string whose syntax and key/value pairs are checked against the database and invalid Key/Values or nested Key/Values will throw an exception. The SqlConnectionStringBuilder class simplifies the management and security of connection strings for connecting to MS SQL Servers. SqlConnectionStringBuilder can also be used for picking up the information from the configuration file. Similar classes exist for other default data providers such as Oracle. The builder can also be used to parse and modify the connection string to suit the needs of the application.