Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

[TheBugSlayer]

Good day.

There is a trigger on a table in one database. The trigger inserts a row in a table in a separate database. However, the insert fails due to the trigger not being able to insert into the second database. As an admin I am able to execute the insert successfully. Users, members of a certain group, which has db_datareader, db_datawriter and db_owner privileges on both database have no luck executing the statement. The error they receive is:

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.


How do I need to configure the group in both databases so that they are able to insert in the other database through the trigger on a table in the first database?

Thank you.


MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA), Training HOTT cert SQL Server 2012 Business Intelligence (SSIS, SSAS).

 

[gmmastros]

Are both databases on the same SQL Instance?



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TheBugSlayer]

Yes, George, they are both on the same instance.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA), Training HOTT cert SQL Server 2012 Business Intelligence (SSIS, SSAS).

 

[ElEye]

I'm sure you already looked at this, but just to ask: under "Security" -> "Logins" have you set "NT AUTHORITY\ANONYMOUS LOGON" for the proper access rights to both DBs under "User Mapping?"

Dave
[

http://flashspot.com

]

 

[TheBugSlayer]

Hi El. No, I have never have to set any such login before. Under Security, I only have NT AUTHORITY\SYSTEM but it's not mapped to any database...Should I map NT AUTHORITY\ANONYMOUS LOGON and try?

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA), Training HOTT cert SQL Server 2012 Business Intelligence (SSIS, SSAS).

 

[ElEye]

Yes, TBS. I think you have to map to that. The most secure method is to have a unique login for each website, but if that's not practical then the "nt authority/anonymous logoin" is standard as that is the default IIS user and thus that's the one to map. So, I suggest trying that and see the result. If it does not help then go back and turn off that access to be safe.

Dave
[

http://flashspot.com

]

 

[TheBugSlayer]

I will try, just in case. Personally I don't like it as the user belongs to a group whose permissions are fine-grained. Feels like I am losing control. No web site thought. It's just users inserting through SSMS or a Windows desktop app. Thanks for your help.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA), Training HOTT cert SQL Server 2012 Business Intelligence (SSIS, SSAS).