Linked Servers in SQL Server – Performance and Security [Video]

Linked Servers are easy to set up in SQL Server, but they can have major problems with security and performance. Join Kendra to find out what may be wrong with your Linked Servers, and your options for fixing them.

Got limited time? We got your back. Check out the table of contents for the video below, if you want to skip around.

Linked Servers – Table of Contents

01:55 – Problem #1: sloppy security
03:28 – Problem #2: Questionable queries
04:24 – Hey, why are you filtering AFTER?
05:05 – The “Collation Compatible” Setting
06:34 – What can go wrong?
07:26 – Problem #3: Terrible Statistics
08:03 – Thankfully, Microsoft finally documented this
08:53 – But… we have a conflict!
09:53 – SQL Server 2012 SP1 offers relief
10:47 – Security option: this isn’t perfect
12:07 – Impersonation/Kerberos: this is harder than it looks
14:35 – Reduce security risks
15:56 – Learn more: BrentOzar.com/GO/Link

Previous Post
New sp_Blitz® and sp_BlitzCache® Updates
Next Post
Microsoft Ignite Morning Keynote Liveblog #MSIgnite

20 Comments. Leave new

  • Hey, great talk, and I learnt a lot!

    We have a few Linked servers in our environment (none running as sa luckily!), but I have a finer question regarding the enhanced abilities of SQL 2012 SP1:

    We have an externally provided database which was recently upgraded to run on SQL 2012 SP2, however the databse itself is still in Sql 2005 compat mode. Do you know if we’d be likely to get the stats improvements or if we have to go begging others to upgrade the compat level?

    Cheers again for all the info on here, it’s a credit to you guys that this is the first time I’ve actually had to ask a question, normally they’re ansered in the talks 🙂

    Tim

    Reply
    • Kendra Little
      May 4, 2015 8:07 pm

      Hi Tim,

      You don’t have to change the database compatibility level on the remote database for the change to take effect, it happens with the SQL Server version. This does mean that you can’t turn OFF the new functionality by lowering the compat mode either — I can’t really think of why you’d want to turn it off, but if you do you must use trace flag 9485.

      This is described in the release notes for 2012 SP1 under the section “DBCC SHOW_STATISTICS works with SELECT permission” if you want to read more on it: https://technet.microsoft.com/en-us/library/bb500435(v=sql.110).aspx#bkmk_sp1

      Reply
  • Great summary – had the chance to learn a few new things!

    We have Kerberos and double-hop set up properly on our domain, and most of our linked servers are set to use the “Be made using the login’s current security context” (in other words, to use Kerberos), and t works fantastic most of the time. However, we run into an issue sometimes where we suddenly start receiving an error message when we attempt to use the linked server (“Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’.”), which is the same error you receive if Kerberos isn’t set up properly. Oddly, it only seems to occur for a single linked server connection at a time – other linked servers on the source continue to work without issues. It’s resolved by restarting the SQL instance, but since that’s not something we can normally do on a whim, we end up hard-coding a read only (low permission – no SA!) account on the security screen and moving on.

    Do you know why this issue occurs? I’ve seen an MSKB article where it occurs in SQL 2005, but we’re on newer versions and it continues to occasionally occur. It doesn’t seem like a configuring or Kerberos setup issue, as a linked server will work for months and then suddenly start to have issues, only to be resolved by a service restart, but I’m open to whatever advice you might have.

    Thanks!

    Reply
    • Kendra Little
      June 11, 2015 2:41 pm

      I’d start with Adam Saxton’s Kerberos checklist here: http://blogs.msdn.com/b/psssql/archive/2010/06/23/my-kerberos-checklist.aspx

      Reply
    • Steve St.Laurent
      September 1, 2016 6:57 am

      I think you’ll find that it’s not the SQL instance that you need to restart. This is an occasional problem that happens from your workstation. The next time it happens try restarting SSMS and it that doesn’t work restart the workstation that your having the issue on and I think you’ll find one of those resolves it. I recently ran into this and at first I failed the instance over to another node on the cluster (in essence restarting it) and found that I still had the issue. I then remote logged into the server and tried it again and the query worked fine from the server. Then I rebooted my workstation and that resolved the issue. It came up again two days later and that time I was able to just close SSMS and restart it and that resolved it. It’s only happened to me twice so I haven’t been able to prove definitively whether just restarting SSMS works.

      Reply
  • Maybe a stupid question, but why does the COLLATION affects the example query in your video?

    SELECT *
    FROM dbo.customers c
    INNER JOIN ServerB.Sales.dbo.Invoices i
    ON c.CustomerId = i.CustomerId
    WHERE c.CustomerName LIKE ‘Brent Ozar%’

    If I assume, that CustomerId is an integer column (or BigInt) I see no problems relating the collation, becuase there is no collation for numeric data types and the compare with ‘Brent Ozar%’ happens on the local database. Of course – if you would filter for i.ReceipientName – it would be understandable that the server gets all invoices and filter local.

    Reply
    • That example query isn’t specifically about the collation — it’s just a general point that the order of processing isn’t guaranteed with a remote query, even if it’s not a collation issue.

      Reply
  • Another question:

    What are the security option
    – Be made without using a security context
    – Be made using the login’s current security context

    for? If I read the description of the second one, I’d guess that it is the same as Impersonate in the box above (which needs Kerberos Authentication to work well), but I have no idea, what the first one is for (as far I know there is no anonymous login for SQL Server).

    Reply
  • Hi Kendra,

    Thank you for a great review of our shameful Linked server past and the great work around in SQL Server 2012. Can you tell me if this will work with Kerberos for a triple hop?

    Reply
  • Could you please double-check the YouTube policy acceptance procedure? Clicking on it doesn’t seem to do anything. I have tried on Chrome, Firefox and Edge. Also tried on a mobile browser. On all four the red button to “Accept YouTube Content” keeps reappearing. Thank you!

    Reply
    • TL – you may be using a script or cookie blocker. It’s working fine here. Thanks! Brent

      Reply
    • I’m having the same problem. Allowed pop ups from web domain, didn’t help. Is there another way to access this video?

      Reply
      • It started working for me on Friday last week (8 June). I don’t think it was anything browser related as I asked a few colleagues to check and also tried on my mobile browser and it never worked.

        But it’s working now so all good! 🙂

        Reply
  • Thank you – very informative!
    Makes me realise quite how important it is to get away from the poisoned chalice of our linked server setup. Just about every bad practice mentioned we do! My problem is keeping the development teams happy and working (and not bring down any existing applications!).
    A lot of reading & work in store…

    Reply
  • Hi,
    I am trying to run to run the Power BI First responder sp_scripts for Linked Server. My task is to two run the scripts on multiple servers and create the sp tables in only one server and the data from all servers should be written into the single instance.
    I noticed you have mentioned @OutputServerName is not functional.
    Can you please tell me how I can use the scripts for my task?

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}