SQL Server 2005 and ASP.NET 2.0 Authorization
So, you’ve spent long hours developing an ASP.NET 2.0 application on your development workstation using the local development server in VS 2005. Your connections to Active Directory, SQL Server 2005, Oracle 10g, etc. are all working like a champ. Your next step is to move it out to a development server. However, moving it over causes your web site to fail all SQL queries.
To make a long story short, using the Network Service to ensure you have AD authorization AND logging into SQL Server 2005 using integrated security takes a BIT of work because DOMAIN\COMPUTERNAME$ is what is passed as the user to SQL Server. Your SQL Server will return (in the SQL logs under the Management Studio), the following error:
Login failed for user ‘DOMAIN\WEBSERVER$’ [CLIENT: ipaddy].
Error: 18456, Severity: 14, State: 11.
The fix? Adding the MACHINE account to your SQL Server and granting it the proper permissions (such as db_datareader and db_datawriter).
Microsoft has a Practices article entitled How To: Connect to SQL Server Using Windows Authentication in ASP.NET 2.0 that details the steps, SQL commands to create the accounts, and permissions.
Why not just set the application pool to IUSR or another local service account? First off, you’d have to have a service account that has full access to your Active Directory and can traverse the trees and properties; second, you would still need to grant the user access to the SQL Server; where as the specific computer name is a bit more specific unless you have multiple web applications per server (in production).