none
Linked Server Set-Up and Access RRS feed

  • Question

  • What am I doing wrong here?

    I am correctly executing EXEC master.dbo.sp_addlinkedserver and I indeed see my Linked Server defined.

    However when I drill down on Linked Servers\(Linked Server Name)\Catalogs, I am NOT seeing the Databases.

    We set-up the Linked Server to use SQL Server Authentication and a defined ServiceAccount and made sure that the ServiceAccount was granted access on the Linked Server and the specific Databases it would need to access and even verified that by signing onto the Linked Server directly using the ServiceAccount.

    Now the Versions are different.

    The Source Server, the Server I am trying to Link FROM is SQL Server Version...

    Microsoft SQL Server 2014 (SP3-GDR) (KB4532095) - 12.0.6118.4 (X64)   Dec 12 2019 21:46:15   Copyright (c) Microsoft Corporation  Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor) 

    The Linked Server we are trying to access TO set-up and access is SQL Server Version...

    Microsoft SQL Server 2016 (SP1-GDR) (KB4505219) - 13.0.4259.0 (X64)   Jun 15 2019 19:20:12   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows Server 2012 R2 Datacenter 6.3 <X64> (Build 9600: ) (Hypervisor) 

    Could that have anything to do with it??

    Thanks for your review and am hopeful for a reply.

    Friday, May 29, 2020 11:23 PM

Answers

  • Hi ITBobbyP,

    Did your issue is like this?


    If you use T-SQL create link server, you need to use sp_addlinkedsrvlogin to creates or updates a mapping between a login on the local instance of SQL Server and a security account on a remote server. Please refer to MS document Create a linked server by using Transact-SQL.

    If you use SSMS UI create link server, the Impersonate check box is checked to passes the local login credentials to the linked server. For SQL Server Authentication, the same login with the exact credentials must exist on the linked server, otherwise when connected to the server with the SQL Server Authentication, the list of the databases under the Catalogs folder may look like as below screenshot. Please refer to How to create and configure a linked server in SQL Server Management Studio.

    If i misunderstood your issue, please let me know. 
    Best regards,
    Cathy 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, June 1, 2020 3:23 AM

All replies

  • Why would you bother about seeing the catalogs in Object Explorer? I assume that you had a deeper meaning with setting up the linked server?

    What happens if you try to use the linked server for something you actually intended?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, May 30, 2020 9:46 AM
  • Hi ITBobbyP,

    Did your issue is like this?


    If you use T-SQL create link server, you need to use sp_addlinkedsrvlogin to creates or updates a mapping between a login on the local instance of SQL Server and a security account on a remote server. Please refer to MS document Create a linked server by using Transact-SQL.

    If you use SSMS UI create link server, the Impersonate check box is checked to passes the local login credentials to the linked server. For SQL Server Authentication, the same login with the exact credentials must exist on the linked server, otherwise when connected to the server with the SQL Server Authentication, the list of the databases under the Catalogs folder may look like as below screenshot. Please refer to How to create and configure a linked server in SQL Server Management Studio.

    If i misunderstood your issue, please let me know. 
    Best regards,
    Cathy 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, June 1, 2020 3:23 AM
  • Hi ITBobbyP,

    Is there any progress?

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, June 2, 2020 1:42 AM