Recipe 1.7 Connecting to a Named Instance of SQL Server or Microsoft Data Engine (MSDE)

Recipe 1.7 Connecting to a Named Instance of SQL Server or Microsoft Data Engine (MSDE)

Problem

You want to connect to a named instance of a SQL Server or Microsoft Data Engine (MSDE).

Solution

You need to understand what a SQL Server or MSDE named instance is and how to connect to one. The sample code contains a single event handler:

Connect Button.Click

Creates and opens a connection to a named instance of a SQL Server. Information about the SQL Server is displayed from the properties of the SqlConnection object.

The C# code is shown in Example 1-6.

Example 1-6. File: ConnectNamedInstanceForm.cs
 // Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Windows.Forms;
using System.Text;
using System.Data.SqlClient;

//  . . . 

private void connectButton_Click(object sender, System.EventArgs e)
{
    StringBuilder result = new StringBuilder( );

    SqlConnection conn = new SqlConnection(
        ConfigurationSettings.AppSettings["Sql_Msde_ConnectString"]);

    try
    {
        conn.Open( );

        // Return some information about the server.
        result.Append(
            "ConnectionState = " + conn.State + Environment.NewLine +
            "DataSource = " + conn.DataSource + Environment.NewLine +
            "ConnectionState = " + conn.State + Environment.NewLine +
            "ServerVersion=" + conn.ServerVersion +
            Environment.NewLine);
    } 
    catch(Exception ex) 
    {
        MessageBox.Show(ex.Message);
    }
    finally
    {
        conn.Close( );
    }

    result.Append("ConnectionState = " + conn.State);

    resultTextBox.Text = result.ToString( );
} 

Discussion

SQL Server 2000 introduced the ability to install multiple copies of SQL Server on a single computer. Only one copy can function as the default instance at any time; it is identified by the network name of the computer on which it is running. All other copies are named instances and are identified by the network name of the computer plus an instance name . The format is <computerName>\<instanceName> . This format is used in the connection string to specify the Data Source attribute for a named instance.

Each instance operates independently of the other instances installed on the same computer. Each instance has its own set of system and user databases that are not shared between instances and it runs within its own security context. The maximum number of instances supported on SQL Server 2000 is 16. The Microsoft Distributed Transaction Coordinator (DTC) and the Microsoft Search services are installed and used simultaneously by every installed instance of SQL Server. Client tools such as Enterprise Manager and Query Analyzer are also shared.

The System.Data.SqlClient class cannot automatically discover the port number of a named instance of SQL Server listening on a port other than the default 1433. To connect to a named instance of SQL Server listening on a custom port, specify the port number following the instance name in the connection string separated by a comma. For, if the named instance msde01 was set up to listen on port 1450, the following connection string might be used:

 Data Source=(local)\  msde01,1450  ;Integrated security=SSPI;
    Initial Catalog=Northwind 

Recipe 1.8 Connecting to SQL Server Using Integrated Security from ASP.NET

Problem

You want to coordinate Windows security accounts between an ASP.NET application and SQL Server.

Solution

Connect to SQL Server from ASP.NET using Windows Authentication in SQL Server.

Discussion

Connecting to a SQL Server database provides two different authentication modes:

Windows Authentication

Uses the current security identity from the Windows NT or Windows 2000 user account to provide authentication information. It does not expose the user ID and password and is the recommended method for authenticating a connection.

SQL Server Authentication

Uses a SQL Server login account providing a user ID and password.

Integrated security requires that the SQL Server is running on the same computer as IIS and that all application users are on the same domain so that their credentials are available to IIS. The following areas of the application need to be configured:

  • Configure the ASP.NET application so that Integrated Windows Authentication is enabled and Anonymous Access is disabled.

  • The web.config file establishes the authentication mode that the application uses and that the application will run as or impersonate the user. Add the following elements to the web.config file:

     <authentication mode="Windows" />
    <identity impersonate="true" /> 
  • The connection string must contain attributes that tell the SQL Server that integrated security is used. Use the Integrated Security=SSPI attribute-and-value pair instead of the User ID and Password attributes in the connection string. The older attribute-and-value pair Trusted_Connection=Yes is also supported.

  • Add users and groups from the domain and set their access permissions as required.

By default, ASP.NET applications run in the context of a local user ASPNET on IIS. The account has limited permissions and is local to the IIS computer and therefore not recognized as a user on remote computers. To overcome this limitation when SQL Server is not on the same computer as IIS, run the web application in the context of a domain user recognized on both IIS and SQL Server computers.

In addition to the areas identified where IIS and SQL Server are on the same computer, the following additional items must be configured if the SQL Server is on a different computer:

  • Ensure that the mapped domain user has required privileges to run the web application.

  • Configure the web application to impersonate the domain user. Add the following elements to the web.config file for the web application:

     <authentication mode="Windows" />
    <identity impersonate="true" userName="domain\username"
            password="myPassword" />