Probably a simple question

[Brian Cobb]

I want to pass the security credentials of the currenty logged on user with a Windows domain account to an asp.net web service. This security info then needs to be captured so that it may be used to authenticate against a SQL server database. Assuming it can be done, Can someone point me to the docs, a tutorial, or a sample that shows and/or discusses how to do this?

Thanks in advance.

 

[avnrao]

check this kb article
http://support.microsoft.com/default.aspx?scid=kb;en-us;813834

Av.

 

[brianc]

Thanks. That gets me authenticated to the web service, but how do I pass those credentials onto SQL? I realize I can impersonate a user; I've already successfully tried it. My first choice would be to pass the actual Windows user credentials of the current logged on user to SQL, though.

(Integrated security=sspi works like a charm when I am doing this all locally, btw, i.e. my SQL connection happens in a method or an instance of another class.

Whenever I try using integrated security in my connection string I always get back the message that it can't connect using
NT Authority\Anoymous logon. The relevant Web.config entries appear to be

<system.web><identity impersonate="true" /><authentication mode="windows" /><authorization><allow roles="servername\groupname" /><deny users="*" /></authorization
...



----- avnrao wrote: ----

check this kb article
http://support.microsoft.com/default.aspx?scid=kb;en-us;81383

Av

 

[Joe Kaplan \(MVP - ADSI\)]

You need Kerberos delegation in order to be able to hop from machine to
machine to machine or you need to ensure that you are securely transporting
plain text credentials using something like Basic authentication and SSL.
There are some good KB postings on Kerberos delegation that will be very
helpful.

Joe K.

Thanks. That gets me authenticated to the web service, but how do I pass

those credentials onto SQL? I realize I can impersonate a user; I've
already successfully tried it. My first choice would be to pass the actual
Windows user credentials of the current logged on user to SQL, though.

(Integrated security=sspi works like a charm when I am doing this all

locally, btw, i.e. my SQL connection happens in a method or an instance of
another class.)

Whenever I try using integrated security in my connection string I always

get back the message that it can't connect using

 

[Alek Davis]

Brian,

You are encountering the delegation issue. You can probably find a lot of
similar posts if you search for "delegation", "SQL Server", "ASP.NET",
"Kerberos", etc. In short, in a typical corporate environment you cannot do
what you want. And by typical environment, I mean that you have three
physical systems involved (Web browser, IIS, and SQL Server), which belong
to an Active Directory domain, and your ASP.NET site is protected using
integrated Windows authentication (IWA). Now, if instead of using IWA, you
use basic authentication, it will work. It will also work if you move SQL
Server or Web browser (either one) to the same machine where IIS runs. But
if you do not do any of these changes you will run into the delegation
problem. The problem here is that in a typical environment, impersonated
credentials do not cross the machine boundary. So while IIS can recognize
the user making the call and can use user's credentials for all local
authorization calls, as soon as an outgoing call is made (to a SQL Server,
Oracle, Web Service, or whatever), the credentials of IIS process (not
impersonated user) will be passed. There is a way to allow passing
impersonated credentials through the machine boundary (as Joe mentioned),
but it requires changing domain security settings, which are generally not
recommended. If you can and are planning to make these changes (to enable
Kerberos delegation), you must be aware of the associated security risks.

Alek

Thanks. That gets me authenticated to the web service, but how do I pass

those credentials onto SQL? I realize I can impersonate a user; I've
already successfully tried it. My first choice would be to pass the actual
Windows user credentials of the current logged on user to SQL, though.

(Integrated security=sspi works like a charm when I am doing this all

locally, btw, i.e. my SQL connection happens in a method or an instance of
another class.)

Whenever I try using integrated security in my connection string I always

get back the message that it can't connect using

 

[avnrao]

have you resolved the issue? just to check, do you really need to access SQL
server using the logged in user credentials?

I am asking this because, most of the database designs are such that the
database needs to know what is the user name and show the data respectively.
and they do not check if a particalar user has execute permission on a
certain stored proc or any other permission on DB object. maintaining these
permissions/users is cumborsome when users are stored in domain (for a
pretty huge number).

if you can put down your requirement, it would be more helpful for us to
check if your way of approax is correct.

Av.

 

[Alek Davis]

By the way, in addition to security risks, there is one more problem with
passing user's credentials to the SQL Server. The problem here is that you
will not be able to use connection pooling, because connection pooling only
works if a database connection is made using the same user's credentials and
in the delegation scenario it is not the case. This is why, most
applications just keep a separate SQL login ID for database operations (all
authorization is normally done in the middle tier).

Alek

Thanks for everyone's help with this. I will investigate the kerberos

authentication option at some point. No, there is no specific requirement
that users authenticate against sql using their current credentials. I
don't think there is any need for anyone, other than myself of course, to
pursue this issue further at this time.