Share via

How to: Access SQL Server Using Windows Integrated Security

  • Article

If your application runs on a Windows-based intranet, you might be able to use Windows integrated authentication for database access. Integrated security uses the current Windows identity established on the operating system thread to access the SQL Server database. You can then map the Windows identity to a SQL Server database and permissions.

To connect to SQL Server using Windows integrated authentication, you must identify the Windows identity under which your ASP.NET application is running. You must also be sure that the identity has been granted access to the SQL Server database. This topic includes a code example that displays the current Windows identity of the ASP.NET application.

Connecting to SQL Server

If SQL Server is on a different computer than the Web server, the Windows identity must be able to flow across the network to the remote instance of SQL Server. (Windows networks that have been configured appropriately with Kerberos authentication are able to do this.) However, depending on the settings in the identity configuration element, the Windows identity established on the operating system thread for ASP.NET applications may not be able to flow properly to the remote SQL Server.

You can supply a specific user name and password for the Web site's worker process identity as shown in How to: Access SQL Server Using a Mapped Windows Domain User, or you can impersonate the authenticated identity supplied by Internet Information Services (IIS). To impersonate the Windows identity supplied by IIS, set the impersonate attribute of the identity configuration element to true as shown in the following example:

<system.web>
  <identity impersonate="true" />
</system.web>

In IIS, only Basic Authentication logs users on with a security token that flows across the network to a remote SQL server. By default, other IIS security modes used in conjunction with the identity configuration element settings will not result in a token that can authenticate to a remote SQL Server.

If the Web site is configured to support only anonymous access in IIS, then the security token passed from IIS will be that of the Windows user account for anonymous access as configured in IIS. The anonymous user account can be used to authenticate against a remote SQL Server. However, the default anonymous user account is a local machine account and thus will not exist as an account on the remote SQL Server. You can change the IIS anonymous account to use a domain account, or you can mirror the local machine account on the remote SQL Server by creating a local account on the remote SQL Server with the same user name and password. Additionally the LogonMethod metabase property for IIS6 must be set to an option that allows credentials to flow across the network. For example, the metabase setting MD_LOGON_NETWORK_CLEARTEXT allows logon credentials to flow across the network.

If you are unsure of the Windows identity for your application and whether that identity is logged on with a token that can flow across the network, you can run the following ASP.NET page as part of your application to display the name of the Windows identity and a value indicating whether the identity can flow across the network. Note that the following sample does not take into account whether or not Kerberos delegation has been successfully set up for your domain.

<%@ Page Language="VB" %>
<%@ Import namespace="System.Security.Principal" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>ASP.NET Example</title>
<script runat="server">

Public Function WillFlowAcrossNetwork(w As WindowsIdentity) As Boolean
  For Each s As SecurityIdentifier In w.Groups
    If s.IsWellKnown(WellKnownSidType.InteractiveSid) Then Return True
    If s.IsWellKnown(WellKnownSidType.BatchSid)       Then Return True
    If s.IsWellKnown(WellKnownSidType.ServiceSid)     Then Return True
  Next

  Return False
End Function

</script>
</head>
<body>
<%
  Dim current As WindowsIdentity =  WindowsIdentity.GetCurrent()
  Response.Write(current.Name & ", " & WillFlowAcrossNetwork(current) & "<br />")
%>
</body>
</html>

<%@ Page Language="C#" %>
<%@ Import namespace="System.Security.Principal" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>ASP.NET Example</title>
<script runat="server">

public bool WillFlowAcrossNetwork(WindowsIdentity w)
{
  foreach (SecurityIdentifier s in w.Groups)
  {
    if (s.IsWellKnown(WellKnownSidType.InteractiveSid)) { return true; }
    if (s.IsWellKnown(WellKnownSidType.BatchSid))       { return true; }
    if (s.IsWellKnown(WellKnownSidType.ServiceSid))     { return true; }
  }

  return false;
}

</script>
</head>
<body>
<%
  WindowsIdentity current =  WindowsIdentity.GetCurrent();
  Response.Write(current.Name + ", " + WillFlowAcrossNetwork(current) + "<br />");
%>
</body>
</html>

The following procedure shows how to access a SQL Server database using Windows integrated authentication in an intranet scenario, where each user has been granted access to the SQL Server individually.

To begin, you need to configure your application in IIS to turn off anonymous access and turn on Windows authentication.

To configure IIS for Windows integrated authentication

  1. In Windows, open the Internet Information Services administration tool.

    • In the Microsoft Windows 2000 Server or Windows Server 2003 operating systems: In the Windows Start menu, point to Programs, then Administrative Tools, and then Internet Services Manager.

    • In the Microsoft Windows XP Professional operating system: open Administrative Tools in the Control Panel.

  2. Open the node for your server, and then open nodes until you find the node for your application, which is typically located under Default Web Site.

  3. Right-click your application and then click Properties.

  4. On the Directory Security tab, click Edit.

  5. In the Authentication Methods dialog box, clear the Anonymous Access check box, and then do one of the following:

    • If SQL Server is on the same computer as IIS, select the Integrated Windows authentication check box.

    • If SQL Server is a remote server, select the Basic Authentication check box and clear the Integrated Windows authentication check box.

  6. Click all the dialog boxes.

In the application configuration file (Web.config), specify that the application will impersonate the user's credentials supplied by IIS.

To configure Web.config to impersonate the identity supplied by IIS

  • Open the Web.config file for your application and add the following to the system.web element:

    <identity impersonate="true"/>
    NoteNote

    Elements in Web.config are case sensitive.

When you create a connection string to access SQL Server, you must include attributes that tell SQL Server that you are using integrated security.

To configure connection strings for Windows integrated security

  • In any connection string for SQL Server, include the attributeTrusted_Connection=Yesand remove the username and password attributes.

    The following shows a typical connection string configured for Windows integrated security:

    "workstation id=WebServer1;packet size=4096;
Trusted_Connection=Yes;data source=SQLServer01";
persist security info=False;initial catalog=northwind"

Set up SQL Server to recognize the users who will be accessing it.

To configure SQL Server for Windows integrated security

  1. From the Windows Start menu, select Microsoft SQL Server, and then select Enterprise Manager.

  2. Open the node for the server and expand the node for the database you want to give users permissions for.

  3. Right-click the Users node and select New Database User.

  4. In the Database User Properties dialog box, enter domain\username in the Login name box, and then click OK. Additionally, configure the SQL Server to allow all domain users to access the database.

See Also

Concepts

ASP.NET Impersonation
Accessing SQL Server from a Web Application

Other Resources

ASP.NET Web Site Security