Tell me more ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.
up vote 4 down vote favorite

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.

share|improve this question

3 Answers

up vote 3 down vote accepted

The first option has another option, which is very good indeed:

  • Connect via SQL Server ID that is encrypted and stored in web.config

See this for more info: http://msdn.microsoft.com/en-us/library/dx0f3cf2%28v=vs.80%29.aspx

share|improve this answer
Yes I should've included the encryption in my writeup. Almost certainly if you are going with the first option you would want to encrypt the connectionstrings section of the web.config. But this is still a con, as encryption adds complexity, and you have to maintain the password, change it between QA & Prod, etc. – RationalGeek Feb 23 '11 at 18:18
There's always overhead when it comes to security. But so far this option has been (for me) the least headache causing, and the most portable. The ability to basically copy paste my web app into another server and have it working almost immediately far over shadows what little effort is needed to maintain the connection string. – System Down Feb 23 '11 at 18:31
I agree. This is the option I'm leaning toward. I just wanted to understand all the options before I pursued one over the others. – RationalGeek Feb 23 '11 at 20:37
up vote 1 down vote

I've toyed with the idea of putting connection strings in the machine.config file before. This has some obvious security requirements (ie, it requires that you trust every single app running on the server - but in my environment, we do).

That way, you can take them out of the web.config altogether. Only people with administrative access to the web server would be able to access that file and view the connection string.

The biggest reason we wanted to do this, however, was for convenience: it would help prevent us from having to maintain two copies of the web.config (debug + release) and would let us rotate SQL Server passwords without having to touch every single application.

However, I haven't actually gone forward with this, so I can't speak to its viability. But it definitely is an option.

share|improve this answer
up vote 0 down vote

I think you've got an error in the 3rd bullet -- you can definitely let the users authenticate against the app and let the app authenticate against the DB server and never have to setup keberos or anything more complex than a named user account and custom app pool. This is really the way to fly IMHO as there are no passwords and no dealing with encryption or key management issues.

share|improve this answer
Can you expand on this Wyatt? Because I can't seem to get this working. Let's say you want users to authenticate against IIS with Windows Auth, and you want ASP.NET / IIS to talk to SQL Server with a particular NT ID - call it testuser. Then you have to run ASP.NET as testuser, right? And then in order to get Kerberos working you have to set up an SPN for testuser against that server. This is what I've found to be the case. What am I doing wrong? You can look at my serverfault post that I linked to for more details. – RationalGeek Feb 23 '11 at 19:08
How are you making ASP.NET run as "testuser"? – Wyatt Barnett Feb 23 '11 at 19:16
By running ASP.NET as testuser. So in IIS under the app pool, you can configure the app pool to run as Network Service (the default), or a particular NT ID. – RationalGeek Feb 23 '11 at 19:55
Ok, this particular scenario should work -- or at least I've used it in production for years without anything like you are running into on the IE side of things. – Wyatt Barnett Feb 24 '11 at 13:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.