aspnet2securityfaq0073

Question: How do I use Windows authentication for connecting to SQL Server?

Answer:

To use Windows authentication, configure SQL Server appropriately and then use a connection string that contains either "Trusted_Connection=Yes", or "Integrated Security=SSPI" as shown in the following code. The two strings are equivalent and both result in Windows authentication.

		 "server=MySQL; Integrated Security=SSPI; database=Northwind"
		 "server=MySQL; Trusted_Connection=Yes; database=Northwind"

Use windows authentications wherever possible as, the accounts are centralized and managed by your Active Directory or local authority store. Strong password policies can be controlled and enforced by your domain or local security policy. Passwords are not transmitted over the network. User IDs and passwords are not specified in database connection strings.
When using Windows authentication, use a trusted service account to access the database when possible. This is usually your application's process account. By using a single trusted service account, your application benefits from connection pooling; this provides greater scalability. Also, account administration and authorization within the database is simplified.
If you need per-user authorization in the database or need to use operating system auditing to track the activity of individual users, you need to use impersonation and delegation and access the database using the caller's identity. This approach has limited scalability because it prevents the efficient use of connection pooling.
Here is how you configure SQL Server for the application's account
* Create a SQL login for the application's account
* Map the login to a database user
* Place the database user in a database role
* Grant permissions to the role. Ideally just grant execute permissions to selected stored procedures and provide no direct table access.

More Information

For more information on using windows authentication for accessing SQL server, see “How To: Connect to SQL Server Using Windows Authentication in ASP.NET 2.0.” at http://msdn.microsoft.com/library/en-us/dnpag2/html/PAGHT000008.asp

Return to
HomePage
ASPNET2SecurityFAQs