Matt,

It will work with a domain account. It is not as secure relative to an intermediate process that controls exactly what gets sent to SQL Server. To send malicious code to SQL Server in an ASP application, all the hacker has to do is spoof the IIS machine and send the code to SQL Server to execute. Of course there are other security mechanisms in IIS, Windows Server, and SQL Server to make such an attack more difficult. But using an intermediate process decreases susceptibility to malicious code attacks by an order of magnitude.

Simply put, if you maxed out the security using IIS -> SQL Server and minimized the security using IIS -> Middle Tier -> SQL Server, the three tier model is still more secure than the two tier model.

Remember that IIS/ASP is almost 10 years old and SQL Server's security model is even older than that. The internet was still relatively tame back then.

>Hi Keith,
>
>Writing a web service or COM object as an intermediary between ASP and SQL Server seems like an awful lot of work to achieve something so simple. I wouldn't even know where to start on that. I'm not clear, for instance, how a COM object would be able to connect to SQL Server - would the COM object be running on the same server as SQL Server? Presumably so, otherwise it would still have to run as a domain user. How would the ASP pages call or instantiate the COM object? Too many questions ;-)
>
>It would probably be quicker to rewrite the whole application in ASP.Net! Unfortunately, that is currently out of the question.
>
>I would have thought that someone else would have solved this problem already - it can't be that unusual to have IIS and SQL Server on separate servers within an organisation. I've asked on several messageboards but haven't got a definitive answer yet.
>
>It looks like we may have to contact Microsoft and ask them what the "best practice" is. The way it's going, it looks like using SQL Server Authentication may be just as, or more secure than setting anonymous IIS users as a domain account with permissions in SQL Server.
>
>Best.
>
>Matt.
>
>
>
>>
>>I missed that part about ASP. To answer your question, yes it is more of a security risk to set anonymous IIS users as a domain account with permissions in SQL Server. ASP doesn't provide the same level of control as ASP.NET, and because it is a scripting language, is more vulnerable to script injection attacks.
>>
>>The correct and most secure way to set up the security is to put something in between the web page and the database. A Web Service or COM object would do the trick. That way you can precisely control what is being executed on SQL Server.
>>