There are several different ways to connect to SQL Server from an ASP.NET application. I'm working on rebuilding an ASP.NET / SQL Server environment right now and I'm trying to figure out which method I should be going for. Here are the options as I see them:
- Connect via SQL Server ID that is stored in web.config. Pro: simple. Cons: password in web.config; have to specifically configure SQL Server ID.
- Connect via user NT ID via ASP.NET impersonation. Pro: no passwords in web.config; fine-grained control of security per user. Cons: administrative overhead of configuring user accounts in SQL Server; SQL Server monitoring of application is scattered across many accounts.
- Run ASP.NET as a custom NT ID, and have that NT ID configured in SQL Server. Pros: connecting to SQL Server as one ID - simple; no passwords in web.config. Cons: complicated from a security perspective. Have to configure custom SPNs in Active Directory for Kerberos authentication.
Are there other options that I'm missing? Which of these options are used in which situations? Which are more standard? Are there pros and cons that I'm not thinking about?
Note that my assumption is that users are authenticating with ASP.NET via integrated windows authentication; this is for an intranet application.