Webservice SQL logon fails on production IIS server

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tim Baley - 11 Feb 2005 16:31 GMT

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.

Reply to this Message

Martin Kulov - 11 Feb 2005 17:23 GMT

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,

Signature

Martin Kulov
http://www.codeattest.com

MCAD Charter Member
MCSD.NET Early Achiever
MCSD

> I recently created some intranet web sevices to expose stored procedures on
> our SQL Servers (SQL2k). The stored procedures include an audit trail with
[quoted text clipped - 14 lines]
> Tim Bailey
> Energy Laboratories, Inc.

Reply to this Message

Tim Baley - 11 Feb 2005 18:03 GMT

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

> Hi Tim,
>
[quoted text clipped - 30 lines]
> > Tim Bailey
> > Energy Laboratories, Inc.

Reply to this Message

Martin Kulov - 11 Feb 2005 19:59 GMT

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

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

Signature

Martin Kulov
http://www.codeattest.com

MCAD Charter Member
MCSD.NET Early Achiever
MCSD

> Hi Martin,
>
[quoted text clipped - 55 lines]
> > > Tim Bailey
> > > Energy Laboratories, Inc.

Reply to this Message

Tim Baley - 11 Feb 2005 22:33 GMT

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.

> Hi Tim,
> Sorry I haven't read quite carefully.
>
> Do you have the two machines in one domain? Or they are just in workgroup.

Reply to this Message

Martin Kulov - 11 Feb 2005 23:03 GMT

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

> Hi Martin,
>
[quoted text clipped - 30 lines]
> >
> > Do you have the two machines in one domain? Or they are just in workgroup.

Reply to this Message

Tim Baley - 14 Feb 2005 17:51 GMT

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.

> Hi Tim,
>
[quoted text clipped - 49 lines]
> > > Do you have the two machines in one domain? Or they are just in
> workgroup.

Reply to this Message

Martin Kulov - 15 Feb 2005 21:17 GMT

> 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,

Signature

Martin Kulov
http://www.codeattest.com

MCAD Charter Member
MCSD.NET Early Achiever
MCSD

Reply to this Message

D. Brown - 23 Feb 2005 13:41 GMT

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

Reply to this Message

Hamfong - 26 Nov 2007 19:50 GMT

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

> Tim,
>
[quoted text clipped - 38 lines]
> > Tim Bailey
> > Energy Laboratories, Inc.

Reply to this Message

Rate this thread:

Webservice SQL logon fails on production IIS server

Free Magazines