A Secure Way of Building Connection Strings - Building a ConnectionString using the class
(Page 4 of 5 )
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
Next: Entering valid connection information >>
More MS SQL Server Articles
More By Jayaram Krishnaswamy
/ 6 
