locked
How to block linked server access RRS feed

  • Question

  • I have a SQL server that I want to prevent it from being accessed by any other servers via linked server. How to do that?
    Tuesday, February 28, 2012 1:48 AM

Answers

  • If a login (and resultant user) can connect and do something, they can connect with any client; SSMS, sqlcmd, Excel, MS Access, or another instance of SQL Server. The key is to only allow the login/user to do what you want them to do, regardless of how they connect. Usually, execute some stored procedure. If you let the login/user select from a table, they can do that from any client.

    There are some things you can do to make it harder. Things like login triggers can examine sys.dm_exec_sessions and restrict activity. But with a linked server it might be hard to tell which connection is the one you don't want. And connections can lie about their attributes, so it's not a reliable security measure.

    You can also use application roles. That solves some problems but can cause other headaches, so I wouldn't go that route without understanding the effects.

    It might be easier to block the specific action that you don't want the user to do. For example, if they are selecting all the records in a 100,000 record table, they shouldn't be able to do that anyway. The procedure that selects data should restrict any user to selecting only 1 row, or only 100 rows as appropriate.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Proposed as answer by nonno peter lee Wednesday, February 29, 2012 10:16 AM
    • Marked as answer by nonno Wednesday, February 29, 2012 1:41 PM
    Tuesday, February 28, 2012 5:01 PM

All replies

  • Do you have control on these "other servers"?


    www.sqlCMD.ru - all around MS SQL Server

    • Proposed as answer by Smarty Tuesday, February 28, 2012 6:29 AM
    • Unproposed as answer by Smarty Tuesday, February 28, 2012 6:29 AM
    Tuesday, February 28, 2012 6:29 AM
  • Have a read of the following:

    http://technet.microsoft.com/en-us/library/gg723715.aspx

    http://www.sqlservercentral.com/Forums/Topic508767-359-1.aspx


    When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer

    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCSD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    Twitter: @Mr_Wharty
    MC ID: Microsoft Transcript

    Tuesday, February 28, 2012 6:52 AM
  • No, I can only control my server.
    Tuesday, February 28, 2012 9:09 AM
  • If a login (and resultant user) can connect and do something, they can connect with any client; SSMS, sqlcmd, Excel, MS Access, or another instance of SQL Server. The key is to only allow the login/user to do what you want them to do, regardless of how they connect. Usually, execute some stored procedure. If you let the login/user select from a table, they can do that from any client.

    There are some things you can do to make it harder. Things like login triggers can examine sys.dm_exec_sessions and restrict activity. But with a linked server it might be hard to tell which connection is the one you don't want. And connections can lie about their attributes, so it's not a reliable security measure.

    You can also use application roles. That solves some problems but can cause other headaches, so I wouldn't go that route without understanding the effects.

    It might be easier to block the specific action that you don't want the user to do. For example, if they are selecting all the records in a 100,000 record table, they shouldn't be able to do that anyway. The procedure that selects data should restrict any user to selecting only 1 row, or only 100 rows as appropriate.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Proposed as answer by nonno peter lee Wednesday, February 29, 2012 10:16 AM
    • Marked as answer by nonno Wednesday, February 29, 2012 1:41 PM
    Tuesday, February 28, 2012 5:01 PM