Windows Authentication

  • Thread starter Thread starter Terry Holland
  • Start date Start date
T

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
 
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)
 
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
Click to expand...
rule). you will need to switch to kerberos and enable credentials
Click to expand...
forwarding on the servers involved, or switch to basic (which would give
Click to expand...
 
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
____________________________________________________________________________________________
 
Back
Top