Windows 2003 and any Sql Server based product from 2000 - 2005
A best practice, when hosting a web app under IIS with SQL Server, is to run the app in an Application Pool in IIS. This will benefit you in several ways.
1) Under an app pool you will better compartmentalize your server so that one app wont bring down other apps in case of a catastrophic failure as it sits in its own memory space and security domain.
2) Authentication to SQL server will be more secure as you wont need to hardcode a username and password in your connection string.
3) You will be able to expose only what is used on SQL Sever such as views and Sp’s.
1) Create a Service account by adding a new user with no remote desktop access.
2) Run C:\WINNT\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis.exe -ga SERVERNAME\ServiceAccountUser to add the new service account to the proper roles so that IIS can authenticate.
3) Add the new service account to the Secuirty Logins in SQL Server and make its Default Database your target Database.
4) Run the following script under this database. It will generate the required SQL to grant permissions to the SPs. This way the Service account can only communicate with the database via the SPs.
Select 'GRANT EXECUTE ON ' + name + ' TO [ServerName\ServiceAccountUser]' from sysobjects where xtype = 'P' and substring(name,1,3) <> 'sp_'
5) Copy the output to another query window and execute.
6) Change your connection string in your web config to the following but match your account name and database name.
7) <add key="Connection.String" value="data source=SERVERName\Instance;initial catalog=Your database Name;integrated security=SSPI;persist security info=False;packet size=4096"/>
8) In IIS add a new app pool and change the identity to Configurable and add your service accounts name and password.
9) Under the IIS virtual Dir of your app change your apps application pool to your newly created pool.
10) Restart IIS and or the App Pool.
11) Walaaa you should be able to browse to your app and it will be running under the new app pool.
Copyright (c) 2006-2012 SplendidCRM Software, Inc. All Rights Reserved.