Impersonation with SQL Server SSPI

  • Thread starter Thread starter Jason Barnett
  • Start date Start date
J

Jason Barnett

I'm running an application with the following configuration and I get a
"Login Failed for user 'NT Authority\Anonymous Logon'" error when I try to
open a connection to SQL Server. Also, the error only appears when I use the
domain account I use during development. A separate domain account (with
connect-only privileges) works fine.

My domain account can connect using Management Studio
My connection string contains SSPI
Web.config is configured with authentication mode set to windows
Web.config is configured with impersonate set to true
IIS is configured to use Windows Authentication
IIS is configured with Anonymous disabled
IIS is running on a separate server than SQL Server

I've verified that WindowsIdentity.GetCurrent() returns the domain account
I'm using. I've even tried explicitly calling Impersonate to see if that
would help, but it doesn't.

Could anyone explain what's going on and how I might correct this issue?
 
IIS is running on a separate server than SQL Server

Your situation is still a little fuzzy to me but the above sounds like
you're accessing IIS on machine B from machine A and machine B is then
trying to connect to SQL server on machine C (using your domain
credentials). If so then my first guess would be a delegation of credentials
problem. Basically, if you have a logon session going on machine B using
your credential from machine A and machine B then tries to connect to
machine C using those same credentials, it won't be able to do so unless
it's using the "SecurityDelegation" impersonation level. This is likely the
reason you're getting 'NT Authority\Anonymous Logon'. Machine B is trying to
access machine C using anonymous credentials since it's not allowed to use
your original credentials to access network resources (note that it's almost
certainly using the "SecurityImpersonation" impersonation level). This is
basic Windows security. I know it's difficult to digest without a background
in this stuff but you're going to have to. Start here:

http://msdn.microsoft.com/en-us/library/aa379572(VS.85).aspx

And do some research (painful I know). You need to understand the
ramifications of using "SecurityDelegation" which normally isn't safe. That
is, it allows your credentials to be passed to more than one machine which
is dangerous if one of those machines is ever compromised. The machine would
then have access to your credentials and can potentially do all sorts of
damage under your name (the reason for sticking with "SecurityImpersonation"
instead since you get one network hop under your credentials and that's
all - in your case it's the call to machine B). Note that it's almost
usually better to have machine B access machine C using a different account
instead of your original credentials. I can recommend a very good book on
Windows security if you're interested (for programmers).
 
Do you refer any link server in your stored procedures or view. If so then
you have to provide the remote user impersonation with global domain
account.
 
Jason Barnett said:
I'm running an application with the following configuration and I get a
"Login Failed for user 'NT Authority\Anonymous Logon'" error when I try to
open a connection to SQL Server. Also, the error only appears when I use
the
domain account I use during development. A separate domain account (with
connect-only privileges) works fine.

My domain account can connect using Management Studio
My connection string contains SSPI
Web.config is configured with authentication mode set to windows
Web.config is configured with impersonate set to true
IIS is configured to use Windows Authentication
IIS is configured with Anonymous disabled
IIS is running on a separate server than SQL Server

I've verified that WindowsIdentity.GetCurrent() returns the domain account
I'm using. I've even tried explicitly calling Impersonate to see if that
would help, but it doesn't.

Could anyone explain what's going on and how I might correct this issue?
Click to expand...

No double hop for credentials.
You need the iis machinee and sql server to be on the same machine running
in the same domain for impersonation to work under straight nt.
You can use kerberos to get round that.
Most web apps don't use impersonation and instead have a non-specific
account used to access sql server.
I would recommend not relying on impersonation.
 
Back
Top