March 19, 2010 at 1:35 pm
Hello. I have been looking for an answer for 2 days. We have been using a linked server for over 2 years. Not aware of any changes to system. After this happened we checked versioning, and the only difference is the 8.00.2039 / 8.00.2055. Both servers are 2000 with SP4. I tried all things that I read about, i.e. check TCP/IP, check MSDTC service, check uid/pwd on both machines, try using ole db instead of sql server, etc... nothing works. I deleted the linked server (many times) and tried re-creating using both enterprise manager (fill in the blanks) and query analyzer (with script). Always creates the entry, but when you click on Tables you freeze up (with an eventual generic error message), and when you try query via query analyzer you freeze up (with an eventual generic error message). Does ANYBODY know what is causing this? Would be forever grateful for any other ideas.
Thanks !
Jennece
March 19, 2010 at 2:09 pm
Is it possible that any permissions have been changed? If so, you'd be able to log in, and that would make the linked server not give a security error. If, for example, you have permission to log in, but don't have any permission to view data.
It's a bit of stretch, but permissions is the first thing that comes to mind that I'd check next in your position.
- 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
March 20, 2010 at 7:03 am
Even though it is generic, can you post the error message?
Also, try and query something that should be available to everyone through that linked server connection. For instance:
SELECT * FROM LinkedServer.master.dbo.sysobjects
See if that returns the same error. If it does not, then the linked server connection is good and it's definitely a permissions issue accessing the objects in question.
Also check the basic things like being able to ping each server from another, bringing up Enterprise Manager on the physical server where the linked server is being created and connecting through Enterprise Manager (not using a linked server, but adding the SQL Server to EM) to make sure that the network path is good between both servers and that you can connect to the second server from the first without the use of a linked server connection.
K. Brian Kelley
@kbriankelley
March 22, 2010 at 7:19 am
First, thank you for the suggestions... We tried creating the Linked table with the admin uid/pwd. Logged in to Windows and Sequel. No go. The linked server does add correctly but when you try to view its tables, you get "Error 17: SQL Server does not exist or access denied". From Query Analyzer, when doing simple select * from lnkSrv.master.dbo.table results in "SQL Server does not exist or access denied". Can NOT add the server using EM on the box, but can from the desktop, hmm...
Network security changes/updates??
March 22, 2010 at 7:20 am
I'd check DNS next. "Server does not exist" may mean it can't resolve the name.
Can you ping the servers from each other?
- 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
March 22, 2010 at 7:33 am
They can ping each other fine
March 22, 2010 at 8:37 am
Then it's definitely security. If they can find each other, then it's not "does not exist" and is "or access denied".
Are you using SQL security at both ends, or Windows (Active Directory), or a mixture?
- 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
March 22, 2010 at 8:51 am
we are using mixed mode. we can add linked server from one server (the direction we DONT need) but cant add linked server from the other server (the server we DO need). so what settings could have been changed that caused this to stop working... that is the question... also having some seemingly random trouble with printing reports (crystal). related??...
March 22, 2010 at 9:18 am
Honestly, I'd have to actually dig into the servers to know exactly what to tell you to do at this point.
Without that, I'm kind of shooting in the dark a bit.
What settings are you using for the security? Options like loggin mapping, what kind of connection to make if it's not a mapped login, kerberos settings for passing AD tokens from server to server. Any or none of those might matter.
Firewall changes might make a difference, as might a variety of security features between the two services.
Have there been any Windows updates on either server recently? Is either (both) on a VM, and has there been any change to either the VM or the underlying physical server?
How about hardware changes anywhere between them?
- 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
March 22, 2010 at 9:39 am
wow... you lost me on the AD tokens... i am a senior programmer, not a dba 🙂
-updates were run over the weekend to make sure both machines are on same versions.
-there were two windows updates put on this week, but both correspond to times AFTER the problem
started.
I totally know its a shot in the dark for someone not able to see... but we were desparate and hoping someone else had this issue come up, already went throught the *&%^$ we're going through, and found an answer...
We have a bunch of code running on this, and HAVE to find a way to fix it, so I will let you know what we find out.
thanks so much for all of your help !!!
March 22, 2010 at 9:47 am
AD tokens aren't a DBA thing. They're network security. WAY outside what most DBAs would be fiddling with.
One idea that I'm not sure you've tried yet, though you may have. From the server you're trying to create the link on, can you connect to the other server via Enterprise Manager, using the credentials you're trying to create the linked server with?
Have you tried that? (I haven't used Enterprise Manager in years, but I think I remember it having an option as to which server to connect to. If not, I know Query Analyzer had that option.)
- 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 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply