Windows authentication/impersonation.. login failed for user null?

[Not Me]

Hey,

We have an sql server 2000 machine and IIS 6 machine running seperately
but on the same domain. I can connect fine to the database without using
impersonation, but when it's enabled I get the error:
"Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection."

When I check System.Security.Principal.WindowsIdentity.GetCurrent().Name I
get the valid domain user that I would expect, why isn't this user being
checked against the sql server?

cheers for any help,
Chris

 

[Norman Yuan]

You must make sure that domain user account or the domain security group
(that domain user account is in) ia mapped to a SQL server login/Sql Server
database user. That is, not all domain user account is automatically allowed
to access SQL Server, you need to explicitly create SQL Server login that
maps to a domain group/user, and then make specific SQL Server login as
given database's user/owner...

 

[Norman Yuan]

You must make sure that domain user account or the domain security group
(that domain user account is in) ia mapped to a SQL server login/Sql Server
database user. That is, not all domain user account is automatically allowed
to access SQL Server, you need to explicitly create SQL Server login that
maps to a domain group/user, and then make specific SQL Server login as
given database's user/owner...

 

[SAL]

Chris, I just, agonizingly, went through this same scenario. Here was my
problem.

In IIS, I opened the properties of my web application and I had to change
the default user (on the Directory Security Tab) that was entered there to
my domain account (or some other user's domain account that could log on to
the machine that SQL Server was residing on). Then I had to uncheck the Let
IIS manage the password checkbox and enter in my own password. I had to
leave Integrated Windows Authentication checked.
In my web.config, I had to have this tag:

<identity impersonate="true" />

When publishing, I believe I had to uncheck the Integrated Windows
Authentication to make it work on machines other than my dev box.

HTH
S

 

[Not Me]

You must make sure that domain user account or the domain security group
(that domain user account is in) ia mapped to a SQL server login/Sql
Server database user. That is, not all domain user account is
automatically allowed to access SQL Server, you need to explicitly create
SQL Server login that maps to a domain group/user, and then make specific
SQL Server login as given database's user/owner...

Thanks for the help Norman.. I'm still a bit confused though (I need to
explain this to the admins for them to make the changes).

I have an account made up on the sql server, for the security group that I
expect the users to reside in. This isn't an sql server login though, does
it need to be?

If I turn off impersonation, and have the username set up in IIS directly..
it works, it's just when I want to pass the credentials of whoever is logged
into the machine, via IIS/ASP to the SQL server, that it breaks.

cheers,
Chris

 

[Not Me]

Chris, I just, agonizingly, went through this same scenario. Here was my
problem.

In IIS, I opened the properties of my web application and I had to change
the default user (on the Directory Security Tab) that was entered there to
my domain account (or some other user's domain account that could log on
to the machine that SQL Server was residing on). Then I had to uncheck the
Let IIS manage the password checkbox and enter in my own password. I had
to leave Integrated Windows Authentication checked.
In my web.config, I had to have this tag:

<identity impersonate="true" />

When publishing, I believe I had to uncheck the Integrated Windows
Authentication to make it work on machines other than my dev box.

Thanks SAL, I'll also pass this to the admins to see if we can work it out.

cheers,
Chris

 

[Norman Yuan]

You need to know which user account on the IIS computer is used to run you
ASP.NET APP (you should know that, as ASP.NET app developer, so you can make
sure your ASPP.NET app runs within the security scope you expected).

Since ASP.NET runs on top of IIS, both IIS configuration and ASP.NET
configuration take place here.

Assume you use Windows Authentication in your ASP.NET (by default). If you
set "impersonate" to true, then the user account running your asp.net app
is determined by if your IIS/App virtue directory allows anonymous access.
If yes, the user account would be the default IIS running account
(IUser_MNachineName, by default, or the account you entered directly into
the IIS, as you described); if not, the ASP.NET app will "impersonate" to
the user who requests the ASP.NET. If you do not use "impersonate", the
APS.NET running account would be ASPNET or Network Service .

So, you really need to

1. As app developer, have clear idea which user account is designed/expected
to run your asp.net app. You need to consider do the configarations in both
IIS and your ASP.NET's web.config, so that the user account used is safe to
the server/network.

2. Once you know which user account (it can be a local account in the IIS
server, or a domain user account), you can configure the SQL Server to give
that account appropriate access.