.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Ashutosh Jha
satyapriyanayak

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Using Windows Authentication

Posted By:Shashi Ray       Posted Date: November 25, 2009    Points: 25    Category: DataBase    URL:   
 

 

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


 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend