October 20, 2010 at 12:28 pm
I have a SQL2008 box. Here is the "SELECT @@VERSION":
Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64) Sep 16 2010 19:43:16 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (VM)
I have two databases on it, say, A and B.
I can modify the stored procedures within database A.
When I tried to modified the stored procedures within database B, I got the following error:
[p]Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.[/p]
Then, I created a "test" user as the db_owner of database B, and tried to modify the stored procedure. I got "login failure for user TEST" error.
It looks to me that this SQL 2008 (SP2) can only support one database. When I tried to connect to the 2nd database, it uses local account to connect to the database (here is NT AUTHORITY\ANONYMOUS).
BTW, I am the SA on this box. This is the first time I got this kind of weird error.
Anyone got the similar error? And how to fix it?
Thanks.
October 20, 2010 at 1:14 pm
It means you are trying to pass Windows authentication data through more than 1 layer.
Does the proc connect to another server? That's the most likely scenario. If you have a linked server and are using "current security context" for the connection. Change that to a SQL login for the connection, if that's the problem.
If not, you'll need to do some research online for the solution. Search the bit about the anonymous login, and search for "kerberos", and you'll find out how to solve it, but it's a bit of a pain. It's not trivially simple, but it can be solved.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 20, 2010 at 2:02 pm
Thanks, GSquared .
I got it figured out. Yes, it is trying to connect another server. After I modified the linked server security, the error is gone.
Cheers.
October 21, 2010 at 1:16 pm
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply