Authentication

When your application connects to a SQL Server database, you have a choice of Windows authentication or SQL authentication. Windows authentication is more secure. If you must use SQL authentication, perhaps because you need to connect to the database using a number of different accounts and you want to avoid calling LogonUser , take additional steps to mitigate the additional risks as far as possible.

Note  

Using LogonUser to create an impersonation token requires the powerful "Act as part of the operating system" privilege on Microsoft Windows 2000 and so this approach should be avoided.

Consider the following recommendations:

• Use Windows authentication .

• Protect the credentials for SQL authentication .

• Connect using a least privileged account .

Use Windows Authentication

Windows authentication does not send credentials over the network. If you use Windows authentication for a Web application, in most cases, you use a service account or a process account, such as the ASPNET account, to connect to the database. Windows and SQL Server must both recognize the account you use on the database server. The account must be granted a login to SQL Server and the login needs to have associated permissions to access a database.

When you use Windows authentication, you use a trusted connection. The following code fragments show typical connection strings that use Windows authentication.

The example below uses the ADO.NET data provider for SQL Server:

 SqlConnection pubsConn = new SqlConnection(    "server=dbserver; database=pubs; Integrated Security=SSPI;"); 

The example below uses the ADO.NET data provider for OLE DB data sources:

 OleDbConnection pubsConn = new OleDbConnection(    "Provider=SQLOLEDB; Data Source=dbserver; Integrated Security=SSPI;" +    "Initial Catalog=northwind"); 

Protect the Credentials for SQL Authentication

If you must use SQL authentication, be sure that the credentials are not sent over the network in clear text and encrypt the database connection string because it contains credentials.

To enable SQL Server to automatically encrypt credentials sent over the network, install a server certificate on the database server. Alternatively, use an IPSec encrypted channel between the Web and database servers to secure all traffic sent to and from the database server. To secure the connection string, use DPAPI. For more information, see "Secure Your Connection String" in the "Configuration Management" section, later in this chapter.

Connect Using a Least Privileged Account

Your application should connect to the database by using a least privileged account. If you use Windows authentication to connect, the Windows account should be least privileged from an operating system perspective and should have limited privileges and limited ability to access Windows resources. Additionally, whether or not you use Windows authentication or SQL authentication, the corresponding SQL Server login should be restricted by permissions in the database.

For more information about how to create a least privileged database account and the options for connecting an ASP.NET Web application to a remote database using Windows authentication, see "Data Access" in Chapter 19, "Securing Your ASP.NET Application and Web Services."