This topic presents a brief discussion of the difference between SQL Server's Windows NT Challenge/Response and Basic Authentication methods when using Named Pipes, Internet Information Server, and SQL Server's integrated security option.
While designing the security for your enterprise application, you must understand the consequences of choosing an authentication method to work with IIS and SQL Server. In the first case, where all resources are on the same machine, you can use SQL Server integrated security. In the second case, where some resources are on remote machines, you cannot. The thing to remember when evaluating SQL Server's integrated security option is its reliance on Named Pipes for authentication.
If your application has all of its server resources on a single server workstation (including SQL Server, IIS, and all of the Web pages), then you can use the high security provided by Windows NT Challenge/Response authentication and SQL Server integrated security.
By removing anonymous access to Active Server Pages, which have ADO data access, authentication occurs when a user attempts to open the ASP. This causes the ASP to be processed under that user's context, and consequently the connection to the SQL Server is made as that user.
If your application provides anonymous access to pages that utilize ADO, you must create an anonymous user account in SQL Server and map the IUSR_computername account to this SQL Server account. By default, SQL Server does not allow underscores (_) in its user names, so you will need to set up a new anonymous user account for use with SQL Server's integrated security.
Notice that setting up an anonymous user account generally defeats the purpose of using integrated security in the first place. If your configuration authenticates all users as anonymous, standard security should be used instead.
In summary, SQL Server with integrated security can only be used on the same machine as IIS if there are no remote resources and you want the users to connect to SQL Server with their logged-on Windows NT accounts using the Windows NT Challenge/Response authentication method.
If your application must access a resource (such as a file, Web page, or SQL Server) on a remote machine, then you must use basic authentication and you cannot use integrated security with SQL Server. The reason is that the Named Pipes connection to another physical Windows NT machine will not provide the user's password as the encryption key.
This typically occurs when your application has an ASP page that uses ADO to access a SQL Server database on a remote Windows NT server. The remote server will challenge the local IIS to prove that it is the impersonated user, and IIS will not be able to satisfy that request because it does not have the user's password as the encryption key.
Note There is one circumstance where the encryption key is known: when the remote machine is a domain controller, the encryption token is available. As a general rule, remote machines are not configured as domain controllers.
For a SQL Server configured with integrated security, this means Active Server Pages that do not allow anonymous access will not be able to connect to SQL Server on another physical Windows NT machine unless the IIS machine is a domain controller. In other words, you will not be able to let users access Web pages and connect to the remote SQL Server with their user accounts.
If anonymous access satisfies your application's security requirements, then using basic authentication is a good choice.