Setting Up Trusted Connection between Sql Server and Web Server

Initiatives: MS Pattern and Practices recommendation: Building Secure ASP.NET Applications: Authentication, Authorization, and Secure Communication:
Extranet Security and Access Security.

Scenario 1 SqlServer and Web Server are running on the same box
Likely be the home pc or standalone development environment-the configuration is quite simple:
1. IIS6 (Windows 2003 Server)
Follow this article to configurate IIS 6 access to SqlServer.
In my experiments, it shows that the IIS anonymous account setting in the web app's '(Virtual) Directory Security setting | Authentication and access control' and disable/enable integrate Windows authentication is not relevant- it will always be an IIS_WPG member account used to access SqlServer. You can run EXEC sp_who to find out which account is used.

The only exception to above is you specify <identity impersonate="true"/> in web.config. I tried to use IUSER_(Internet Guest Account). However, it failed to get access to Sql Server (SP3)mysteriously, even I have grant the correct access rights to it.

2. IIS5 (Windows 2000 Server)
If running on IIS5(Windows 2000)it is IUSER_ used as the anonymous account. I guess it will be fine to use it to access SqlServer. Will try out soon. At the end of this blog is a vbs script which shows the credentials of IIS anonymous account and ASPNET Procs account.

Scenario 2 SqlServer and Web Server are running on two machines
In short, the MS recommended way is:
1)Don't put Web server on the trusted domain, but DMZ.
2)Create a NT account that impersonate as IIS anonymous account on web server.
3)Create the exact same account(username/password) on the Sql Server machine.
4)Grant access to this account.

How to use Trusted Connection when SQL server and web Server are on two separate machines has step by step guide on this.

Following vbs script shows credentials for iis anonymous user account

Dim IIsObject
Set IIsObject = GetObject ("IIS://localhost/w3svc")
WScript.Echo "AnonymousUserName = " & IIsObject.Get("AnonymousUserName") & vbCrlf
WScript.Echo "AnonymousUserPass = " & IIsObject.Get("AnonymousUserPass") & vbCrlf
WScript.Echo "WAMUserName = " & IIsObject.Get("WAMUserName") & vbCrlf
WScript.Echo "WAMUserPass = " & IIsObject.Get("WAMUserPass")
Set IIsObject = Nothing

Biztalk Resource:

Valuble Biztalk Resource:
Biztalk MessageBox Advance Queries

week two at MTC(Microsof Technoloy Center)

We have been here for two weeks to work with MS technologists on a Biztalk proof of concept project for our B2B system integration. I finally can spare sometime to do some brain dump.
We completed most of the development tasks for the POC. The opened issues here are around testing and some deployment setups for non-core services.
Phil (card ops), Geoff and Ade visited us on Thursday for a mid-term work check. They seemed quite satisfied about what Biztalk can do and how Biztalk can be used by the non-technologist. We even got Phil to create and test an orchestration with his own hand on the mouse.
The most interesting bit is deployment automation. Biztalk Deployment is a daunting task:
First you need to deploy .NET assemblies to GAC (these assemblies typically provide utility functions that used by orchestrations, rules engine, custom pipelines).
Then install all custom adapters if they are not previously installed on the machine. HTTP, SOAP, MSMQ etc are provided as standard, we also got a Topend adapter.
Then install the business rules engine.
Then install all Biztalk assemblies.
Then install and bind all send/receive ports Biztalk assemblies used.
Then start all orchestrations.
For our development and demo, we also need to install all web services used by Biztalk such like Smart Connectors and web sites for demonstration.

And of course we need a clean up script to reverse the deployment as well- it takes a whole lot to test a Biztalk orchestration.

We use command line script to automate these tasks. Some of them are provided in the SDK samples, others are not. Such like deploying business rules. Before we come to Reading, even MS are telling people ‘It is very difficult to automate this step, and you are better off to use the GUI wizard to do it’. And now we have a little command line program to do the job!