SQL Server Linked Server security ? RRS feed

  • Question

  • Hello in creating a new Linked Server on SQLServer_A going into a different SQLServer_B using the:

    Be made using this security context:

    When a regular different Login user connects into SQLServer_A the user is able to see the data via the Linked Server.

    Is it possible to prevent this and only let the Linked Server be used by particular user(s)?

    Thanks in advance.

    Thursday, October 25, 2018 5:28 PM

All replies

  • A login with the exact UID and PWD must exist on the remote server, restrict the access by proving necessary permission for the specific user on DB

    Finally, Link this server with the UID and PWD from the Machine where you wanted the linked server.
    Thursday, October 25, 2018 5:36 PM
  • A linked server is not a securable. That is, you can grant or deny access on it as such.

    The only device you have is the login mapping. You can use sp_addlinkedsrvlogin to this end. This procedure takes five pararameters:

    1. The server name.
    2. Useself, "true" or "false". If "true" login is made on the remote server with the same credentials as the local server.
    3. local login. For  which local login you are setting the mapping. NULL means "for everyone else".
    4. Remote login. A login on the remote server. Cannot be a Windows login. Leave NULL for Windows auth.
    5. Password. Password on the remote server.

    So possibly this works:

    EXEC sp_addlinkedsrvlogin SERVER, false, NULL, NULL, NULL
    EXEC sp_addlinkedsrvlogn SERVER, true, Hello, NULL, NULL

    But I have not tested.

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

    Thursday, October 25, 2018 10:05 PM
  • Hi techresearch7777777,

    There is no way you can do to hide the linked server or prevent unauthorized users from trying to use it. But it is feasible to control which user have access to the linked server. You can use the option "Not be made" with a login mapping in your configuration.

    It will specify that a connection will not be made for logins not defined in the list. If someone use an account without the mapped login to use the linked server, he will get the error message like:

    Msg 7416, Level 16, State 1, Line 1
    Access to the remote server is denied because no login-mapping exists

    Best Regards
    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

    • Proposed as answer by AV111 Friday, October 26, 2018 1:14 PM
    Friday, October 26, 2018 6:17 AM