Anonymous access (IIS) and SQL Server

Question

I just had an interview in Redmond where they asked me a ton of security based questions surrounding asp.net. One of the questions they asked was about configuring a secure intranet application to use constrained delegation to access the SQL Server. In this scenario an AD user account is delegated access to the SQL Server. The whole purpose of course is to a) not store any username/password anywhere on the web server (web.config), and b) provide an abstracted security model that can be managed in Active Directory.

This got me to thinking about how I have been configuring my sites for anonymous access all these years. Typically I will run my IIS websites using the default anonymous account and store the connection string in the web.config (encrypted, and sometimes in clear text). This, of course, requires your SQL Server to run in mixed mode. So my question is, what if we didn't store the connection string in the web.config at all, and just created a unique anonymous domain account for the particular website that would have db_datareader access within SQL Server? Is there any reason why it would be a bad idea to do this?

I have tried to think of all the scenarios where this would be a bad idea, and the only one I can think of is where a "hacker" compromised the code on the web server, and then somehow got access to your SQL Server...but this could happen in either scenario.

Does anyone know the best practice here?

Answer 1

Maybe you could use ODBC to create a DSN for the SQL Server connection. Then your web.config only needs to know the DSN. This may require you to use System.Data.OleDb. I've never seen DSN used in ASP.NET but it used to be pretty standard for Classic ASP. And I've never heard of Active Directory being used to manage ODBC.

Answer 2

Where I work, we have a windows service that runs under a specific domain account. That account is setup in SQL Server as a login and it has a matching user in the DB it needs access to. We have never had any problems with this.

I think the most important thing is to properly configure your database user (or role) so it only has access to what it needs.

I have considered using AD to manage SQL access in a similar manner that you describe in your first paragraph. (AD Group -> SQL Server login -> DB User -> DB objects) The only drawback I can see so far is, if a user connected directly to the database, they would bypass any logic you have in your app. One benefit is, you know what domain users are accessing your database.