October 23, 2003 at 9:59 am
I have two SQL server with Windows authentication, I tried to set up linked server from one to another, it did not work, I got "Error 18456: Login failed for user 'NT AUTHORITY/ANONYMOUS LOGON'.
October 23, 2003 at 1:01 pm
Try to use linked server itself, not your development machine.
October 23, 2003 at 1:33 pm
It seems the connection coming from IIS server. You may use SQL Server authentication or grant "NT AUTHORITY/ANONYMOUS LOGON" by running EXEC sp_grantlogin 'NT AUTHORITY/ANONYMOUS LOGON'.
October 23, 2003 at 3:18 pm
I don't understand either of the above "answers," but I think you need to read the updated BOL entry on Security Account Delegation.
--Jonathan
--Jonathan
October 23, 2003 at 8:27 pm
The problem is that SQL Server doesn't forward the login information through remote servers well. What I have always done is to set up a sql user on my linked servers with minimal rights. I then use the Security tabl on the Linked Server to map the logins. I have never been able to get trusted connections to work across a linked server any other way. I wish it would though!
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
October 24, 2003 at 4:11 am
My understanding of the theory is:
1) You need to read what BOL says about this
2) You need to be running W2K or above & SQL2K
3) You need to talk to your security guys and NT admin guys about allowing 4) 5) & 6)
4) You need to register your servers with AD
5) You need to grant the Kerberos right for 'Allow account delegation' to all 'sending' servers.
6) You need to grant the Kerberos right 'Accept account delegation' to all 'receiving' servers
If you have one master server that requests data from other servers, then the master is the sender and the others are receivers. If all servers request data from each other then they are all senders and receivers.
Hope it works!
All information provided is a personal opinion that may not match reality.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
October 27, 2003 at 4:36 am
EdVassie,
How do you set the rights detailed in 5) and 6)?
I know about the 'Account is trusted for delegation' setting, but neither I nor our infrastructure team have heard of these other rights.
We have the same problem, and have tried everything mentioned in BOL and various KB articles, all to no avail.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply