Building A Trusted Connection from an ASP.NET application to SQL Server database

On the Web Server Machine(s)

1. Set ASPNET password to Unique Strong Password on Web Server(s)
   


2. Generate registration keys using aspnet_setreg.exe see Q329290 at Microsoft Support (http://support.microsoft.com/default.aspx?scid=kb;en-us;329290)
   
   
   1. See Batch Job BuildRegistryKeys.bat for example of batch build using Password in Password.txt
   
   
   2. RUN aspnet_setreg.exe -k:SOFTWARE\MyASP.Net\Identity -u:"userName" -p:"UniqueStrongPassword"
      e.g. aspnet_setreg.exe -k:SOFTWARE\MyASP.Net\Identity -u:"ASPNET" -p:"6D7C23D0-D78D-4446-B019-3C1F70CDA9FD"
   
   
   3. Right down new registry keys generated above. Will be something like:
      HKEY_LOCAL_MACHINE\SOFTWARE\MyASP.Net\Identity\ASPNET_SETREG


3. Set permissions on new registry keys build above to allow the ASPNET user permission to read. To accomplish this run Reged32.exe right click on the new key (HKEY_LOCAL_MACHINE\SOFTWARE\MyASP.Net\Identity\ASPNET_SETREG). Select Permissions and allow read permissions for ASPNET user.
   


4. Windows 2000 Servers
1. Locate Machine.Config file. Should be something like:
   c:\WINDOWS\Microsoft.NET\Framework\v1.0.3705\CONFIG\Machine.Config


2. Find Section:
   <processModel
        userName="machine"
        password="AutoGenerate"
        ....processModel>


3. Change to:
   <processModel
        userName="registry:HKLM\Software\MyASP.Net\Identity\ASPNET_SETREG,userName"
        password="registry:HKLM\Software\MyASP.Net\Identity\ASPNET_SETREG,password"
        ....processModel>
   


5. Windows 2003 Servers
1. Change Web.Config (Impersonation for Network Service User):
   <identity impersonate="true"
        userName="registry:HKLM\Software\MyASP.Net\Identity\ASPNET_SETREG,userName"
        password="registry:HKLM\Software\MyASP.Net\Identity\ASPNET_SETREG,password" />
   
   http://support.microsoft.com/?id=871179
   http://geekswithblogs.net/vamsi/articles/43002.aspx
   http://weblogs.asp.net/AChang/archive/2004/04/15/113866.aspx



---

On the Sql Server Machine(s)

1. Create Mirror ASPNET user on SQL Server machine. Set Password to above (1) strong password.
   
2. Add ASPNET user to security in SQL Server
   
3. Add ASPNET user to Database that you wish access. You probably need to allow ASPNET user to be a dbo and have security
   

NOTES:

I would leave a normal Sql Server connection as you currently do on individual developer machines and use the trusted connection on development and production machines.

This allows you to control the developer Ids seperate from the ASPNET user ID. I would also limit the users that know any known password for the production ASPNET user.


Robin