New Windows Authentication Issues

[Bari Allen]

Yesterday I was trying to get:

WindowsIdentity.GetCurrent.Name

to work.


Finally, that appears to be working both on my local box & my web server.

Now I'm having issues connecting to the database...

My SQL Connection string is as follows:

integrated security=SSPI;data source=MyServerName;persist security
info=False;initial catalog=MyDatabaseName


The connection works fine from my local box, and I hit the SQL server & get
my data, with no problems, however, when I load the page to our Intranet
Server (which is on the same domain), I get the following error:

Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server connection"

If WindowsIdentity.GetCurrent.Name is correctly picking up my domain login,
then why is the connection string picking up Null?

Additional Background info:
<authentication mode="Windows" />
<identity impersonate="true" />
IIS is for the directory on the Web server is set up as follows:
Enable anonymous access is NOT checked
Windows Integrated Authentication is checked

Thanks in advance for any advice, here.

 

[Dominick Baier [DevelopMentor]]

Hello Bari,

you have to enabled delegation for the web server

see:

http://www.leastprivilege.com/CredentialsAndDelegation.aspx

 

[Patrick.O.Ige]

Since you added impersonate = true that means anybody that logs in and get
to that site would be using there login to access
the Database..
What auth is your SQL server using..
Have u added asp.net acct to your SQl server?
If you've done that then u don't need to impersonate user.
Patrick

 

[Joubert Nel]

Hello Bari,

I suspect the intranet setup is having issues with delegation.

When the client on Machine A authenticates with the web server on Machine B,
the user proves that he knows his password (windows auth). However, the user
never tells Machine B what the password is. Machine B has no way of proving
to Machine C (where SQL Server is running) that it really does have a valid
logon for the client, but Machine C (SQL Server) needs proof.

When you turn on auditing success + failure of logon events, you'll see that
ANONYMOUS LOGON logged onto Machine C. This happens because Machine B
doesn't have network credentials for the client - when it impersonates the
client (from Machine A) and tries to connect to the database it establishes
a "null session" instead of authenticating.

To get your scenario to work you must allow the web server to delegate
client credentials (setting in Active Directory). What's essentially
happening is that you're telling AD:
1) that Machine B is designed to pass through client credentials, and
2) that B is trusted not to abuse this privilege by using delegated client
credentials to access network resources other than the DBMS on Machine C
(this 2nd assumption can be removed, though).

Under Windows Server 2003 the Computer Account Properties dialog has a tab
"Delegation". You will see a number of options, but activating "Trust this
computer for delegation to any service (Kerberos only)" should do the trick.

Note: before this will work, the client must log off and back on in order to
refresh his Kerberos ticket cache. The next time they connect the
credentials should flow through to SQL Server.

Note 2
======
Delegation of credentials in Windows only works for the Kerberos
authentication protocol. And Kerberos isn't always used to authenticate
clients, even if the clients are using domain accounts. What is used instead
is another protocol called "Negotiate" which in turn determines whether
Kerberos or NTLM (NT Lan Manager) will be the authentication protocol used.
The latter doesn't allow credential delegation so we need to ensure that
Kerberos is used to authenticate the client. How is this done? Two things:
1) both server (B) and client (A) must use domain accounts to authenticate,
and
2) a SPN (Service Principal Name) must be used for Kerberos to function.

The client can get a Kerberos ticket for the server if it can identify the
server's domain account (this is necessary because the ticket is encrypted
using a master key that is derived from the server's password, and when it
is sent to the server, the server must be able to decrypt the ticket - this
helps assure the client that the server isn't being spoofed because the
intruder doesn't know the server's password and therefore cannot use the
ticket in a mutual authentication Kerberos handshake).

So, an SPN: just a way to uniquely identify a service running on a
particular machine and listening on a particular port.
E.g. the sys admin runs a default SQL Server instance on a box with the DNS
name "sql.sos.state.ga.us". She creates a user account (SOS\DataUser) in the
domain and configure SQL Server to use that account. She can then add the
following SPN to the SOS\DataUser user account:
MSSQLSvc/sql.sos.state.ga.us:1433
AD will now know there's an authorized instance of a service of class
"MSSQLSvc" listening on port 1433 on sql.sos.state.ga.us and that it is
supposed to run as user "DataUser" in the SOS domain. If a client requires a
ticket for the service, the ticket is encrypted with the master key for
SOS\DataUser.

If SQL Server runs with high privilege (e.g. SYSTEM) this SPN is
automatically registered each time SQL Server starts up. This exposes a
significant security hole because if an intruder can take control of SQL
Server (e.g. via SQL injection) they have significant rights on the box. So
it is a good idea to run SQL Server under a least privileged user account -
however, this requires you to register an SPN yourself. You can do this
with the "setspn.exe" tool - it is part of the support tools that comes with
Windows server versions.

To see the SPNs associated with a particular account:
setspn -L SOS\DataUser

To add the SPN mentioned earlier for SQL Server:
setspn -A MSSQLSvc/sql.sos.state.ga.us:1433 SOS\DataUser

To get delegation to work, you must have Kerberos negotiated between the
client and the web server but also between the web server and the DBMS. So,
bot the web server and the DBMS must have the appropriate SPNs. IE requests
Kerberos tickets using the HTTP service class so if the web app runs on a
box called "Webbox" under a custom domain account called "SOS\WebAccount"
you can register an SPN for that account:
setspn -A http/webbox.sos.state.ga.us SOS\WebAccount


I hope this helps you with the issue below.

Regards
Joubert