When using Windows authentication, how can I give the default ASP.NET worker process access to a remote database server?

From Guidance Share

Jump to: navigation, search

J.D. Meier, Prashant Bansode, Alex Mackman

Answer

Create a SQL Login for the Network Service Account, create a database user in the required database and map the login to the database user. Place the database user in database role and then grant the required permission to the database role. Note: Using the Network Service account will generally be a development scenario, in production scenario’s the ASP.NET process will be running using a custom domain account. In IIS 6.0, the ASP.NET worker process runs under the Network service account by default. This is a low privileged account that has network credentials. These credentials can be authenticated on the network using the computer's domain account. So to grant remote database access to the Network Service account, you need do the following

  • Create SQL login for the Network Service Account. The name appears as domainName\<WebServerMachineName>$ if your database is on a separate server. You can use Enterprise Manager or run the following SQL statement to create the SQL Login
exec sp_grantlogin [domainName\<WebServerMachineName>$] 
  • Create a database user in the required database and map the login to the database user. Or you can run the following SQL statement:
exec sp_grantdbaccess [domainName\<WebServerMachineName>$] 
  • Place the database user in a database role. This enables you to assign permissions to roles instead of individual users, which helps should, the user account change. Grant the required permissions to the role. Ensure that you grant only necessary permissions with appropriate levels of access. Ideally, this should be execute permissions to select stored procedures with no direct table access

More Information

For more information on using windows authentication with Network Service account for accessing SQL server, see “How To: Connect to SQL Server Using Windows Authentication in ASP.NET 2.0.” at http://msdn.microsoft.com/library/en-us/dnpag2/html/PAGHT000008.asp

Personal tools