Data Security in ASP.Net with SQL Server

I want to avoid putting the user name and password in my connection string for security purposes so I use “Persist Security Info=true;Integrated Security=SSPI”. When the database and the web site are on the same server I can get this to work by assigning the user machinename\aspnet something it works with nt authority\local service. But when the web site and database are on separate machnines the metho开发者_运维百科logy to set this up on SQL Server is different. I don’t know how to this. Can anyone help me?

Create new application pool in the IIS management console, set its identity to a domain account you want to use to connect to server and configure your app to use this application pool. Configure windows authentication in the connection string.

Regards

Piotr

Assuming your IIS server app pool is running as NETWORK SERVICE, on your SQL Server, create a login for DOMAIN/MACHINE$ and grant access to security objects. Make sure you add the dollar-sign.

I provided the following answer to another question Web service with database : SqlException. I believe this might help you as well. These steps are for IIS 7.5 and ASP.NET 4.0 but the underlying concept is same for other versions as well.

If you have Active Directory domain setup in your environment, I would suggest the following:

1. Create a domain account say MyDomain\webappuser, where MyDomain would be active directory domain and webappuser would be the Windows user account in that domain. In SQL, provide this new domain account user with appropriate permissions to access the database.

2. In Internet Information Services (IIS) Manager, change the Application Pool to run under this service account. If you are running IIS 7.5, following steps can be performed to do that:

3. In IIS, expand your <server name> node and click on Application Pools.

4. It is better to create a new Application Pool, so that you don't disturb the functionality of other applications that might use the application pool ASP.NET v4.0.

5. Create the new application pool (say CustomAppPool) similar to ASP.NET v4.0 application pool, except that the new application pool will use the Identity of the newly created domain account MyDomain\webappuser instead of the usual ApplicationPoolIdentity.

6. On the Advanced Settings option of the virtual directory/site where your web service is deployed to, change the Application Pool property to use the newly created application pool CustomAppPool.

Hope that helps.