Working with Connection Objects

A connection is essentially a pipe through which other objects communicate with the database. The connection takes care of data-translation issues for you according to properties you provide to a Connection object, usually at the time you create it. VS.NET ships with two types of connection classes. The OleDbConnection class is a generalized class that works with several popular ODBC databases, including Access (Jet), SQL Server, and Oracle. Microsoft offers a separate download for an OdbcConnection class that works with the OLE DB provider for ODBC databases. An equivalent class, called SqlConnection (pronounced "sequel connection" rather than "S-Q-L connection"), works specifically with SQL Server.

If you're querying a SQL Server database, you should use the SqlConnection class, because it's considerably faster than the generalized OleDbConnection class. This duality—support of both generalized OleDb classes and optimized SQL classes—exists throughout the System.NET namespace. Every applicable OleDb class has an equivalent SQL class counterpart, meaning you can (almost) seamlessly switch to SQL Server and the SQL classes and get a performance boost without changing anything else in your application.

With that said, there are some important differences. I'll reiterate these throughout the chapter, so if they don't make complete sense at the moment, bear with me. Briefly, here are the major differences:

■ You must specify a provider to connect to a database with an OleDbConnection object.

■ OleDbConnection objects don't close automatically when they go out of scope. You must explicitly call the Close method or the Dispose method to close the connection. In contrast, SqlConnection objects close automatically when they go out of scope; in other words, SqlConnection objects act like other .NET objects, whereas OleDbConnection objects act like COM objects. That's because the OleDbConnection object is a .NET wrapper for COM-based OLE DB providers, but the SqlConnection object communicates directly with the SQL Server API (which also explains why the SQL classes are faster).

I'll use both versions in the remainder of this chapter, so you'll feel comfortable with either set, but you should keep the SQL Server-only version in mind. Feel free to substitute one for the other in these examples as you wish.

You use a Connection object to connect to a specific database. If you're querying multiple databases, you need multiple connections. However, you can reuse a connection to a single database multiple times. If you want to switch your Connection object to a different database, you must close it and then open it again. Connecting to a database and "opening" a database are not quite the same thing. You connect to a database by specifying a connection string. A connection string, rather obviously, is a string that consists of several fields. Like CSS values, the connection string consists of key=value pairs separated by semicolons. For example, the following OleDb connection string contains four key=value pairs:

Provider=SQLOLEDB; Data source=localhost; initial catalog=Northwind;user id=sa

Required Connection String Fields

For every connection string, you must specify the following:

The Provider (OleDbConnection only Here's one difference between the OleDbConnection and SqlConnection classes. When you use an OleDbConnection object, you must specify a provider. In contrast, when you use a SqlConnection object, you don't have to specify a provider because the classes "talk" directly to the SQL Server API.

The Data Source The location of the database or database server. For example, if your database server is on the same machine as your development environment, you could use the name localhost. If the database is remote, you must provide the name or IP address of the database server.

The Initial Catalog A catalog is the same thing as a database, so all the term initial catalog means is that you must specify the name of the database you want to query. Examples are Northwind and pubs. Strictly speaking, the catalog isn't required for SQL Server; but if you don't specify a catalog, SQL Server uses the "master" database, which probably isn't your intention.

Security Information There are several approaches to security. You can pass an explicit user ID and password, or you can use the Integrated Security (a.k.a. Trusted_Connection) setting to specify that the connection is secure. The Integrated Security field accepts one of three values: true, false, or sspi (Security Support Provider Interface). In ADO.NET running on Windows, there's no difference between specifying Integrated Security=true and Integrated Security=SSPI. SSPI is essentially a buffer between your application and the various security protocols used by data and service providers, such as databases or Web servers. Your application uses SSPI to call a security support provider (SSP), which in turn handles the specific security details for each security implementation (such as Kerberos, COM-authenticated RPC, or Secure Channels) for you. Each SSP exposes one or more security package that translates generic SSPI function calls into the format required for a specific security implementation. A detailed look at SSPI is beyond the scope of this book. Fortunately, you don't need to know much about it to use it. In fact, you don't even have to call any SSPI functions to use it against SQL Server. On a server running Windows 2000 or Windows XP, the system automatically loads Kerberos and Windows NT Challenge/Response (NTLM) authentication SSPs when you boot the server. Windows NT systems automatically load only NTLM.

In addition to these three required fields (or four if you use an OleDbConnection object), connection strings may contain several other optional fields, which are as follows:

Application name Passes the name of an application to the server, which may accept or reject connections based on that value.

ConnectTimeout Specifies the length of time the connection will wait for the database server to respond before timing out.

Max Pool Size and Min Pool Size Specify the maximum and minimum number of connections to keep in the connection pool.

Network Library Specifies how to connect to the server. The default Network Library value is dbnssocn, which tells the connection to use TCP/IP, but other values, such as dbnmpntw (Named Pipes) and dbmsrpcn (Multiprotocol) can connect to databases using those protocols. For most installations and for smaller applications, you won't need to worry about any of these values—the defaults will work just fine.

Creating a Connection Object

There are two ways to create connections: by using the VS.NET Data Connection Wizard and by writing the code yourself. I want you to ignore the availability of the wizard for a few minutes and practice writing the connections in code. Not only will you need to recognize the parameter names and settings when you debug applications, but you can't write quick test code in a text editor unless you're familiar with the code itself rather than the wizard alone.

Warning If you're not using SQL Server, you must use the OleDbConnection object (and specify the provider) to complete this chapter. Substitute the OleDb prefix wherever you see a class that starts with Sql—and remember to call Close or Dispose when you're done with the connection.

Warning In the remainder of this book, I've assumed that you have SQL Server installed on your development server or on a server accessible through your network. If you're using MSDE, you should install the SQL Server Developer edition to gain access to the Enterprise Manager application. You can download the Developer edition from

http://www.microsoft.com/sql/downloads. The Developer edition is time-limited, but it will serve to introduce you to SQL Server's capabilities and to work with the examples in this book.

Note The data-access examples in this book use the server name localhost in connection strings. If you're using a remote database server, you won't be able to use localhost. In that case, remember to substitute your own server's name wherever you see localhost in a connection string.

The overloaded Connection object constructor lets you create a new Connection object with no parameters or assign the connection string when you create the object. No matter which version of the constructor you choose, for the most part, you must set connection properties before you open the connection. For example, you can't change the ConnectionString property while a Connection object is open; you must first close the connection, assign the new connection string, and then reopen it.

Assigning a connection string doesn't open the database, but it can throw errors. For example, syntax errors in the connection string itself cause an error immediately; however, if you assign a connection string with invalid field values, you won't get an error until you try to open the connection.

Building a Web Form to Test Connections

The Web Form ch13-1 lets you create simple connection strings containing the required fields (see Figure 13.1).

Figure 13.1 : The Web Form ch13-1 lets you build and test simple connections

To fill out the form, you must select a connection type. The other fields are not required, but you're likely to get an error unless you fill them out. Try clicking the Test Connection button without filling out any of the fields. You'll see an error message stating that the Connection Type field is required. Try filling out the fields as follows:

Field Name

Value

Connection Type

OleDbConnection

Provider

Leave blank

Data Source

localhost

Initial Catalog

Northwind

UserlD

<your SQL Server User ID>

Password

<your SQL Server Password>

Click the Test Connection button. The ConnectionString label displays an error message, and the traffic light icon remains red.

Change the Provider field value to sqloledb and click the Test Connection button again. This time, the ConnectionString label displays the generated connection string, and the traffic light turns green (see Figure 13.2).

Figure 13.2: Web Form ch13-1 after building a successful connection

Figure 13.2: Web Form ch13-1 after building a successful connection

Here's how it works. A RequiredFieldValidator checks to ensure that the user has selected a Connection Type, either SqlConnection or OleDbConnection. That validation happens on both the client and the server (remember that the Validator controls always validate on the server, even if the client supports JavaScript so that the controls can validate on the client).

To simplify the code, the Web Form uses the System.Data.SqlClient and System.Data.OleDb namespaces:

using System.Data.SqlClient; using System.Data.OleDbl

When the user posts the form data by clicking the Test Connection button, the Web Form grabs the value (not the text label) of the selected Connection Type radio button to determine whether to use a

SqlConnection or an OleDbConnection object.

private void Page Load(object sender, System.EventArgs e) { String sConn=""; SqlConnection sqlconn=null; OleDbConnection oledbconn=null; if (IsPostBack) {

sConnectionType = this.connectionType.SelectedItem.Value;

// more code here

At this point, the sConnectionType string variable contains either the string sql or the string oledb. Having obtained the desired connection type, the code then calls the makeConnectionString function.

String makeConnectionString() { String sconn=""; try {

sconn += "Provider=" + txtProvider.Text + "; ";

// get the server name sconn += "Data Source=" + txtServer.Text + "; "; sconn += "Initial Catalog=" + txtDatabase.Text + "; "; sconn += "User ID=" + txtUserID.Text + "; "; if (txtPassword.Text != "") {

sconn += "Password=" + txtPassword.Text;

catch (Exception ex) { throw ex;

return (sconn);

The makeConnectionString function simply concatenates the connection string field names and the user-entered values. If the user selected the OleDbConnection radio button, the function appends the string Provider= and the user-entered provider name. The function always concatenates the Data Source, Initial Catalog, and User ID values, but it ignores blank passwords. Any errors generate an exception.

After obtaining the formatted connection string, the Page_Load method creates the appropriate Connection object (again, by checking the value of the sConnectionType string). Note the use of the overloaded constructor to assign the ConnectionString property during object construction. After creating the appropriate Connection object type, the code attempts to open the connection. If the Open method succeeds, the code sets the imgConnection.imageURL property to the green traffic light icon and displays the connection string in the lblConnection control. If the Open method fails, the Catch block displays the error message and sets the imgConnection.imageURL property to display the red traffic light icon.

sqlconn = new SqlConnection(sConn); sqlconn.Open();

imgConnection.imageUrl = "../images/greenlight.gif"; lblConnection.Text = sConn;

oledbconn = new OleDbConnection(sConn); oledbconn.Open();

imgConnection.imageUrl = "../images/greenlight.gif"; lblConnection.Text = sConn;

catch (Exception ex) {

imgConnection.imageUrl = "../images/redlight.gif"; lblConnection.Text = ex.Message;

finally {

// close the connection if (oledbconn != null) { oledbconn.Close();

No matter what happens during the Page_Load event, the finally block forces any open connections to close. Note that you must trap for errors during the finally block just as you would for any other block. Rather than checking to see whether the oledbconn and sqlconn variables are null, an alternate method to ensure the connections are closed would be to put the Close methods in nested try blocks; for example:

finally {

// close the connections try {

oledbconn.Close();

sqlconn.Close();

While this takes a little less code, it probably also executes marginally slower. As you can see from the empty catch blocks, you don't have to handle errors in a catch block—you can simply ignore them, but you do have to catch them. Listing 13.1 contains the full code for the chl3-l Web Form, which lets you build and test simple connections.

Listing 13.1: Code for the Web Form chl3-l (chl3-l.aspx.cs)

using System;

using System.Collections;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Web;

using System.Web.SessionState;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.HtmlControls;

using System.Data.SqlClient;

using System.Data.OleDb;

namespace CSharpASP.ch13 { /// <summary>

/// Summary description for ch13 1. /// </summary>

namespace CSharpASP.ch13 { /// <summary>

/// Summary description for ch13 1. /// </summary>

public

class ch13

l

: System.Web,

. UI.Page {

protected

System.

.Web.

,UI

WebControls

. Label

Label6;

protected

System.

.Web.

,UI

WebControls

.Label

Provider;

protected

System.

.Web.

,UI

WebControls

. TextBox txtProvider;

protected

System.

.Web.

,UI

WebControls

Label

Label5;

protected

System.

.Web.

,UI

WebControls

TextBox txtServer;

protected

System

Web

UI

WebControls

.TextBox txtPassword;

protected

System

Web

UI

WebControls

.TextBox txtUserID;

protected

System

Web

UI

WebControls

Label

Label7;

protected

System

Web

UI

WebControls

Label

Labell;

protected

System

Web

UI

WebControls

TextBox txtDatabase;

protected

System

Web

UI

WebControls

Label

Label2;

protected

System

Web

UI

WebControls

Label

Label3;

protected

System

Web

UI

WebControls

Label

lblConnection;

protected

System

Web

UI

WebControls

Label

Label4;

protected

System

Web

UI

WebControls

.RadioButtonList connectionType;

protected

System

Web

UI

WebControls

Image

imgConnection;

protected

System

Web

UI

WebControls

.Button Buttonl;

protected

System

Web

UI

WebControls

RequiredFieldValidator

RequiredFieldValidatorl; String sConnectionType;

RequiredFieldValidatorl; String sConnectionType;

private void Page Load(object sender, System.EventArgs e) { String sConn=""; SqlConnection sqlconn=null; OleDbConnection oledbconn=null; if (IsPostBack) {

sConnectionType = this.connectionType.SelectedItem.Value; try {

sConn = makeConnectionString(); if (sConn != null) { try {

sqlconn = new SqlConnection(sConn); sqlconn.Open();

imgConnection.ImageUrl = "../images/greenlight.gif";

lblConnection.Text = sConn;

oledbconn = new OleDbConnection(sConn); oledbconn.Open();

imgConnection.ImageUrl = "../images/greenlight.gif"; lblConnection.Text = sConn;

catch (Exception ex) {

imgConnection.ImageUrl = "../images/redlight.gif"; lblConnection.Text = ex.Message;

finally {

// close the connection if (oledbconn != null) { oledbconn.Close();

catch (Exception ex) {

lblConnection.Text = ex.Message;

imgConnection.ImageUrl = "../images/redlight.gif"; lblConnection.Text = "";

String makeConnectionString() { String sconn=""; try {

sconn += "Provider=" + txtProvider.Text + "; ";

// get the server name sconn += "Data Source=" + txtServer.Text + "; "; sconn += "Initial Catalog=" + txtDatabase.Text + "; "; sconn += "User ID=" + txtUserlD.Text + "; "; if (txtPassword.Text != "") {

sconn += "Password=" + txtPassword.Text;

catch (Exception ex) { throw ex;

return (sconn);

#region Web Form Designer generated code override protected void OnInit(EventArgs e) {

// CODEGEN: This call is required by the ASP.NET Web Form Designer. //

InitializeComponent();

base.OnInit(e);

/// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// </summary>

private void InitializeComponent() {

this.Load += new System.EventHandler(this.Page Load);

#endregion }

You've seen how to create, open, and close a connection, and you're probably eager to see how to retrieve and display some data. Before leaving this Web Form, there are a few more useful things to learn from the example.

Web Form Tab Order

As I built the Web Form, I added controls in the order that I thought of them and dragged them around a little to create a decent layout. In other words, the controls were not created in their final order. If you use the keyboard to tab between the controls, you'll notice that the tab order is not conducive to easy entry (people who always click to move from control to control—and a few people do that—may not notice).

Unfortunately, you can't set the tab order in a Web Form as you can in a Windows Form—that handy little Tab Order view isn't on the View menu. You can set tab order three ways:

■ Set the Tabindex property for each control in the Properties Window to the appropriate tab order number.

■ Set the tabindex attribute for each control in the HTML editor to the proper tab order.

■ Move the control definitions in the HTML so they're created in the appropriate tab order.

All these methods are tedious, so here are a few "tricks" that can help speed things up or improve the result.

1. Don't bother setting the Tabindex property until you are otherwise satisfied with the layout and performance of the Web Form. You're likely to have to repeat the operation any time you add or rearrange the controls.

2. Set the tab order manually, incrementing the Tabindex value for each control.

3. Unlike Windows Forms, label controls in Web Forms can get the focus. The browser shows which control currently has the focus by drawing a dotted line around the control. You don't usually want labels to get the focus, so to leave them out of the tab order, set the Tabindex property (or the tabindex attribute) to zero or a negative number.

Finally, you should realize that modern browsers use the z-index style attribute to set the tab order. If you look at the generated HTML after setting the Tabindex property, you'll see the text z-index: <some integer> in the generated style attribute. For older browsers (those that don't understand CSS), you must create the controls in the desired tab order.

So, no matter what, you must set the tab order either manually or programmatically. Rearranging the controls is a manual—and error-prone—process. If you must deliver Web Forms to downlevel browsers (those for which you use a pageLayout property set to FlowLayout rather than GridLayout), I suggest you build and test the form first using GridLayout mode as if you were delivering the page to a modern browser. Then, after everything works exactly the way you want it to, set the pageLayout property to FlowLayout and test again. It's very difficult to switch the control order in FlowLayout mode.

For example, the listed order of the first few controls in the chl3-l Web Form on www.sybex.com is as follows:

<asp:Label id="Label6" style="Z-iNDEX: 116; LEFT: 25px;

POSiTiON: absolute; TOP: 70px" runat="server" Width="94px" Height="17px" CssClass="smallfontalignright" tabindex="0"> Connection Type:</asp:Label> <asp:Label id="Provider" style="Z-iNDEX: 114; LEFT: 24px;

POSiTiON: absolute; TOP: 112px" runat="server" Width="94px" Height="17px" CssClass="smallfontalignright" tabindex="0"> Provider:</asp:Label> <asp:TextBox id="txtProvider" style="Z-iNDEX: 105; LEFT: 127px; POSiTiON: absolute; TOP: 107px" runat="server" Width="196px" Height="2 4px" tabindex="3"></asp:TextBox> <asp:Label id="Label5" style="Z-iNDEX: 110; LEFT: 24px;

POSiTiON: absolute; TOP: 291px" runat="server" Width="39px" Height="17px" CssClass="smallfontalignright" tabindex="0"> ConnectionString:</asp:Label> <asp:TextBox id="txtServer" style="Z-iNDEX: 104; LEFT: 126px; POSiTiON: absolute; TOP: 141px" runat="server" Width="196px" Height="2 4px" tabindex="5"></asp:TextBox> <asp:TextBox id="txtPassword" style="Z-iNDEX: 106; LEFT: 126px; POSiTiON: absolute; TOP: 248px" runat="server" Width="101px" Height="2 6px" TextMode="Password" tabindex="11"></asp:TextBox> <asp:TextBox id="txtUseriD" style="Z-iNDEX: 107; LEFT: 126px; POSiTiON: absolute; TOP: 209px" runat="server" Width="101px" Height="2 6px" tabindex="9"></asp:TextBox>

But that's not the ideal tab order. Typically, tab order should move from left to right and top to bottom on the screen. Dragging the fields around visually won't work—you need to alter the order in which the page creates the controls to set the tab order properly—and that means you need to rearrange the code.

Tip To set the tab order on a Web Form, ensure that the HTML form creates the controls in the desired tab order.

I won't force you to do it—although I urge you to try it. The Web Form ch13-1a functions identically to the Web Form ch13-1, but the controls have the proper tab order.

Web Form Control Accelerator (Access) Keys

Another problem with the ch13-1 Web Form is that there's no easy way to jump directly to a specific control. In a Windows Form, you typically set an accelerator key for each label. When a user presses the Alt key and the label's accelerator key at the same time, Windows sets the focus to the control that follows the label in the tab order sequence. For example, you would expect a control labeled Provider to have an accelerator key of "P" and, when you press Alt+P, you would expect the txtProvider control to get the focus. It's not quite that simple in a browser. Each Web Server or HTML control in a browser has its own accelerator key property, called AccessKey. The property accepts a single character (you can't create a two-character accelerator for a control).

Note You should get in the habit of assigning the AccessKey property when you create a control— just as you should assign the id property to every control you create. It's a tremendous burden for maintenance programmers to find generically named controls on forms, because it makes the code difficult to read.

For some forms, you might find it easier to set the AccessKey and Tabindex properties in code. You can do that in the Page_Load event for the Web Form:

Provider.AccessKey = "P"; Provider.Text = "&Provider"; Provider.Tabindex="2"; txtProvider.Tabindex="3";

Connection String Warnings

Connection strings are complicated and often contain sensitive information such as passwords and user account names. Therefore, you need to be particularly careful about keeping that information private and known only to your application.

Check Dynamically Assigned Connection Strings

When you assign a connection string to a Connection object, the property code parses the connection string into its associated parts and uses the last value for any field occurrence. For example, if you accidentally specify the User ID and Password fields twice, with a specific user ID and password for the first set of values, and sa and the system administrator password for the second value, the connection will have system administrator privileges. Therefore, you must be careful if you ever do build a form like the one in Listing 13.1. A user could trick your application by entering extra information in the User ID or Password field.

For example, suppose you have an application that queries a database for sales information. You have programmatically fixed the Data Source and Initial Catalog settings to localhost and SalesData, respectively. But you display a dialog for a user to enter his or her user ID and password. If a malicious user enters the following text into the User ID field, and your application simply concatenates the User ID value with the Data Source and Initial Catalog values, what connection will the server attempt to make?

myUserID;Initial Catalog=CompanySalaries

Make sure that your code tests to ensure such security breaches can't happen. For example, you might refuse user IDs containing semicolons, or truncate user-supplied information before any embedded semicolon.

Storing Connection Information

Another type of security breach occurs when your application displays sensitive connection information to users. Unfortunately, this can happen in ASP.NET applications entirely by accident. In classic ASP, most developers stored connection information in an Application or Session variable. Do not do this in ASP.NET unless you have so severely limited the capabilities of your connection login account that you no longer care if the information is made public. The culprit is tracing. Although you can tell developers never to leave tracing turned on in a production application, people forget. Here's how your connection information can accidentally be made public. Follow this procedure to see it:

1. Create a Web Form that stores connection information in an Application or Session variable.

2. Turn tracing off for the application in the web.config file (see Chapter 9, "Debugging ASP.NET and Error Handling," for details).

3. Turn on tracing for the Web Form.

4. Run the Web Form. The trace directive displays the values of Application and Session variables—including your entire connection string containing user ID and password values—for anyone to see.

Granted, this may be a little far-fetched, but given the potential security risks, it's better to avoid the problem altogether.

Warning Don't store connection information in Application or Session variables.

So if you can't store connection information in Application variables, where should you store it? The simple answer is this: Store your connection strings in the web.config file. You can retrieve the values using the System.Configuration.ConfigurationSettings.AppSettings collection. The AppSettings collection contains settings from the <appSettings> section of your web.config file. The collection contains a set of keys. For example, suppose you want to create a fixed connection string to the Northwind database and then use it in multiple pages.

Note In the rest of this chapter, I'll use the connection strings defined for the Northwind database in the web.config file. Note that the connections are commented out as the file ships on www.sybex.com. Uncomment the connection definitions and modify them as needed for your database server, user ID, and password.

Find the <appSettings> section in your web.config file and add the following key and value (you may need to edit the values to apply to your database):

<add key="Northwind"

value="Provider=SQLOLEDB; Data Source=localhost; Initial Catalog=Northwind; User ID=sa; Password=;" />

The <add key="name" value="someValue" /> tag adds a key/value pair to the AppSettings collection. After you add the key, save the web.config file. Now you can access the values from code. Listing 13.2 contains code from the Web Form ch13-2. All the code does is use the value stored in the AppSettings collection under the key Northwind as the connection string to open—and immediately close—a connection, but it serves as an example for accessing AppSettings values.

Listing 13.2: Page_Load Event Code for the Web Form chl3-2 (chl3-2.aspx.cs)

using System.Data.OleDb;

private void Page Load(object sender, System.EventArgs e) { OleDbConnection conn; conn = new OleDbConnection(); conn.ConnectionString =

System.Configuration.ConfigurationSettings.

AppSettings.Get("Northwind"); //conn.ConnectionString =

System.Configuration.ConfigurationSettings. // AppSettings.Get("ConnIntegratedSecurity"); conn.Open();

Response.Write("Northwind connection opened successfully<br>"); conn.Close();

Connection String Field Order and Syntax

Every example you've seen so far shows the connection string fields in Provider, Data Source, Initial Catalog, User ID, and Password order, but you should know that there's no required order. You can just as easily reverse the field order or mix the fields in any order you wish.

You must separate the field pairs with semicolons. You do not need to conclude the connection string with a trailing semicolon, although doing so does not affect the validity of the connection string. Optionally, you may surround the field values with single or double quotes. If you must use double quotes within a connection string field value, escape the inner quotes.

myConn.ConnectionString = "Data source='localhost'; " +

"user id='sa'; initial catalog=\"Northwind\"; provider='SQLOLEDB';";

White space is completely optional. The connection string property accessor ignores all white space except that contained within inner quotes. The accessor also ignores case for all field names, but not for password values. In other words, the following two connection string assignments are equivalent:

OleDbConnection myConn = new OleDbConnection(); myConn.ConnectionString = "Provider=SQLOLEDB; " + "Data source=localhost; " + "initial catalog=Northwind; " + "user id=sa";

myConn.ConnectionString = "DATA SOURCE = localhost ; " + " USER ID=sa; INITIAL CATALOG=Northwind; PROVIDER=SQLOLEDB; ";

However, the following two connection strings are not equivalent (note the difference in the Password field capitalization):

OleDbConnection myConn = new OleDbConnection(); myConn.ConnectionString = "Provider=SQLOLEDB; " + "Data source=localhost; " + "initial catalog=Northwind; " + "user id=sa; Password="aPassword";

OleDbConnection myConn = new OleDbConnection(); myConn.ConnectionString = "Provider=SQLOLEDB; " + "Data source=localhost; " + "initial catalog=Northwind; " + "user id=sa; Password="apassword";

When you're attaching to file-based databases, such as Microsoft Access, use the provider name

Microsoft.Jet.OLEDB.4.0. The Data Source field value must contain the location of the database (MDB) file. I'll reiterate here that I don't recommend using Microsoft Access for your Web database.

Integrated Security

So far, the connection strings you've seen all used SQL Server security. You can use integrated security to connect to a database as well. You can use integrated security at any time. For example, you can either add permissions to the ASP.NET user account or change the account to one with specific permissions. You should use integrated security if

■ You have a secured Web application that requires authentication. and

■ You have individual accounts with different authorizations to access SQL Server data.

Before you can authorize an action by an account, you must know the identity of the account. By default, Web applications run anonymously, and the server uses either the ASPNET account or the System account to serve all pages. In a secured application, the user logs in either explicitly by entering a user ID and password or (with Internet Explorer clients) implicitly via Digest authentication or Integrated Windows authentication (NT Challenge/Response).

Using integrated security, the Web server passes the client's identity to the connected SQL Server instance (both the Web server and the SQL Server may be on the same machine), and SQL Server can grant or deny access to data based on its internal security settings.

I won't go into this in detail, but here's a procedure you can use for a quick test:

1. Add a connection string called NorthwindSSPI to your web.config file.

<add key="NorthwindSSPI" value="Provider=SQLOLEDB; Data Source=localhost; Initial Catalog=Northwind; Integrated Security=SSPI" />

2. Using the Web Form ch13-2, change the line that assigns the ConnectionString property to the following:

conn.ConnectionString=System.Configuration.ConfigurationSettings. AppSettings.Get("NorthwindSSPI");

3. Run the Web Form again. The default security settings should allow you to access the database, but if you get an error, you'll need to give the iusr_machinename or aspnet account on the Web server permission to access the database. You can do that through SQL Server Enterprise Manager by selecting the Security item for your SQL Server instance and adding the iusr_machinename account or aspnet account to the Logins list. Depending on other settings on your SQL Server, you may need to grant the account permission explicitly to access the Northwind database.

Debugging Connections

If you have trouble connecting to your SQL Server, one good debugging technique is to trace connections using the SQL Profiler. An in-depth description of tracing is beyond the scope of this book, but briefly, here's the procedure to set up tracing:

1. Start the SQL Server Enterprise Manager application.

2. Select SQL Profiler from the Tools menu.

3. Click the File menu and select New ® Trace. You'll see the Connect to SQL Server dialog (see Figure 13.3).

Figure 13.3: The Connect to SQL Server dialog

4. Select your SQL Server and enter your Login Name and Password, then click OK. You'll see the Trace Properties dialog (see Figure 13.4).

Figure 13.4: The Trace Properties dialog

5. Although the dialog has numerous options for tracing, you can accept the defaults; you don't even have to enter a name. However, it's worth exploring just a little bit. Click the Events tab.

6. The Events tab displays a list of available event classes on the left and a list of selected event classes on the right (see Figure 13.5). Note that the default selection includes Security Audit: Audit Login and Audit Logout. You're specifically interested in auditing logins to debug connection problems.

Ir«ce PrcfwrtiejL

E wtf i I Cdunwu | Frteii j J -JjT Ss^il* SQL^PVff ev^dwmsjw****'ou«?

E wtf i I Cdunwu | Frteii j J -JjT Ss^il* SQL^PVff ev^dwmsjw****'ou«?

Cut iiiii

H—

-

D-ridtMse

AiALqpi

4

Film iirtd War ninijx

»

Locfci

Add

ürJkiuhT

Gfcwtf*

E w rt^gC c^rtct^

lJL'if uii'ii.iriL^

ü Im nil l'Hii:niluinx

+:

5-rant

S r:i:ui ¡tp Auilil

_

Remev-e

■ TSOL

LÏ ElVfr!

PmçnAjmi

ïlATlIKltOfll

PAKfofl gl BU«Ji BVWi C^FPTT

PAKfofl gl BU«Ji BVWi C^FPTT

l'rt

Figure 13.5: The Trace Properties dialog Events tab, with default event class selections

7. Click the Data Columns tab. This tab displays the column list that you'll see in the trace log (refer to Figure 13.5). Scroll through the unselected data list on the left and add any items that you want to trace (you don't need to add any for this exercise) to the selected data list on the right.

8. Click the Run button at the bottom of the dialog to start tracing.

9. To watch the trace in action, browse to a page that connects to SQL Server, such as chl3-l.aspx or chl3-2.aspx. Each time the Web server requests a connection, you'll see an entry in the trace log (see Figure 13.6).

USiiiJf«iJJtlilijSj^J ifJSfJilf Figure 13.6: The trace log set to audit login information

The last event—Audit Login—in the trace log shows a little of the login information. To debug logins, you're interested in the NTUsername field (system) and the LoginName field (nt authority\system) in Figure 13.6.

The point of this exercise is that it's not always apparent which account is trying to access SQL Server, because the account used by IIS and the mapped SQL Server account depend on the security setup for your Web and SQL servers. Tracing shows you the actual values.

That's enough about connections—time to see the bigger picture.

Was this article helpful?

0 0

Post a comment