ArcGIS Server Security and Windows 7

So I got this new fancy machine with Windows 7 installed. Let me tell you, it is a breath of fresh air after all those years of XP. Anyway, I was setting up a demo for ArcGIS Server security and I noticed that after I configured ArcGIS server to use SQL users and groups I got an error when I tried to create any users. At the bottom of manager I got the following error:
Unable to connect to SQL Server. Possible reasons include SQL Server not running, network problems, and permission denied for the account used to access SQL Server. See ArcGIS Server Help for more information.

The reason for this is IIS uses an ApplicationPoolIdentity instead of the Network Service. You can read more about this ApplicationPoolIdentity in the blog Goodbye Network Service. ArcGIS server is setup to grant access to the SQL Data base for Network Service but not for this new application pool identity. So after you run through the wizard to setup SQL for users and roles you need to run this little SQL script to grant the ApplicationPoolIdentity access to the database. You will need SQL Server Management Studio to run this script.

The following script creates a new login in SQL server for the DefaultAppPool. It then creates a user in aspnetdb database and grants it appropriate permission to manage the membership and role tables. If you named your database something other than aspnetdb you will need to change this script according.

CREATE LOGIN [IIS APPPOOL\DefaultAppPool]
FROM WINDOWS WITH DEFAULT_DATABASE=[aspnetdb]

USE [aspnetdb]
CREATE USER [IIS APPPOOL\DefaultAppPool]
FOR LOGIN [IIS APPPOOL\DefaultAppPool]

EXEC sp_addrolemember N'aspnet_Membership_FullAccess', N'IIS APPPOOL\DefaultAppPool'
EXEC sp_addrolemember N'aspnet_Roles_ReportingAccess', N'IIS APPPOOL\DefaultAppPool'
EXEC sp_addrolemember N'aspnet_Membership_BasicAccess', N'IIS APPPOOL\DefaultAppPool'
EXEC sp_addrolemember N'aspnet_Membership_ReportingAccess', N'IIS APPPOOL\DefaultAppPool'
EXEC sp_addrolemember N'aspnet_Roles_BasicAccess', N'IIS APPPOOL\DefaultAppPool'
EXEC sp_addrolemember N'aspnet_Roles_FullAccess', N'IIS APPPOOL\DefaultAppPool'
GO