Sql-server – SQL Server Linked Server Best Practices – Step by Step

best practiceslinked-serversql serversql-server-2016

Hello and thanks for stopping by. I'm an accidental DBA looking for some guidance in creating Linked Servers the correct way. MS provides complete descriptions of the datatype and descriptions of all of the various parameters for sp_addlinkedserver, sp_addlinkedsrvlogin and sp_serveroption but no guidance on HOW to align the various options as Best Practices for a given situation.

I have examples from the other DBA's who simply used the 'sa' password but my research indicates I should be using bespoke logins tailored to their Linked Server use. The Problem is I'm so far unable to find the right combination and sequence (order of ops) to correctly create all of the parts and pieces resulting in a Linked Server that allows limited communication between two servers.

Goal: Create a Linked Server between a Source server that will allow a job step from the source server to check certain conditions and if TRUE, invoke sp_start_job on Destination server. …and nothing more.

On advice, I've created two SQL Auth Logins of the same name/pw on both Source and Destination, both with limited 'public' permissions.

I've created Linked Servers attempting to map the local login to the remote login (thinking if I got that far, I'd carefully tinker with the permissions of the Destination login to find the permission to allow it to exec sp_start_job).

But so far, my only reward has been a series of failure notices of various types.

There are a TON of online documents explaining what each various proc/param does but I'm having a difficult time finding some sort of over-view explaining how combinations of procs/params lead to different desired outcomes.

I'm hoping for some useful advice, reference to some 'yet to be discovered' tutorial or maybe even a Step by Step instruction on how to achieve my goal and develop a little self respect. (so far, this task has done nothing but bruise my ego!)

Thank you for your time.

Best Answer

I would start by using Windows Auth and @useself='True', which will use the source server's SQL Server Agent Service Account to make the remote connection. If you get a login failure, look in the target server's SQL Server Error Log to determine what Windows principal to create a login for on the target server. So the linked server definitaion would be something like:

USE [master]
GO

EXEC sp_addlinkedserver @server = N'TargetServer', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'targetserver.mydomain.com'

EXEC sp_addlinkedsrvlogin @rmtsrvname=N'TargetServer',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO

EXEC sp_serveroption @server=N'TargetServer', @optname=N'collation compatible', @optvalue=N'true'
GO

EXEC sp_serveroption @server=N'TargetServer', @optname=N'data access', @optvalue=N'true'
GO

EXEC sp_serveroption @server=N'TargetServer', @optname=N'rpc out', @optvalue=N'true'
GO

EXEC sp_serveroption @server=N'TargetServer', @optname=N'remote proc transaction promotion', @optvalue=N'false'
GO

Impersonating the caller is more complicated with remote connections to the SQL Server where the Linked Server is defined, as you have to properly configure Kerberos Constrained Delegation. But when used from a SQL Agent Job, it's not a "double hop" and so is a really simple configuration.

Related Solutions

Sql-server – Local login impersonation not working with a linked server

The error you have is unrelated to logins as such.

This is caused when SQL Server tries to "pass through" the NT login token to the remote server. It doesn't have permission to pass the token through. The remote server looks for this because the local servers connects with Integrated Security.

You need "Security Account Delegation" to be configured for the local server.

As for the default mapping...

What MSDN says is that if you have "MyDomain\bob" locally then you have an "virtual" entry "stored" locally called "MyDomain\bob". There is no reconciliation of the 2 servers between the 2 servers. This is the same as running this:

EXEC sp_addlinkedsrvlogin 'MyLinkedServer', @useself = 'TRUE';

Note that "MyDomain\MyGroup" is not mapped: only discrete NT users and SQL logins. Not NT Groups.

This is all described in "Security for Linked Servers"

The default mapping for a linked server configuration is to emulate the current security credentials of the login. This kind of mapping is known as self-mapping. When a linked server is added by using sp_addlinkedserver, a default self-mapping is added for all local logins. If security account delegation is available and the linked server supports Windows Authentication, self-mapping for the Windows authenticated logins is supported.

...

If security account delegation is not available on the client or sending server, or the linked server/provider does not recognize Windows Authentication Mode, self-mapping will not work for logins that use Windows Authentication.

Sql-server – SQL SERVER – Linked Server and query performance

Your problem begins and ends with statistics and estimates. I have reproduced your situation on my servers and found some interesting hints, and a workaround solution.

First things first, let's take a look at your execution plan:
When a view is used we can see that a filter is applied after the Remote Query is executed, while without the view there was no filter applied at all. The truth is that the filter was applied inside the Remote Query, at the remote server, before retrieving the data over the network.
Well, obviously applying the filter at the remote server and thus retrieving less data is a better option, and obviously that only happens when not using a view.

So... what is so intersting...?

Surprisingly, when I changed the filter from cognome = 'test' to cognome = N'test' (unicode representation of the string) the view used the same execution plan as the first query did.
I guess the reason is that somehow when using the view SQL Server estimated that there will be a small number of rows returning from the (remote) query, and that a local filtering will be cheaper, but when SQL Server had to implicit convert NVARCHAR to VARCHAR, statistics could no longer be used and the decision to filter locally was not taken.
I have looked for the statistics locally, but the view had no statistics, so my guess is that the view uses the remote statistics in a way that ad-hoc query does not, and than takes the wrong decision.

OK, so what solves the problem?

I stated earlier that there is a workaround (at least until someone comes up with a better solution), and no, I don't mean using unicode for your strings.
I wanted to give an answer first, I still have to find why, but when using an Inline Function SQL Server behaves exactly the same as with the query (without view), so replacing the view with the function will give the same result, in a simple query, and with good peformance (at least in my environment).

My code suggestion for you is:

CREATE FUNCTION fn_anagrafiche2()
RETURNS table
AS
RETURN 
(
    SELECT * 
    FROM dolph2.agendasdn.dbo.vistaanagraficagrp
    UNION
    SELECT * 
    FROM dolph2.acampanet.dbo.vistaanagraficagrp
    UNION
    SELECT * 
    FROM municipio2.dbnet.dbo.vistaanagraficagrp
)
GO

The query will then be:

SELECT * 
FROM fn_anagrafiche2()
WHERE cognome = 'prova'

This works on my servers, but of course test it first.
Note: I do not recommend using SELECT * at all, as it is prone to future errors, I simply used it because it was in your question and there was no need for me to change that when I can add this remark instead :)

Best Answer

Related Solutions

Sql-server – Local login impersonation not working with a linked server

Sql-server – SQL SERVER – Linked Server and query performance

Related Question