November 3, 2011 at 3:33 pm
Hey. I have, let's say, three SQL Server 2008 R2 systems. A, B and C. I tried adding B as a linked server to A. When I do this I only see two out of maybe 30 databases. So, I go server C and I add the other two systems as linked servers. I see all databases from here on A and B.
It seems I have tried just about every permutation you could to get Server A to see all the databases on C. Permissions seem to be a moot point since in all cases I am trying to use pass-through authentication and all ID's are Windows ID's and also belong to a group with SysAdmin privileges.
It is not possible for me to use a SQL login.
Two questions: Does anyone know of a reason why the SS Utility might create issues connecting between these servers? Does anyone have the proper way to add a linked server, using pass-through authentication.
Oh, yeah, and how come Microsoft is working so hard on supposed features when ones that would be really helpful fail to work as seamlessly as their installs?
Links, comments, questions, suggestions welcome.
November 3, 2011 at 7:59 pm
Hi
Please try this:
1)SSMS -> Server objects -> Linked Servers -> 'New Linked Server'
2)In the pop-up window “New Linked Server”, select the General tab
3)Specify an alias name in "Linked server" field
4)Select “SQL Server Native Client 10.0” as provider
5)Add sql_server in "Product Name" field
6)In "Data Source" – specify FQDN of the host to be used as linked server
7)In Security tab – specify proper security options (e.g. security context). First try using -- “Be made using the login’s current security context”
if this works, then it will be easy to figure out the problem
8)In Server Options tab – select ‘Collation Compatible’,"Data Access", RPC, "RPC Out" and "Use Remote Collaboration" to be true
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply