Windows Authentication problem with Webservice and SQLServer

[Andrew]

I am having a problem using Windows Authentication
IIS has been configured to accept only Windows Authentication.
The web.config file is set to allow impersonation.

My WebService connects to a SQLServer database using the credentials of
the impersonated user.

If I connect to the webservice via a browser the call to the db has no problems
and my data is returned (this is mostly true but sometimes it fails).
But,
If I connect to the webservice via a web form or a windows application, I
authenticate with the Webservice OK, examination of my Windows Identity states
my user is who it is supposed to be, with the correct domain and name. The
call to the database fails when I call Open() on the connection.
The exception message is:
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'
It's like the database is receiving the wrong user credentials.

Does anyone know what is going wrong?

Thanks Andrew

[Taiwo]

If your client application, IIS, and SQL Server are on different machines,
then you need kerberos delegation (Windows 2000 or recent OS) to make this
work.

This book provides very good guidance:
http://www.amazon.com/exec/obidos/AS...9081/ref%3Dsr%
5F11%5F0%5F1/104-9375870-7874347

Also see the following at
http://msdn.microsoft.com/library/de...us/dnsql2k/htm
l/sql_security2000.asp:

Kerberos and Delegation in Windows 2000 Environments
Kerberos is the primary authentication mechanism on Windows 2000 networks.
Delegation is the ability to pass security credentials across multiple
computers and applications. With each "hop" between computers, the user's
security credentials are preserved. SQL Server 2000 fully supports Kerberos,
including the ability to accept delegated Kerberos tickets, as well as
delegate these tickets further (when running on the Microsoft Windows 2000
operating system), with Windows 2000 domain controllers and Active
Directory. This affects remote stored procedures as well as distributed
queries. For more information about Kerberos and Windows 2000 security, see
Windows 2000 Security Services and review the relevant technical papers.

To configure delegation, all servers that you are connecting to must be
running Microsoft Windows 2000 with Kerberos support enabled, and you must
be using Active Directory. The following must be set in Active Directory for
delegation to work:

a.. Account is sensitive and cannot be delegated. This option must not be
selected for the user requesting delegation.
b.. Account is trusted for delegation. This option must be selected for
the service account of SQL Server.
c.. Computer is trusted for delegation. This option must be selected for
the server running an instance of Microsoft SQL Server.
To use security account delegation, SQL Server must have a Service Principal
Name (SPN) assigned by the Windows 2000 account domain administrator. The
SPN must be assigned to the service account of the SQL Server service on
that particular computer. Delegation enforces mutual authentication. The SPN
is necessary to prove that SQL Server is verified on the particular server,
at the particular socket address by the Windows 2000 account domain
administrator. Your domain administrator can establish an SPN for SQL
Server. For more information about the setspn utility, see the Windows 2000
Resource Kit documentation.

To create an SPN for SQL Server 2000

a.. Run the following command:
setspn -A MSSQLSvc/Hostort serviceaccount

For example:

setspn -A MSSQLSvc/server1.redmond.microsoft.com sqlaccount

You must be using the TCP/IP Sockets Network Library for delegation to work.
You cannot use Named Pipes because the SPN targets a particular TCP/IP
socket. If you are using multiple ports, you must have a SPN for each port.

You can also enable delegation by running under the LocalSystem account. SQL
Server 2000 self-registers at service startup and automatically registers
the SPN. This option is easier than enabling delegation using a domain user
account; however, when SQL Server 2000 shuts down, the SPNs will be
unregistered for the LocalSystem account.

---
This Posting is provided "AS IS" with no warranties, and confers no rights.

Taiwo

"Andrew" <andrew.mason@spektra.co.uk> wrote in message
news:3ca2f7c4$1@10.1.10.29...
>
> I am having a problem using Windows Authentication
> IIS has been configured to accept only Windows Authentication.
> The web.config file is set to allow impersonation.
>
> My WebService connects to a SQLServer database using the credentials of
> the impersonated user.
>
> If I connect to the webservice via a browser the call to the db has no
problems
> and my data is returned (this is mostly true but sometimes it fails).
> But,
> If I connect to the webservice via a web form or a windows application, I
> authenticate with the Webservice OK, examination of my Windows Identity
states
> my user is who it is supposed to be, with the correct domain and name. The
> call to the database fails when I call Open() on the connection.
> The exception message is:
> Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'
> It's like the database is receiving the wrong user credentials.
>
> Does anyone know what is going wrong?
>
> Thanks Andrew