While configuring a new server running Microsoft Windows Server 2003, I decided to start using trusted SQL connections from ASP.Net Web sites (instead of specific SQL user accounts). One of the differences between Windows 2000 and Windows 2003 is the ASP.Net process account; the former uses ASPNET, the latter NETWORK SERVICE.
One thing you’ll notice is that when creating a new login for SQL Server is that you can’t select the NETWORK SERVICE account. (Oddly, you can select the ASPNET account, but I don’t know what it is used for in Windows 2003/IIS 6.0, since it’s been replaced with NETWORK SERVICE.) After plenty of searching I found a newsgroup posting from Microsoft that solved the problem.
In brief: Either specify the username as NT AUTHORITY\NETWORK SERVICE in SQL Enterprise Manager’s New Login window, or run the following query:
exec sp_grantlogin [NT AUTHORITY\NETWORK SERVICE]
Yet another example of Microsoft hiding the obvious.