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