Login failed for user NT AUTHORITY\ANONYMOUS LOGON

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Author

Topic

neto_pf
Starting Member

3 Posts

Posted - 2009-09-15 : 08:13:17

Microsoft OLE DB Provider for SQL Server error '80040e4d'
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

My infrastructure is a sql 2008 server (machine name: SRVSQL) and a Windows 2008 server with IIS 7 (machine name: SRVIIS), both in a domain "dominio.lan".

What I am trying to do is using the machine with IIS and try to connect directly to SQL through windows authentication, and I get the error above.

I already use the "setspn srvsql", which is:

MSSQLSvc/srvsql.dominio.lan:1433
MSSQLSvc/srvsql.dominio.lan

I'm trying to login with my user and I can't do it, if I install IIS 7 on the server srvsql it works, the user 'util1' has permissions at the database, What am I doing wrong? Do I need to give permissions to "util1" using setspn (which duplicate keys when I do setspn -X)?

Thanks for your help,

António Neto

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-09-15 : 08:47:39

How are you connecting to the SQL Server?
Did you create a SPN for the http service on SRVIIS? And trust for delegation.
Does your web application allow impersonation? Check web.config for <identity impersonate="true" />
What is the security account of the application pool?

quote:
if I install IIS 7 on the server srvsql it works

Yes, this issue only occurs in a double-hop situation.

neto_pf
Starting Member

3 Posts

Posted - 2009-09-15 : 10:22:17

The connection string is:
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=bang;Data Source=srvsql

The SPN for srviis is:
TERMSRV/SRVIIS
TERMSRV/SRVIIS.dominio.lan
SMTPSVC/SRVIIS
SMTPSVC/SRVIIS.dominio.lan
HOST/SRVIIS
HOST/SRVIIS.dominio.lan

In trust for delegation in the active directory is "Trust this computer for delegation to any service (Kerberos only)"

I add the identity impersontation to web.config and still doesn't work.

The security account of the application pool is ApplicationPoolIdentity

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-09-15 : 10:59:03

You probably need a SPN for the HTTP service: http://support.microsoft.com/kb/929650/

This Checklist for Double Hop issues {IIS and SQL Server} may also help: http://blogs.technet.com/taraj/archive/2009/01/29/checklist-for-double-hop-issues-iis-and-sql-server.aspx
How to troubleshoot Kerberos-related issues in IIS: http://support.microsoft.com/kb/326985

For the SQL Server service I'd suggest using ADSIEdit and giving the SQL Service account rights to manage it's own SPN.

Most DBAs don't have Domain Admin rights, so don't usually set SPNs. I suggest you post this question on a Windows Admin site.

neto_pf
Starting Member

3 Posts

Posted - 2009-09-16 : 04:20:49

I am already setting up what you give me, but because the servers can't be restarted when we want I've not finshed yet.

But I have some doubts about setspn (if you could help me), when I set the HTTP and MSSQLSvc I must enter a user, but what I would like to set was a group, but it isn't possible. So I create a user for that and gave permissions of sysadmin in SQL Server instance, but in my application I have many users who will need to access to the database, so I gave permissions to a group of the active directory in the database, and putted the users in members of tha group, am I doing right, or should specify the HTTP and MSSQLSvc to the correspondent machine name of the service (HTTP - srviis and MSSQLSvc - srvsql)?

When you mention SQL Service account, which account is that?
If I create more than one entrance to a service (with different users) it gives me duplicate keys (setspn -X), I can't set MSSQLSvc and HTTP to a machine and to users right?

Thanks for your help

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-09-16 : 04:36:06

The SPN must be setup under the service account. Not just any user account, as you have done.
For the SQL Server service account, from Start --> All Programs --> SQL Server 2005 --> SQL Server Configuration Manager --> SQL Server Services
Look in the "Log On As" column of the Service named "SQL Server (MSSQLSERVER)" or similiar.

This should be a domain account and this is the only user account to use in the SetSPN command for each SQL Server. You should remove any other SPN for the same SQL Server.

The HTTP SPN should be set to the security account of the Application Pool account, as far as I know.

It is usually time consuming to setup the first time - I suggest you document as you go along for next time. And try to create the SPNs as part of the post-installation procedure (when you can restart the server at will).

Hope this helps.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources