The problem arises when your SQL server and your IIS/asp.net server are on separate machines, which is a typical setup. As the ASPNET user does not exist on the SQL server you can't grant access to it.
There are 4 main ways to overcome this problem
- Use IIS6 in native application mode
- Mirror the asp.net user on both the IIS box and the SQL server and set a known password
- Use impersonation to change the context your pages run in
- Encrypt a connection string in the registry and forget about trusted connections.
- Switch the asp.net context to be a domain user.
Running any web service as a domain user is ill-advised. A compromise of your web server would mean that any cracker would then have an authenticated session to your domain or active directory and be able to wander outside the web server and through any other machines the user context has access to.
Encrypting and accessing data in the registry is covered by Microsoft Knowledge Base article 329290 and an MSDN article in the Building Secure ASP.NET Applications patterns and practices section.
Both impersonation and mirroring the asp.net user require you to mirror accounts on both the web and SQL server if you are not in a domain/AD environment.
Comments