Login failed for user xxx on IIS 7

Recently I migrated one of my ASP.NET MVC projects to another machine.  I updated the Entity Framework .edmx file, modified the connection string, added a specific application pool PM in IIS, and added my application to use this application pool.  After I ran it, I got the exception "Login failed for user IIS APPPOOL\PM".

After some digging, I found some useful links, such as this and this.  I list all necessary steps to run an ASP.NET MVC application in IIS 7 here in case somebody needs helps.

1. In IIS, I like to create a dedicated application pool MyPool, and specify the correct .NET framework version and Managed Pipeline Mode, mostly Integrated.

2. Check the created pool, and we can see by default the Identity using ApplicationPoolIdentity.  This is the recommended way to use in a development environment.  Some blogs suggest changing this to "Local System" or others.  If this is changed to "Local System", then step 4 is not needed.  However, this change may bring some security risks.  You can check the links I listed and see some good arguments.  I will keep ApplicationPoolIdentity for my application.

3. In IIS, under Default Web Site, create an application for the website, give a proper Alias and point to the physical path of the project.  In a development environment, I just directly point to the web site project. Then let the application use the dedicated pool.

4. In SQL Studio, add new login IIS APPPOOL\MyPool.  You may check this link to see some good discussions.  Remember to assign proper roles to this login.  We don't want to give too much privilege for this login.

5. Sometimes we may need to enable MSDTC on the machine to support distributed transaction if the application uses distributed transaction.   On Win7, go to Control Panel -> Administrative tools -> Component Services, expand Computers -> My Computer -> Distributed Transaction Coordinator -> Local DTC, and right click Properties.  Under Security tab, check Network DTC Access, Allow Remote Clients, Allow Remote Administration, Allow inbound, Allow outbound.  Choose Manual Authentication Required.  Click OK.  A reboot may be needed here.

Using Stuff and For XML Path to format the string in one column

Recently in one project I needed to create some SSRS reports to present business data. One normal requirement is to use comma to join all the text in one column to one big string. I had experience to use Stuff and For XML Path to join columns, but this time I had a chance to know what is really going on behind For XML Path.  You can check this great blog for details to explain the syntax.

In my project, the first requirement was to join 2 columns with a space, and use a comma to separate each record, but leave a space after the comma.  So my SQL was like this:

stuff((
                    select ', ' + [Name] + ' ' + [Call] From DetailsView
                        for XML path('')
                ), 1, 2, '')

Later, the requirement changed.  User wanted to show each record in a separate line.  So I needed to replace the comma with a line feed.  Char(13) and Char(10) together can make a new line.  In my case, Char(10) is good enough to go.  So I changed my SQL to this:

stuff((
                    select Char(10) + [Name] + ' ' + [Call] From DetailsView
                        for XML path('')
                ), 1, 1, '')
Later, user still wanted to add the comma before the line break, so I just slightly changed to this:

stuff((
                    select ',' + Char(10) + [Name] + ' ' + [Call] From DetailsView
                        for XML path('')
                ), 1, 2, '') 

So now in my report, I can have a formatted string in multiple line.