locked
SQL Server Linked Server Security RRS feed

  • Question

  • In SQL Server BOL for sp_addlinkedserver (http://msdn.microsoft.com/en-us/library/ms190479.aspx), there is a Security Note just before the “Permissions” section.

    When a linked server is created by using sp_addlinkedserver, a default self-mapping is added for all local logins. For non-SQL Server providers, SQL Server Authenticated logins may be able to gain access to the provider under the SQL Server service account. Administrators should consider using sp_droplinkedsrvlogin <linkedserver_name>, NULLto remove the global mapping.

    This same note exists for all versions (SQL 2005 – SQL 2012). My question is: How can I programmatically (T-SQL, PowerShell, etc.) detect instances where the global mapping exists and would be at risk. I’ve been guessing at how to query sys.servers and sys.linked_logins, but not sure exactly what combination I’m looking for to ensure I’ve properly identified at-risk instances.

    Thanks,

    Nancy

    Monday, April 30, 2012 9:49 PM

Answers

  • This query should give you what you are looking for, I believe. I've added
    conditions to exlucde linked servers that are other linked servers. As I
    understand the text, there is no security concern for them.

    select *
    from   sys.servers s
    WHERE  s.provider NOT LIKE 'SQLNCLI%'
      AND  s.provider <> 'SQLOLEDB'
      AND  EXISTS (SELECT *
                   FROM   sys.linked_logins ll
                   WHERE  ll.server_id = s.server_id
                     AND  ll.uses_self_credential = 1)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, April 30, 2012 9:58 PM

All replies

  • This query should give you what you are looking for, I believe. I've added
    conditions to exlucde linked servers that are other linked servers. As I
    understand the text, there is no security concern for them.

    select *
    from   sys.servers s
    WHERE  s.provider NOT LIKE 'SQLNCLI%'
      AND  s.provider <> 'SQLOLEDB'
      AND  EXISTS (SELECT *
                   FROM   sys.linked_logins ll
                   WHERE  ll.server_id = s.server_id
                     AND  ll.uses_self_credential = 1)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, April 30, 2012 9:58 PM
  • Awesome! Thanks for the quick response. That indeed looks like where I was headed, just needed some confirmation.

    Remind me to give you a hug at PASS Summit!

    Nancy


    Nancy Hidy Wilson

    Monday, April 30, 2012 10:03 PM