ASP.NET + SQL Server Windows authentication

[Lior Amar]

Hey All,

Trying to understand why I can not get SQL server to trust my IIS server. I
have two machines set up, 1 App and 1 DB, and I'm trying to validate the
applications access to the DB server via NT Authentication. The App comes in
via NTLM which from my understanding only supports Single hop security
delegation. So far I understand why it doesn't work, although seems to me
like a very bad problem. Now, Basic Authentication will transfer the PW and
the UID which will allow IIS to login to the DB server and then NT
Authentication will work. But we all know how non-secure Basic
Authentication is.

Here's the confusion, if Kerberos permits token transferring with no
limitation why can't IIS receive a token via NTLM and transfer it to the DB
server?

I've been reading all of these articles

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/
vbconaccessingsqlserverfromwebapplication.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/
vbtskaccessingsqlserverusingwindowsintegratedsecurity.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnauth/html
/dnauth_security.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnauth/html
/signfaq.asp
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q176377

and a bunch of other documents and they all come down to two valid
solutions: Basic Authentication or SQL Users. These are only valid if the
level of security you wish to achieve is not something that needs to pass a
certain level of security (would not pass in industries that require maximum
security).

If I am bound to NT Authentication, is my only option Basic Authentication
(of course under SSL)? And why is it that we don't have these problems with
other Database vendors? Is there any way we can utilize ADSI to get the
users NTLM credentials to pass on to SQL server?

Any help or suggestions will be very appreciated.

Thank you,

 

[Sherif ElMetainy]

Things that you have to check are:


1- What is the account the webserver is using? in asp.net using default
configuration (no impersonation), it is ASPNET, it can be the
IUSR_MachineName account, or any other account.
in asp.net you can easily find out with this code
Response.Write(System.Security.Principal.WindowsIdentity.GetCurrent().Name);
to change the username underwhich the code executes for asp.net change the
<identity> in machine.config

2- Is this account a local account or a domain account?

If it is a domain account, then check that in the SQL server security that
the is permitted to access the server, and has access to the its default
database (or the database specified in the connection string).

If it is a local account, then use a domain account.

If there is no domain, then the username and password for the local account
must be valid on the database server, ie the same username and password on
both machines, I think when ASPNET account is created a random password is
generated for it. so the password is not the same for both machines, and
changing the ASPNET account password is not recommended.

In all cases make sure that the account has access to SQL Server.

 

[Lior Amar]

Think the problem is just a limitation of NTLM single hop. Don't think there
is a way around it other than using SSL and Basic Authentication. ASPNET is
set up properly and is impersonating the user approriately. Don't think
there is anyway around this limitation.

Thanks for the help though

Lior