views:

153

answers:

4

Here's the situaiton. I'm working on developing a new website to access an old database. This is a DoD installation so there's lots of security around.

The current application is written in classic ASP, VBScript and some javascript. The new systems is ASP.NET.

Accessing the database in the old system meant hitting the server with your own credentials (domainname\username). Now I'm trying to test some of the early development I've done. When I used Cassini (under VS2008), I had no trouble getting to the database because ourdomain\myusername registered with the SQL Server instance as a trusted connection. Due to security aspects that I have to write, Cassini can't serve as a test server anymore - I have to use IIS (we have security card readers here). Cassini can't handle them.

So when I went through all the problems of getting the appropriate accounts added to Administrators on my local pc so that I could debug in VS2008 while using IIS, I tried to connect to the database and I was rejected because MYPC\ASPNET was not a trusted connection.

Altering the existing database is out of the question. Hard coding usernames and passwords for access to the database is out of the question.

I asked the DBA if he could add MYPC\ASPNET to of the domain groups so that SQL Server could see it as a trusted connection (since MYDOMAIN\MYNAME was in a group that was seen as a trusted connection). He tells me that is not technically possible.

In the end there are going to be three or four machines (mine, another developer, the eventual live web server and a future test web server) who's ASPNET accounts are going to be hitting our two SQL servers (live and test).

What do I have to do to make the existing SQL server see me as Friend and not Foe? I looked at impersonation but I get the impression it's not compatible with our system - the business rules make a call to a common routine to create a SqlConnection object and open it (maybe even a SqlTransaction object to go with it) and this object is used for the rest of the business rules and data-access layer until it's done. It didn't look like impersonation would persist once the SqlConnection was opened (and passed, ByRef back to the calling routine)

Thanks in advance for any advice.

A: 

Use Impersonation

John Sheehan
A: 

Sounds like you want to impersonate the client who is accessing your web site correct? Have you tried to use impersonation or are you assuming it won't work?

Edit

As Albert points out, impersonation requires the user to be authenticated using Windows authentication. You will want to disable Anonymous Access, and enable Windows Authentication in IIS Management tool.

JoshBerke
In what I've read so far in MSDN pages on Impersonation I haven't yet found an answer. I put a "<identity impersonate="true" />" into the web.config and the error now says that "(null)" user is not a trusted connection. Progress, but not a complete answer yet.
David
That happens because you have allowed anonymous users. You have to allow only Windows Authentiacation in IIS config
Albert
Eventually, I have to allow anonymous access because we have users not only in our local domain but coming in from a 'portal site' - unfortunately I don't know much about what information will be available to me from those user's contexts.
David
In that case your only option is to configure application pool to run in the context of a domain account
Albert
Your going have to get your red tape out
JoshBerke
For some reason, disabling anonymous access worked and the website now comes up - when I'm running it in VS2008. If I go into a browser and say https://mypc/mysite - I get refused with a 403 error (since anonymous is turned off)
David
+1  A: 

You have two options:

  1. Run your web application in an application pool configured to run in the context of a domain account
  2. Use impersonation and configure your web application to use windows authentication only
Albert
#1 isn't an option. #2 is a mystery so far and I'm hoping the answer is in Impersonation somewhere.
David
Why number one is not an option? Based on your description it is the best option.
Albert
I'm at a DoD installation and do not have the credentials nor the red tape supply to go the application pool route.
David
There's nothing wrong (security wise) to run your application pool in the context of a domain account. You just have to make a single change to IIS
Albert
A: 

As has already been suggested you should use impersonation.

However if your SQL Server is running on a different machine than your web server then impersonation will not suffice as the credentials of the user will not be delegated to the SQL Server (server hop). In that case you will have to either enable delegation in the AD or create a non-Windows login on your SQL Server and use that instead (this will not work if your SQL Server actually uses the Windows login for access control to tables etc.).

Jakob Christensen
This is *exactly* the problem. The web server, even in production, will always be different from the SQL server. But creating SQL Server logins introduces security concerns - as in where/how to store the username/passowrd to stick in the connection string?
David
There are ways to secure the credentials in connection strings (http://msdn.microsoft.com/en-us/library/dx0f3cf2.aspx) but I'm not sure if that's your best option.
Albert
Allowing delegation on some accounts also poses a security problem. I am not sure what is the best solution but encrypting parts of the web.config file is certainly the easiest.
Jakob Christensen