Solved

IIS and SQL 'NT AUTHORITY\ANONYMOUS LOGON'

Posted on 2003-11-17
25
39,828 Views
Last Modified: 2011-09-20
I have am trying to write an ASP page that pulls data from a SQL Server database, where the SQL server is on a different machine than the IIS server.  
I have everything working, except when a non-administrator goes to my page, they get an error message stating:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

I am using intergrated authentication only.  I have read about some other people with simmilar problems, and one stated that the problem goes away if you run the IIS and SQL server on the same box.  I would like to avoid having to do this.. And it would seem like I could, as, like I said, the page opens and displays just fine for me, but not for the users.
0
Comment
Question by:jagoodie
  • 8
  • 5
  • 5
  • +4
25 Comments
 
LVL 7

Expert Comment

by:wsteegmans
ID: 9765787
I think your connectionstring to the SQL-Server is something like this (or are you using a DSN)?

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DBName;Data Source=SQLServerName

Try to create a new user in your SQL-Server Database and give it permission to your database (or give it permission to all databases) ...

So, instead of connection to the SQL-Server using Integrated Security, you use this SQL-Server user. You could also use the 'sa' of SQL-Server, but that's no such a nice solution.
So, your new connectionstring will look like this:

Provider=SQLOLEDB.1;Password=UserPassword;Persist Security Info=False;User ID=UserName;Initial Catalog=DBName;Data Source=SQLServerName
0
 
LVL 34

Expert Comment

by:arbert
ID: 9767392
You can NOT use integrated (Windows) Authentication when the SQL Server and IIS machine are on different boxes (Unless you grant the IIS IUSR account access to the database).  You must enable Mixed Mode security and use a user/password.

Brett
0
 
LVL 18

Expert Comment

by:BigSchmuh
ID: 9767503
You can use integrated Windows authentication even if SqlServer and IIS are on different servers but need to:
-Have your 2 servers trusting the same authority (Same domain)
-Create a domain account
-Grant this account enough priv to use your db
-Make this account the anonymous one replacing the IIS default one (IUSR_servername)
==> Be aware that this may potentially leads to security hole so I will only use this on IIS servers which can not be reached from the Internet

Hope this helps.
0
 
LVL 34

Expert Comment

by:arbert
ID: 9767683
of course, if the above is a web application, the SQL Server machine would be behind a firewall and the IIS machine wouldn't be in the domain with the sql machine....
0
 
LVL 2

Author Comment

by:jagoodie
ID: 9771325
wsteegmans method of not using Integrated Security=SSPI; works, but, I would rather use it...
regarding people saying you can't use Integrated Security=SSPI; with SQL and IIS being on different servers, I STRONGLY disagree, because, like I said, I was able to use it for my logon, but just not another "user" logon, all without having the iusr account on the SQL server.  Any thoughts?
0
 
LVL 2

Author Comment

by:jagoodie
ID: 9771361
missed a couple details: these are both DC's  for the same domain, behind a firewall
0
 
LVL 18

Expert Comment

by:BigSchmuh
ID: 9772115
May be your SqlServer service is not started using a domain account. If SqlServer starts from "LocalSystemAccount" it should not be able to authenticate Domain users.

Hope this helps.
0
 
LVL 34

Expert Comment

by:arbert
ID: 9772550
Both are DCs for the same domain????  You mean one is a PDC and one is a BDC?
0
 
LVL 7

Expert Comment

by:wsteegmans
ID: 9772691
Was reading your question and all the answers again ...

If I'm not wrong ... when using intergrated authentication, your IUSR_servername is not used, because it is for anonymous authentication ... So, you open an ASP-file on the server using the Login Account of the user requested the ASP-Page.

In this ASP-Page, you have a DB-Connection to your SQL-Server located on a different machine. The ConnectionString for this SQL-Server DB also uses integrated authentication ...

So, whant happens (if I'm not wrong) ... The SQL-Server is accessed by the user who did the ASP-Request ... (because of integrated authentication in the IIS). Maybe, all domainusers have access to the machine where the SQL-Server is located on, but they must also have access to the requested Database!

So, what to do ... is to add your domainusers to the users of the SQL Database you're requesting from your ASP-page, and give all these users at least Read Access (and if data is saved, also Write Access) ...
0
 
LVL 2

Author Comment

by:jagoodie
ID: 9772923
all correct, that is how i think it should work also. HOWEVER, all users already have access to the db (they use it everyday) and they still cant get it through asp...

baffling.
0
 
LVL 34

Expert Comment

by:arbert
ID: 9772955
you are wrong--IIS will NOT pass the userid on to SQL Server UNLESS IIS and SQL Server are hosted on the same machine....
0
 
LVL 34

Expert Comment

by:arbert
ID: 9772980
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 2

Author Comment

by:jagoodie
ID: 9788525
still doesn't explain why Integrated Security=SSPI works for me, but no one else...
hmm...
0
 
LVL 7

Expert Comment

by:wsteegmans
ID: 9788543
Can you check your IIS-Settings and give them to us ...
0
 
LVL 2

Author Comment

by:jagoodie
ID: 9788598
they are both windows 2000 sp4 boxes
the iis box has basic and intergrated security, no anon.  fp2002 server ext.  also has exchange 2000 sp3 on it.

any other settings you need?
0
 
LVL 12

Expert Comment

by:vascov
ID: 9807521
jagoodie,

Your configuration is working because you're forcing a Basic Auth in IIS. This actually results in a local logon of the remote user, that enables you to hop to another machine using the impersonated credentials. (you also have impersonation = true)
If you change it Integrated Security in IIS, you'll see that unless your machine is trusted for delegation, the logon wont work.

BTW, if using Integrated Auth in IIS and logged on the same machine that hosts IIS, it will also work (because it will reuse your interactive logon session), BUT if you make a request from another machine it will not work.

HTH
1
 
LVL 12

Expert Comment

by:vascov
ID: 10042561
jagoodie, have you solved this one ? did my comment help ?
0
 
LVL 2

Author Comment

by:jagoodie
ID: 10043112
hi vascov,
no,"as, like I said, the page opens and displays just fine for me, but not for the users" from a different box.  so i am basically trying to figure out what setting is different for me, than some of the other uesrs.
perplexing.
0
 
LVL 12

Accepted Solution

by:
vascov earned 500 total points
ID: 10044139
Hi jagoodie.

As i said in my last post, this can happen if you are logged ON the machine. If you try with your user from a different machine you probably will fail just like others.

If you enable ONLY Basic Auth, what are the results ? (it should work for everyone)

hth

Vasco
0
 
LVL 2

Author Comment

by:jagoodie
ID: 10044505
Ahh!  Now I get it!
Yep.. That did it.
Thanks!
0
 
LVL 12

Expert Comment

by:vascov
ID: 10044526
Kool!

Glad that i could help :)

Vasco
0
 
LVL 12

Expert Comment

by:vascov
ID: 10048098
jagoodie,

just a final caution.

Use https when using Basic Authentication. Basic authentication sends passwords clear text across the wire, and therefore i would recommend https to protect it.


Vasco
0
 

Expert Comment

by:joaopinto2
ID: 10929950
My asp application gives me the following error:

[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON

How can I solve this problem?

Joao
0
 
LVL 2

Author Comment

by:jagoodie
ID: 10930095
set up a "shared" acount and put it in your connect string instead of impersonation
0
 

Expert Comment

by:seisupport
ID: 11329457
Jagoodie,

I think I know what you want to achieve - use trusted_connection for the SQL string, and access a SQL machine on another box using Windows Authentication in ASP.NET.  You can access SQL from an IIS/ASP.NET instance on another box despite what others think.  There's one small caveat, you need to be all Win 2K or all Win 2003 boxes with SQL SP3 or later (no mixed 2000/2003 boxes - I'll explain why below) Here's how to solve your problem:
Step 1)
In your Web.Config file, enable Impersonation and Windows Auth:
<authentication mode="Windows" />
<identity impersonate="true" userName="" password="" />
Step 2)
In IIS, force Windows Authentication ONLY for the Instance
Step 3)
Make sure the value of your SQL connection string is set properly:
value="Server=***;trusted_connection=true;Database=***"
Step 4)
Identify a test user, make sure that we are running AD, make the user account is "trusted for delegation"
Step 5)
Make sure the IIS box you’re running is "trusted for delegation".  Refer to this link for help on the matter:
http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerbdel.mspx
Step 6)
If you ASP.NET/IIS instance is NOT the machine name, but is a custom host name for the IIS, you need to add an SPN for that HTTP service in the AD.  Refer to the previous article for doing that. This is critical for it to work.  Without a proper SPN registered for the host machine / IIS Instance, you're dead in the water.
Step 7)
Make sure your user has logon rights to SQL and can actually perform the operations your doing (SELECT, etc) in Enterprise Manager.  Add the user or groups of people appropriately.

Give it a whirl.  You should stop seeing that error since the user will now be having a delegation of the Kerberos ticket to the SQL machine, via the impersonation.  This ONLY works on heterogeneous OS models since they radically changed 2003 to have granular delegation.  I have not found a way to get my SPN registered, albeit using an older version of setspn.exe (which may be my problem on one system).

Hope this helps!
Mark
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now