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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

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.
Put Your Flow Data to Work

SolarWinds® Flow Tool Bundle combines three easy-to-download, easy-to-use flow analysis tools that can help you quickly distribute, test, and configure your flow traffic.

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....
jagoodieAuthor Commented:
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?
jagoodieAuthor Commented:
missed a couple details: these are both DC's  for the same domain, behind a firewall
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.
Both are DCs for the same domain????  You mean one is a PDC and one is a BDC?
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) ...
jagoodieAuthor Commented:
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...

you are wrong--IIS will NOT pass the userid on to SQL Server UNLESS IIS and SQL Server are hosted on the same machine....
jagoodieAuthor Commented:
still doesn't explain why Integrated Security=SSPI works for me, but no one else...
Can you check your IIS-Settings and give them to us ...
jagoodieAuthor Commented:
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?

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.

jagoodie, have you solved this one ? did my comment help ?
jagoodieAuthor Commented:
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.
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)



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jagoodieAuthor Commented:
Ahh!  Now I get it!
Yep.. That did it.

Glad that i could help :)


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.

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?

jagoodieAuthor Commented:
set up a "shared" acount and put it in your connect string instead of impersonation

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:
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:
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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.