September 24, 2008 at 8:41 am
ericzbeard (9/24/2008)
Ok, I have come to the conclusion that it is not possible to link two Sql Server 2005 servers using windows authentication. I had to resort to creating a Sql Login on the linked server.I have spent days on this, trying everything found by this google search:
and many many more searches.
Oh well, at least it works now. I just feel like I'm not doing it "right".
Windows servers can only pass a login a certain number of chain links. You either need to set up Kerberos to allow the login to chain, or you need to set up NT AUTHORITY/ANONYMOUS LOGIN as a login. Either one will work.
- 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
September 24, 2008 at 8:43 am
Thanks, that document explains a lot. I'm really suprised MSFT didn't make this easier, what with all the preaching about not using Sql logins and always going with windows auth. Hello Sql Server 2008 team?
It looks like we have to summon up super-hero sysadmin strength to make this work. I'm actually afraid of screwing something up and making things less secure then with a Sql Login. I have the sa account passworded and disabled, and the login I created for the link has limited access, so I think I'll just leave it.
September 25, 2008 at 7:15 am
Yeah, linking servers using domain security should be easier. It's a pain. Using mixed authentication is the easiest solution to it.
- 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
September 25, 2008 at 7:26 am
I've had plenty of head-banging moments with linked servers so you have my sympathy. But there's one thing that's worth trying and that's to log on to the server you want to link from using Remote Desktop, launch Mgmt Studio from there and then try and set up the linked server. Just something simple like connect to SQL Server, and using the login's current security context.
This works for me and I have jobs using the linked servers which run fine. But if I try and access the linked server through Mgmt Studio from my client I get a login failed for NT AUTHORITY/ANONYMOUS LOGIN. Resolving this particular issue is unknown to me at the moment, but it does seem to smack of a kerberos issue (as I think someone else mentioned in an earlier thread).
Hope this helps
April 2, 2009 at 1:56 am
i am having the problem.
when i specify the second server name it says already exists and says connection failed.but i cann't find 2nd server under linked server.
April 2, 2009 at 2:09 am
This NT Authority\Anonymous LOGON error can be related to kerberos setting. Please check spn (setspn /L) to verify (blogs.msdn.com/sql_protocols/archive/2005/10/12/479871.aspx). Please consult your SA.
The best workaround is not using nt login, set your link server security as "be made with this security context" and then give login/password valid for destination server (not the current server).
August 14, 2009 at 3:54 am
noce blog
February 16, 2010 at 11:18 am
I may be late in the game on this, but in my experience (and I just tested this to be sure) you can pretty much ignore the login failed message when using the GUI to establish the link.
I RDP'd into two servers (A & B) and used SSMS on A to establish a link between the two. Using the GUI, I specified the server name (B), SQL Server as the type, and selected "Be made using this scurity context:" using my domain admin account for testing.
On server A, I immediately got the "Login failed" error but I chose to keep the linked server anyway (clicked on Yes).
On server B, I checked the windows security log and to my surprise I found a "Success Audit" for my domain admin account at the exact time I created the link.
Sooo... I went back to server A, wrote a query to select data from server B and viola! It works like a charm.
I hope this helps those of you seeking it. Microsoft 0, Real people 1.
May 5, 2011 at 6:25 am
I just encountered the same issue being discussed here. I was finally able to solve it. I did the following:
- RDP'd to the Domain controller for the SQL Server (if you're not sure, RDP to the SQL Server and type "SET" at the command prompt)
- Once on the Domain Controller, check to make sure you have some SPNs created for SQL Server (from the command prompt, type "setspn -l <insert service account for SQL Server>" e.g C:\setspn -l svc001
- You should see something like MSSQLSvc/sqlservername and MSSQLSvc/sqlservername:1433
- In my case, the problem was a duplicate SPN. This can happen if you installed SQL Server under a different account or changed the SQL Server account
- To check for duplicates, run the following from the command prompt:
C:\>ldifde -s <domain controller name> -d "DC=<bob>,DC=<bobsdomain>,DC=<com>" -r (servicePrincipalName=MS
SQLSvc/<instance name>*) -v
Be sure to run this from the domain controller. In this example, do include any of “< >” characters
- Once I removed the duplicate SPN, new connections into SQL Server authenticated using Kerberos
- You can check authentication method for logins using the following query:
SELECT
s.session_id
, c.connect_time
, s.login_time
, s.login_name
, c.protocol_type
, c.auth_scheme
, s.HOST_NAME
, s.program_name
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_connections c
ON s.session_id = c.session_id
SQL from: http://www.sqlservercentral.com/articles/Security/65169/
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply