WebService Identity- best practice?

[MichaelG]

I have a web service that talks to SQL Server 2005. At present the WS uses
an SQL Server login and password to connect.

Is this best practice or should I be running the WS under a domain account
and using Integrated Security to connect to SQL Server?

On reading the IIS help it seems that if I create a new application pool and
configure it's identity then I can't use Kerberos. Most of the time the WS
is serving users on the LAN but some of the time users will work from home
across the internet. I plan on using WSE 3.0 (and WCF when released) so
authentication and authorisation should happen in the WS rather than the
database I think.

What is best practice with all of this?

Thanks
Michael

[Peter Kelcey]

Michael

Without knowing all of your exact requirements, here are my thoughts.

1) I believe it's a best practice to use integrated authentication to
connect to the SQL server from your web service instead of sql
authentication. With sql authentication, you have worry about securely
storing and managing you user id and password. With integrated
authentication you don't have to worry about this and you avoid
passing credentials over the network.

2) Unless you absolutely require each service consumer to have their
own ID in the database, I would recommend setting up a trust-boundary
between your service and your database. Since you have control of both
the service and the database, you can have the database trust the web
service to do proper authentication. Your web service then becomes
responsible for authenticating the service consumers first (using your
WSE 3.0). Once the service has properly authenticated the consumer, the
web service runs under a single windows account and connects to the
database using this ID. The SQL server only accepts this id from the
service and trusts that the service performed its own authentication
properly. Using this pattern, you only have to have your database
manage and authenticate a single ID.

3) With regards to which windows account to have your web service
access the database with, you can just use the default ASPNET process
account or you can create a new domain account. If you choose to use
the least privileged ASPNET account, you will need to ensure it exists
on both your web service and your database service with a synchronized
password. The need to synchronize the password on both machines does
lead to a slightly higher administrative overhead. If you choose to use
a domain account you don't have to worry about this synchronization
but you will need to ensure that you create it as a least-privileged
account so that it only has access to what it need.

There is an excellent patterns and practices document on MSDN that
discusses options for your scenario in detail. It gives the pros and
cons of a number of different solutions and I would strongly recommend
giving it a read. It expands considerably on the issues I outlined
above.

http://msdn.microsoft.com/library/de...danceIndex.asp

Hope that helps

Peter