ASP.NET Impersonation 1

[Akusei]

I have one big headache of a problem here! I can't seem to figure this out.

I have 2 servers. 1 has IIS and the other has SQL. I want a user to go to my website (from inside the company.. Intranet), use Integrated windows authentication to access the website which in turn impersonates this user and connects to SQL Server.

I have anonymous access disabled, Integrated Windows Authentication is enabled.

Both these options are set in the Web.config

<authentication mode="Windows />
<identity impersonate="true" />

The user is impersonated correctly on the IIS server; however, when it goes out to the SQL server it tries to authenticate as NT AUTHORITY\ANONYMOUS LOGON. I do not want this, I want the users identity to flow through to the SQL Server using impersonation. What am I doing wrong?

Thanks

 

[aftertaf]

check your sql connection string.....

use SSPI
(not at work, cant paste you an example.... can do monday if you wish!)

 

[Akusei]

This is the connection string I am using already

Data Source=SERVER;Integrated Security=SSPI;Initial Catalog=DATABASE

Any other ideas? I heard that if you have IIS and SQL on seperate machines, then the authentication needs to be Kerberos and not NTLM which is the default. NTLM is only good for one hop, and Kerberos is good for multiple hops. Anyone know a solution to my problem?

 

[iambrunner]

*bump*

I'm having exactly the same problem. I've tried the following two connection strings:

Server=10.0.0.3; Database=tmcweb; Trusted_Connection=yes

Server=10.0.0.3; Database=tmcweb; Integrated Security=SSPI

Does anyone know what could be causing this?

Thanks in advance.

 

[aftertaf]

what is the authentification mode on SQL server that you are trying to use.. Windows integrated?

if you create an sqlconnection object from within vs.net can you authenticate to the server and the database?? if so, with which account?

how is your IIS setup for user authentification?

Aftertaf

getting quite good at sorting out Windows problems...
An expert when it comes to crashing Linux distributions (mdk, debian - nothing withstands me)

 

[Warlocks]

Hi,

It would seem that quite a few are having similar issues with this type of connection.
Each user on my project will have an Active Directory account (Windows 2003). Basically the NT account.
The web site directory where the source code/application is held is protected by this authentication method so the user HAS to have an AD account before proceeding.
I have set the web.config line (using appSettings) to:

<add key="ConnectionString" value="server=SERVER;Trusted_Connection=Yes;Database=DB" />


I have also used "Integrated Security=SSPI" but get the same error.

Although I have entered my credentials to access the actual application directory, when trying to do any database activity, I get the error:

Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.

even though I have a specific login...

Does anyone have the answer?

Woody.

 

[dace]

As Akusei said NTLM is only good for one hop, which is used up to authenticate the request to IIS, so any authentication attempt to a network resource will fail. One solutions would be to use Basic authentication, which should work because the NTLM auth then won't be used for IIS authentication so it should be free for the hop to the SQL server. Another would be to set up a dedicated username and password for the app to access the SQL server and pass them explicitly in the connection string:
server=SERVER;Trusted_Connection=No;user=USER,password=PASSWORD;Database=DB
However, this may not be a solution for some because the username/password will be sent in plaintext (shouldn't be a problem on a LAN), and this won't work if you're enforcing data access at the SQL server level (ie, your app users have different data access rights to the SQL server).

 

[Warlocks]

Thank Dace.
Moving on, we are looking at actually using .NET linking to Active Directories to manage the user information and login to the site.

Can I not get this information and use it to make the connection to SQLServer?
One major factor is that we also need to audit any changes and I would rather use triggers on the tables rather than through code and so we can use the user login to also record who made the change (hence why we don't really want one login for the whole app otherwise auditing will have to be done through code...)

Cheers,

Woody.

 

[Rhys666]

If you change the IIS setup of the site to disable anonymous access and use windows authentication you'll get the users NT logon.

By default anonymous access is allowed on the site when you create a new web project thru' vs.

Rhys

""Vampireware /n/, a project, capable of sucking the lifeblood out of anyone unfortunate enough to be assigned to it, which never actually sees the light of day, but nonetheless refuses to die."

My Home

 

[Warlocks]

Rhys,

I have done this at IIS and was under the same impression as you, that it would take the NT Login and use it to open the database with... But it doesn't. This is unless I have the setting incorrect in the web.config file (which it currently uses "...Integrated Security=SSPI..."

Woody.

 

[Rhys666]

I have done that and successfully accessed a dB.

have you got the following tags set in the web.config file?

  <system.web>
    <authentication mode="Windows" /> 
    <identity impersonate="true" />

Rhys

""Vampireware /n/, a project, capable of sucking the lifeblood out of anyone unfortunate enough to be assigned to it, which never actually sees the light of day, but nonetheless refuses to die."

My Home

 

[Warlocks]

Rhys,

You are wise and I am not worthy...

The little bit about imporsonate seemed to do the trick..

Many thanks and the post has been accredited to you..

Cheers,

Woody.

 

[Rhys666]

You are more then worthy - you are Woody ;-)

It was actually my first issue when trying to do the same...

Rhys

""Vampireware /n/, a project, capable of sucking the lifeblood out of anyone unfortunate enough to be assigned to it, which never actually sees the light of day, but nonetheless refuses to die."

My Home