Using Windows Authentication

Using Windows Authentication

You should use Windows authentication when connecting to SQL Server because it provides a number of benefits:

• Security is easier to manage because you work with a single (Windows) security model rather than the separate SQL Server security model.
• You avoid embedding user names and passwords in connection strings.
• User names and passwords are not passed over the network in clear text.
• Logon security improves through password expiration periods, minimum lengths, and account lockout after multiple invalid logon requests.

More Information

When you use Windows authentication to access SQL Server, use the following guidelines:

• Consider performance tradeoffs. Performance tests have shown that it takes longer to open a pooled database connection when using Windows authentication as compared to using SQL Server authentication. The .NET runtime version 1.1 has reduced the margin by which SQL Server security outperforms Windows authentication, but SQL Server authentication is still faster.

However, although Windows authentication is still more expensive, the performance reduction is relatively insignificant in comparison to the time it takes to execute a command or stored procedure. As a result, in most cases the security benefits of using Windows authentication outweigh this slight performance degradation. Before making a decision, assess the performance requirements of your application.

• Avoid impersonation in the middle tier. Windows authentication requires a Windows account for database access. Although it might seem logical to use impersonation in the middle tier, avoid doing so because it defeats connection pooling and has a severe impact on application scalability.

To address this problem, consider impersonating a limited number of Windows accounts (rather than the authenticated principal) with each account representing a particular role.

For example, you can use this approach:

1. Create two Windows accounts, one for read operations and one for write operations. (Or, you might want separate accounts to mirror application-specific roles. For example, you might want to use one account for Internet users and another for internal operators and/or administrators.)
2. Map each account to a SQL Server database role, and establish the necessary database permissions for each role.
3. Use application logic in your data access layer to determine which Windows account to impersonate before you perform a database operation.

Note   Each account must be a domain account with Internet Information Services (IIS) and SQL Server in the same domain or in trusted domains. Or, you can create matching accounts (with the same name and password) on each computer.

• Use TCP/IP for your network library. SQL Server 7.0 and later support Windows authentication for all network libraries. Use TCP/IP to gain configuration, performance, and scalability benefits.

Shashi Ray