Comment from Frans:
"Rather than use SQL authentication when connecting from ASP.NET to SQL Server, you should use native windows authentication -- but lock down access not to the identity of the calling user, but rather to that of the ASP.NET worker process identity of the running application. That way you can restrict access to only allow the application access to the data (not the end users using it). You also do not have to worry about the SQL authentication usernames/passwords ever being compromised and access in these scenarios -- since no username/password is ever stored in an unencrypted way"
That's wrong, sorry. If I have 2 machines, one being the IIS server and the other one being the sqlserver machine, the ASP.NET account is LOCAL to the IIS server. I can't use that account to access the sqlserver instance and do things there. I then have to add the SAME user to the sqlserver machine as well with the SAME password, also as local account.
-> 2 times the same user and it 'works' because they have the same credentials, but they are different users.
On the DOTNET-CLR mailing list we had a lengthy discussion about this a month or so ago and we all concluded that it wasn't possible to do this how MS tells us to do it. On 1 box, it's no problem (sqlserver and IIS on one machine). There is however a problem with the contradiction between the advice from MS not to run your sqlserver/iis boxes in a domain / as a PDC/BDC, so you use local defined accounts on the IIS box, not domain users, and the fact that it is impossible to access box B from box A when you are logged in on A as a local user (ASPNET) of A which is not known on B.