Hello,

I hate to be the 12345th person to request assistance on this topic, but despite looking at previous requests, I can't seem to find the solution. Any assistance you could offer would be much appreciated.

Background

I have a new ASP.NET 2.0 web application which is hosted on (for argument's sake) WEBSERVER (using IIS 6.0), and which accesses data on SQLSERVER (using SQL Server 2000).

Because this is an intranet application, I want the application's authentication mode to be Windows, and the IIS authentication mode to be Integrated Windows (with Anonymous access disabled). ie: IIS should authenticate the user as smithjohn and SQLSERVER should also authenticate the user as smithjohn.

I don't know if this is relevant, but because of an existing nasty Windows application, all relevant domain users already have their own <DOMAIN>\<username> User account on SQLSERVER.

Error

When I build and browse the pages in Visual Web Developer Express, I can retrieve information from SQLSERVER without any problems.

When I publish the site, and upload it to WEBSERVER, I get the following error upon attempting to retrieve data from a stored procedure on SQLSERVER:

System.Data.SqlClient.SqlException: Login failed for user '<DOMAIN>\<WEBSERVER>$'. at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at snapshot.btnGo_Click(Object sender, EventArgs e)

More Detail

• Connection String (in Web.Config):
• <add name="strConnMyDatabase" connectionString="Data Source=SQLSERVER;Initial Catalog=MyDatabase;Integrated Security=True" providerName="System.Data.SqlClient"/>
• Authentication Type (in Web.Config)
• <authentication mode="Windows"/>
  
•  There are no impersonation settings defined in Web.Config
• IIS 'Directory Security' settings are:
• Anonymous Access disabled
• Integrated Windows authentication enabled
• The stored procedure (usp_MyProcedure) has the following execute permissions:
• DOMAIN\Domain Users
• SQLSERVER\MyRole (to which my SQL Server User account belongs)

Questions

1. Is it possible to achieve the security settings I've outlined, or are they unfeasible / unnecessary? My assumption is that if I use an 'ASPNET' user account, I'll lose the ability to distinguish between (for example) a Marketing department user, and a Finance department user, and that information may be useful if I develop more sensitive data pages in the future.
   
2. How do I resolve the SQL Exception I'm generating?

Thank you for your time - I apologise for the length of the post, but I wanted to try to provide any relevant information.

The following article pretty much takes you through the process that you need to complete step by step.

http://support.microsoft.com/kb/810572

A couple of things to note.

It works on the VWD Express Web Server because that always run its little web server process using the identify of the user on the machine. Therefore, when it tries to access the database, it's doing it as you. With IIS, it uses a system account that has no right to access the SQL Server, unless you turn on impersonation/delegation.

Hi Dave,

Many thanks for your quick reply  I've been working through your linked solution. Because I don't understand the ramifications fully, I'm not comfortable implementing delegation on the webserver, but all other elements have been implemented. It didn't solve my problem, so I imagine that not implementing delegation on the webserver is preventing me from succeeding.

Given my reluctance, I suppose I'll simply have to allow anonymous access, and provide a domain account for the app to run as.

However I really can't believe so many things need to be changed in order to run the application as the logged-on user. Is there really no other way? I've tried implementing impersonation (without providing a username/password) but, the error message simply changes to Login failed for user: (null).

I'm using MS products all along the line, from the webserver and SQL server to Active Directory and the workstation. It knows who I am , why does it make it so hard to let me in?

If you (or anyone else) has anything else that I might try, it would be much appreciated - thank you in advance for your time.

Hi Iori,

Thanks for taking a look at my problem

I don't want to use a universal credential. I want each user to be authenticated as themselves (ie: the logged-on user), so that I can restrict access to sensitive pages. eg: Restrict access to some pages to managers only.

- Sarah 

Restricting access to pages is something that should be done at the Web Server, not the database server. This is handled entirely within ASP.NET using its authentication and authorisation modules (the mode you're looking for is called Windows authentication, because it relies on IIS to authenticate the user so that ASP.NET can then authorise them). This mode fully supports individual user and role-based (Windows Groups) authorisation for pages.

If you are prepared to implement a security model known as trusted subsystem, you can use a single ASP.NET account to access the database. This would then not need the web server to use impersonation/delegation, and you would gain additional benefits such as connection pooling, which helps performance. The cost of trusted subsystem is that you cannot audit or secure access at the database level without writing additional SQL code, because a single account is used to access the db for all users. The model is known as trusted subsystem because the db server trusts the web server to perform relevant authentication and authorisation checks (i.e. it trusts another system within the application).

Trusted subsystem is the model that is used in by far the majority of web applications today.

The reason that delegation requires so much effort to set up is because of the inherent security risks involved. For example, if malicious code makes it into the web server, which is configured to impersonate users, and your domain admin then browses to the web app, that malicious code would execute with those domain admin level permissions. When that happens, it can be "bye bye" to your system. Therefore, you need to go through several security hoops to make sure that when you configure impersonation/delegation, you do so with an open (and very security focused) mind.

Compare that with the trusted subsystem model, where the web application is configure to run with minimum access (to the domain and the db, at least) then the impact of a compromised server can be somewhat limited.

Hi Dave,

Thank you so much for taking the time to explain the pros and cons of each system. From what you've said, the 'trusted subsystem' option is perfect for my situation (particularly since the database access is in this application consists entirely of select statements - no 'dangerous' action queries).

It's also a pleasant surprise to hear that the Windows Groups can be used - that's terrific.

Thanks again

Sarah