Windows Authentication

[Terry Holland]

Not sure if this question best placed in ASP or SQL group so trying both...

I have an internal asp.net application that connects to an sql server 2005
database. We are using Windows authentication. When the SQL database was
on the same box as the iis server, things work working fine. Now we are
ready to deploy to live environment where sql database in on seperate box to
iis server and we are getting authentication errors. It seems that the user
credentials being passed to sql are for anonymous user and not those of the
user logged onto client machine.

I have read info at http://msdn.microsoft.com/en-us/library/ms998292.aspx.
From this I have set the following in the web.config but this has had no
effect.

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


Createing a dummy page I am able to see that IIS knows whoe the logged on
user is but these credentials are not being passed to SQL

This problem needs to be resolved ASAP so any help would be appreciated

Regards

Terry Holland

 

[bruce barker]

this by design. windows authentication does not allow forwarding (1 hop
rule). you will need to switch to kerberos and enable credentials
forwarding on the servers involved, or switch to basic (which would give
the iis server a primary token with which to access sqlserver).

if you don't need to use the users credentials, set impersonate to
false, and give the pool a domain account with access to sqlserver

-- bruce (sqlwork.com)

 

[Terry Holland]

Thanks for responce bruce.

could you point me to a good "step by step" source of info for setting up
kerberos for the scenario I have described. The method I use is not set in
stone at this point but I would like to know what is involved in going down
the kerberos route. The client requested object level permissions based on
ad accounts as the db will be accessed by a number of different
applications. securing the data at db level ensures that only people who
have authority to access data can, regardless of the client application they
use.

Terry

this by design. windows authentication does not allow forwarding (1 hop

rule). you will need to switch to kerberos and enable credentials

forwarding on the servers involved, or switch to basic (which would give

 

[William Vaughn \(MVP\)]

I, for one don't recommend SSPI authentication. Yes, that's not the
Microsoft POV. However, I think it's dramatically easier to use
application-specific SQL Server credentials that are managed through custom
user-authentication. Once the user identity is established, your code can
choose an appropriate SQL Server role/login account that appropriately
limits rights. This might mean that you keep a table that maps users with
the groups to which they belong but there are other approaches. I think it's
far simpler to configure and it does not require complex (and somewhat
brittle) Kerberos authentication configurations.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________

 

[bruce barker]

http://www.microsoft.com/windowsserver2003/technologies/security/kerberos/default.mspx

-- bruce (sqlwork.com)