Webservice SQL logon fails on production IIS server

[Tim Baley]

I recently created some intranet web sevices to expose stored procedures on
our SQL Servers (SQL2k). The stored procedures include an audit trail with
user/login identification, so the web services are set up for Windows
integrated authentication. The web sevices work well when they are hosted on
IIS on my Win2k Pro development machine. I have tried accessing the services
under several user accounts on several machines, including machines in other
zones connected by VPN. I have connected the webservices to different SQL
servers.

When I move the services to production IIS servers, the database connection
step throws an exception "Login failed for user 'NT AUTHORITY\ANONYMOUS
LOGON'". The web service on the production service is not flowing the user
credentials through to SQL server. What do I need to do to get the service
to work?

Thanks,
Tim Bailey
Energy Laboratories, Inc.

[Martin Kulov]

Hi Tim,

Go to your Web Service virtual directory in IIS management console. Open
Properties and click on tab Directory Security. In the Enable anonymous
access select account that has rights to call the SQL server.

HTH,

--
Martin Kulov
http://www.codeattest.com

MCAD Charter Member
MCSD.NET Early Achiever
MCSD


"Tim Baley" <Ti******@discussions.microsoft.com> wrote in message
news:95**********************************@microsof t.com...

I recently created some intranet web sevices to expose stored procedures on our SQL Servers (SQL2k). The stored procedures include an audit trail with user/login identification, so the web services are set up for Windows
integrated authentication. The web sevices work well when they are hosted on IIS on my Win2k Pro development machine. I have tried accessing the services under several user accounts on several machines, including machines in other zones connected by VPN. I have connected the webservices to different SQL
servers.

When I move the services to production IIS servers, the database connection step throws an exception "Login failed for user 'NT AUTHORITY\ANONYMOUS
LOGON'". The web service on the production service is not flowing the user credentials through to SQL server. What do I need to do to get the service to work?

Thanks,
Tim Bailey
Energy Laboratories, Inc.

[Tim Baley]

Hi Martin,

Thanks for your quick response. Anonymous access is not enabled on any of
the intranet IIS web services. Our intent is to use Windows integrated
authentication for SQL Server authentication because the stored procedures
use the SQL login user for audit info.

The web services work correctly (including audit trail) when they are hosted
on my development machine. They also work correctly when the services are
hosted on an IIS server on the same machine as the SQL Server. By
"correctly" I mean that the procedures work as intended and show the domain
account of the executing client in the audit trail. In the scenario you
suggest, they would always show the webservice account in the audit trail.

The problem occurs when the webservice is hosted on a different server than
the SQL Server. SysAdmin doesn't want anything but SQL Server on the
database machine. I hope this clarifies my problem.

Thanks again for the quick response!

Tim Bailey
Energy Laboratories, Inc.

The problem
"Martin Kulov" wrote:

Hi Tim,

Go to your Web Service virtual directory in IIS management console. Open
Properties and click on tab Directory Security. In the Enable anonymous
access select account that has rights to call the SQL server.

HTH,

--
Martin Kulov
http://www.codeattest.com

MCAD Charter Member
MCSD.NET Early Achiever
MCSD


"Tim Baley" <Ti******@discussions.microsoft.com> wrote in message
news:95**********************************@microsof t.com...

I recently created some intranet web sevices to expose stored procedures

on

our SQL Servers (SQL2k). The stored procedures include an audit trail

with

user/login identification, so the web services are set up for Windows
integrated authentication. The web sevices work well when they are hosted

on

IIS on my Win2k Pro development machine. I have tried accessing the

services

under several user accounts on several machines, including machines in

other

zones connected by VPN. I have connected the webservices to different SQL
servers.

When I move the services to production IIS servers, the database

connection

step throws an exception "Login failed for user 'NT AUTHORITY\ANONYMOUS
LOGON'". The web service on the production service is not flowing the

user

credentials through to SQL server. What do I need to do to get the

service

to work?

Thanks,
Tim Bailey
Energy Laboratories, Inc.

[Martin Kulov]

Hi Tim,
Sorry I haven't read quite carefully.

Do you have the two machines in one domain? Or they are just in workgroup.
--
Martin Kulov
http://www.codeattest.com

MCAD Charter Member
MCSD.NET Early Achiever
MCSD

"Tim Baley" <Ti******@discussions.microsoft.com> wrote in message
news:D7**********************************@microsof t.com...

Hi Martin,

Thanks for your quick response. Anonymous access is not enabled on any of
the intranet IIS web services. Our intent is to use Windows integrated
authentication for SQL Server authentication because the stored procedures use the SQL login user for audit info.

The web services work correctly (including audit trail) when they are hosted on my development machine. They also work correctly when the services are hosted on an IIS server on the same machine as the SQL Server. By
"correctly" I mean that the procedures work as intended and show the domain account of the executing client in the audit trail. In the scenario you
suggest, they would always show the webservice account in the audit trail.
The problem occurs when the webservice is hosted on a different server than the SQL Server. SysAdmin doesn't want anything but SQL Server on the
database machine. I hope this clarifies my problem.

Thanks again for the quick response!

Tim Bailey
Energy Laboratories, Inc.

The problem
"Martin Kulov" wrote:

Hi Tim,

Go to your Web Service virtual directory in IIS management console. Open
Properties and click on tab Directory Security. In the Enable anonymous
access select account that has rights to call the SQL server.

HTH,

--
Martin Kulov
http://www.codeattest.com

MCAD Charter Member
MCSD.NET Early Achiever
MCSD


"Tim Baley" <Ti******@discussions.microsoft.com> wrote in message
news:95**********************************@microsof t.com...

I recently created some intranet web sevices to expose stored procedures

on

our SQL Servers (SQL2k). The stored procedures include an audit trail

with

user/login identification, so the web services are set up for Windows
integrated authentication. The web sevices work well when they are

hosted on

IIS on my Win2k Pro development machine. I have tried accessing the

services

under several user accounts on several machines, including machines in

other

zones connected by VPN. I have connected the webservices to different

SQL servers.

When I move the services to production IIS servers, the database

connection

step throws an exception "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'". The web service on the production service is not flowing the

user

credentials through to SQL server. What do I need to do to get the

service

to work?

Thanks,
Tim Bailey
Energy Laboratories, Inc.

[Tim Baley]

Hi Martin,

The machines are all part of a single domain of roughly 200 computers. The
domain has seven sites connected by VPN tunnels. Each site has at least one
Win2k or Win2k3 server as a domain controller and a SQL Server 2k. Active
Directory is replicated across all the domain controllers and domain accounts
are used to connect to the SQL Servers enterprise wide. Our intent is that
each site will host webservices to access their SQL Server.

At the largest site, the SQL Server, the intranet IIS server and the domain
controller are all on separate machines.

When the web services are running on the personal IIS on my Win2k Pro
development system and SQL Server is on a Win2k Server, the services behave
correctly. When I run the webservices on an IIS server on the same machine
Win2k Server with the SQL Server, the services behave correctly. When the
webservices and the SQL Server are on different machines, credentials aren't
being passed and I get the anonymous logon error. In all cases, the client
software is explicitly passing credentials to the webservice. The IIS servers
and the SQL Server machines are all trusted for delegation. The .NET
processmodel settings are configured for delegation. It's apparently a
"double-hop" issue on the authentication.

It seems like it must be a fairly simple configuration issue, but I am
completely stumped.

Thanks for your help.
Tim Bailey
Energy Laboratories, Inc.
"Martin Kulov" wrote:

Hi Tim,
Sorry I haven't read quite carefully.

Do you have the two machines in one domain? Or they are just in workgroup.
--
Martin Kulov
http://www.codeattest.com

[Martin Kulov]

Hi Tim,

Can you open the SQL server using the Query Analyzer with Windows
Authentication?
I am just shooting in the dark but you never know :)

Martin
"Tim Baley" <Ti******@discussions.microsoft.com> wrote in message
news:69**********************************@microsof t.com...

Hi Martin,

The machines are all part of a single domain of roughly 200 computers. The domain has seven sites connected by VPN tunnels. Each site has at least one Win2k or Win2k3 server as a domain controller and a SQL Server 2k. Active
Directory is replicated across all the domain controllers and domain accounts are used to connect to the SQL Servers enterprise wide. Our intent is that each site will host webservices to access their SQL Server.

At the largest site, the SQL Server, the intranet IIS server and the domain controller are all on separate machines.

When the web services are running on the personal IIS on my Win2k Pro
development system and SQL Server is on a Win2k Server, the services behave correctly. When I run the webservices on an IIS server on the same machine Win2k Server with the SQL Server, the services behave correctly. When the
webservices and the SQL Server are on different machines, credentials aren't being passed and I get the anonymous logon error. In all cases, the client software is explicitly passing credentials to the webservice. The IIS servers and the SQL Server machines are all trusted for delegation. The .NET
processmodel settings are configured for delegation. It's apparently a
"double-hop" issue on the authentication.

It seems like it must be a fairly simple configuration issue, but I am
completely stumped.

Thanks for your help.
Tim Bailey
Energy Laboratories, Inc.
"Martin Kulov" wrote:

Hi Tim,
Sorry I haven't read quite carefully.

Do you have the two machines in one domain? Or they are just in workgroup.

--
Martin Kulov
http://www.codeattest.com

[Tim Baley]

Hi Martin,

Yes, I can open any of the SQL Servers w/ Win integrated authentication in
Query Analyzer. The stored procedures I'm trying to expose are all
production code currently accessed (w/Win authentication) from an Access
frontend.

Thank you for your help and suggestions,
Tim Bailey
Energy Laboratories, Inc.

"Martin Kulov" wrote:

Hi Tim,

Can you open the SQL server using the Query Analyzer with Windows
Authentication?
I am just shooting in the dark but you never know :)

Martin
"Tim Baley" <Ti******@discussions.microsoft.com> wrote in message
news:69**********************************@microsof t.com...

Hi Martin,

The machines are all part of a single domain of roughly 200 computers.

The

domain has seven sites connected by VPN tunnels. Each site has at least

one

Win2k or Win2k3 server as a domain controller and a SQL Server 2k. Active
Directory is replicated across all the domain controllers and domain

accounts

are used to connect to the SQL Servers enterprise wide. Our intent is

that

each site will host webservices to access their SQL Server.

At the largest site, the SQL Server, the intranet IIS server and the

domain

controller are all on separate machines.

When the web services are running on the personal IIS on my Win2k Pro
development system and SQL Server is on a Win2k Server, the services

behave

correctly. When I run the webservices on an IIS server on the same

machine

Win2k Server with the SQL Server, the services behave correctly. When the
webservices and the SQL Server are on different machines, credentials

aren't

being passed and I get the anonymous logon error. In all cases, the

client

software is explicitly passing credentials to the webservice. The IIS

servers

and the SQL Server machines are all trusted for delegation. The .NET
processmodel settings are configured for delegation. It's apparently a
"double-hop" issue on the authentication.

It seems like it must be a fairly simple configuration issue, but I am
completely stumped.

Thanks for your help.
Tim Bailey
Energy Laboratories, Inc.
"Martin Kulov" wrote:

Hi Tim,
Sorry I haven't read quite carefully.

Do you have the two machines in one domain? Or they are just in workgroup.

--
Martin Kulov
http://www.codeattest.com

[Martin Kulov]

"Tim Baley" <Ti******@discussions.microsoft.com> wrote in message
news:06**********************************@microsof t.com...

Hi Martin,

Yes, I can open any of the SQL Servers w/ Win integrated authentication in
Query Analyzer. The stored procedures I'm trying to expose are all
production code currently accessed (w/Win authentication) from an Access
frontend.

Hi Tim,
Strange but I may have missed something. In my first post I suggested to set
an account for Anonymous access. My intent was actually to point out that
the web service is running under account that has no rights to call the SQL
server. When you are using Windows Authentication you just specify the way
to access the IIS. It has nothing to do with the account under web services
run at. Open Task Manager and see it for yourself. On WinXP it should be
aspnet_iis.exe process which is running under ASP_NET account. On Win2003 -
w3wp.exe under NETWORK_SERVICE. Both accounts do not have access to network
resources by default. You can specify the account for the web services in
the machine.config file using <processModel> tag and username and password
attributes. Or if you are using Windows2003 you can create new application
pool and specify account that has enough rights to access network resources.

HTH,
--
Martin Kulov
http://www.codeattest.com

MCAD Charter Member
MCSD.NET Early Achiever
MCSD

[D. Brown]

Tim,

The reason why your authentication is failing is becuase you're trying to
authenticate to a remote source and when using windows auth, you will have
difficulties unless you use delegation w/ kerberos, some type of PKI-related
authentication strategy, pass basic auth credentials(with SSL or IPSEC), or
configure your application to use a particular set of credentials to
authenticated to your remote SQL server.

It works locally because the "'NT AUTHORITY\ANONYMOUS LOGON" is local to the
system. I'm not sure but, I may have read somewhere, where you can actually
synchronize these passwords on both servers. Then add the id as a login to
SQL server.

The root of the problem goes something like this: When you authenticate to
IIS, you pass your primary token which consists of your username and
password. When access to a remote resource is needed and the server is
passing the credentials indirectly, it attempts to pass them using a
secondary token which consists of a username and a 'hash' of the password.
It's this hash which is causing your authentication failure.

Hope this helps.
D. Brown, MCSE 2003

"Tim Baley" wrote:

I recently created some intranet web sevices to expose stored procedures on
our SQL Servers (SQL2k). The stored procedures include an audit trail with
user/login identification, so the web services are set up for Windows
integrated authentication. The web sevices work well when they are hosted on
IIS on my Win2k Pro development machine. I have tried accessing the services
under several user accounts on several machines, including machines in other
zones connected by VPN. I have connected the webservices to different SQL
servers.

When I move the services to production IIS servers, the database connection
step throws an exception "Login failed for user 'NT AUTHORITY\ANONYMOUS
LOGON'". The web service on the production service is not flowing the user
credentials through to SQL server. What do I need to do to get the service
to work?

Thanks,
Tim Bailey
Energy Laboratories, Inc.

[=?Utf-8?B?SGFtZm9uZw==?=]

Hi D. Brown:

I am having a problem that sounds a lot like what Tim is having. I've
googled & tried every suggestion there is on the net & can't seem to resolve
the problem.

Can you or "anyone" help me?

Thanks in advance,
Hamfong

"D. Brown" wrote:

Tim,

The reason why your authentication is failing is becuase you're trying to
authenticate to a remote source and when using windows auth, you will have
difficulties unless you use delegation w/ kerberos, some type of PKI-related
authentication strategy, pass basic auth credentials(with SSL or IPSEC), or
configure your application to use a particular set of credentials to
authenticated to your remote SQL server.

It works locally because the "'NT AUTHORITY\ANONYMOUS LOGON" is local to the
system. I'm not sure but, I may have read somewhere, where you can actually
synchronize these passwords on both servers. Then add the id as a login to
SQL server.

The root of the problem goes something like this: When you authenticate to
IIS, you pass your primary token which consists of your username and
password. When access to a remote resource is needed and the server is
passing the credentials indirectly, it attempts to pass them using a
secondary token which consists of a username and a 'hash' of the password.
It's this hash which is causing your authentication failure.

Hope this helps.
D. Brown, MCSE 2003

"Tim Baley" wrote:

I recently created some intranet web sevices to expose stored procedures on
our SQL Servers (SQL2k). The stored procedures include an audit trail with
user/login identification, so the web services are set up for Windows
integrated authentication. The web sevices work well when they are hosted on
IIS on my Win2k Pro development machine. I have tried accessing the services
under several user accounts on several machines, including machines in other
zones connected by VPN. I have connected the webservices to different SQL
servers.

When I move the services to production IIS servers, the database connection
step throws an exception "Login failed for user 'NT AUTHORITY\ANONYMOUS
LOGON'". The web service on the production service is not flowing the user
credentials through to SQL server. What do I need to do to get the service
to work?

Thanks,
Tim Bailey
Energy Laboratories, Inc.