Home / Guides / Compare linked servers between two SQL Servers

Compare linked servers between two SQL Servers

A report works on the primary and fails after a failover with error 7202, "Could not find server ... in sys.servers". Or the linked server exists on both servers, but one points at a different database, has RPC Out turned off, or maps the application login to another remote user. This guide shows where SQL Server keeps linked servers and their login mappings, the queries that list them, a PowerShell script that compares a reference server with others setting by setting, what dbatools offers, and the part no query can compare: the stored remote passwords.

Where SQL Server keeps linked servers

• sys.servers: one row per linked or remote server, plus the local server (server_id = 0). Linked servers have is_linked = 1. The row holds the product, the OLE DB provider, the data source, location, catalog and provider string, the time-outs and the options you set in SSMS or with sp_serveroption.
• sys.linked_logins: one row per login mapping. local_principal_id = 0 is the mapping for all other logins; uses_self_credential = 1 means "use the login's own credentials"; otherwise remote_name is the remote login. Microsoft's documentation says the password is stored too, but not exposed in catalog views.
• sp_linkedservers and sp_helplinkedsrvlogin: the same information as procedures. sp_linkedservers returns name, provider, product, data source, provider string, location and catalog, but not the options; sp_helplinkedsrvlogin returns each mapping with Is Self Mapping and Remote Login.

Which column is which option

The names in sys.servers differ from the options in SSMS and sp_serveroption:

• is_data_access_enabled is data access (distributed queries allowed).
• is_remote_login_enabled is rpc, and is_rpc_out_enabled is rpc out, the one you need to run EXEC LinkedServer.db.dbo.proc.
• is_collation_compatible, uses_remote_collation and collation_name are the three collation options.
• lazy_schema_validation, connect_timeout, query_timeout and is_remote_proc_transaction_promotion_enabled (remote proc transaction promotion) complete the list.

Permissions

provider_string is always NULL unless you have ALTER ANY LINKED SERVER. On SQL Server 2022 and later, reading sys.servers and sys.linked_logins requires VIEW SERVER SECURITY STATE. A member of sysadmin sees everything; a login with less may see fewer linked servers, so run the comparison with the same kind of login on every server.

List linked servers and their login mappings

Run each query on both servers. First the linked servers with their settings:

USE [master];
SELECT s.name, s.product, s.provider, s.data_source, s.catalog,
       s.is_data_access_enabled AS data_access,
       s.is_remote_login_enabled AS rpc,
       s.is_rpc_out_enabled AS rpc_out,
       s.is_collation_compatible AS collation_compatible,
       s.uses_remote_collation AS use_remote_collation,
       s.collation_name,
       s.connect_timeout, s.query_timeout,
       s.modify_date
FROM sys.servers AS s
WHERE s.is_linked = 1
ORDER BY s.name;

Then the login mappings, one row per mapping:

USE [master];
SELECT s.name AS linked_server,
       CASE WHEN ll.local_principal_id = 0 THEN N'(all other logins)' ELSE p.name END AS local_login,
       ll.uses_self_credential,
       ll.remote_name
FROM sys.linked_logins AS ll
JOIN sys.servers AS s ON s.server_id = ll.server_id
LEFT JOIN sys.server_principals AS p ON p.principal_id = ll.local_principal_id
WHERE s.is_linked = 1
ORDER BY s.name, local_login;

For two servers and a few linked servers, reading the two results side by side is enough. SSMS can also script a linked server (right-click it under Server Objects, Linked Servers, then Script Linked Server as, CREATE To) so you can put the two scripts in a diff tool. Neither way shows the remote passwords: SQL Server does not return them to anyone.

Compare a reference server with others in PowerShell

This query returns one row per linked server with every setting that should match and its login mappings joined into one text. It needs SQL Server 2017 or later (STRING_AGG). Save it as C:\Scripts\LinkedServers.sql:

USE [master];
SELECT s.name                                        AS linked_server,
       s.product, s.provider, s.data_source, s.location, s.catalog,
       s.provider_string,
       s.connect_timeout, s.query_timeout,
       s.is_data_access_enabled                      AS data_access,
       s.is_remote_login_enabled                     AS rpc,
       s.is_rpc_out_enabled                          AS rpc_out,
       s.is_collation_compatible                     AS collation_compatible,
       s.uses_remote_collation                       AS use_remote_collation,
       s.collation_name,
       s.lazy_schema_validation,
       s.is_remote_proc_transaction_promotion_enabled AS remote_proc_transaction_promotion,
       (SELECT STRING_AGG(CAST(CONCAT(
                   CASE WHEN ll.local_principal_id = 0 THEN N'(all other logins)' ELSE p.name END, N' -> ',
                   CASE WHEN ll.uses_self_credential = 1 THEN N'(self)'
                        ELSE ISNULL(ll.remote_name, N'(no login)') END) AS nvarchar(max)), N'; ')
               WITHIN GROUP (ORDER BY CASE WHEN ll.local_principal_id = 0 THEN N'' ELSE p.name END)
        FROM sys.linked_logins AS ll
        LEFT JOIN sys.server_principals AS p ON p.principal_id = ll.local_principal_id
        WHERE ll.server_id = s.server_id)            AS login_mappings,
       s.modify_date
FROM sys.servers AS s
WHERE s.is_linked = 1
ORDER BY s.name;

The script runs it on the reference server and on every server you compare with, matches the linked servers by name and reports each one as Same, Missing on the other server, Only on the other server, or Different with the settings that differ and both values. A provider string can contain a password, so for that column it only says that it differs. It uses Invoke-Sqlcmd from Microsoft's SqlServer module (Install-Module SqlServer once); a server that cannot be read is reported and skipped.

param(
    [string]$Reference = 'SQL01',                    # the server whose linked servers are "right"
    [string[]]$Compare = @('SQL02', 'SQL03\DR'),     # the servers to check against it
    [string]$QueryFile = 'C:\Scripts\LinkedServers.sql',
    [string]$CsvPath   = "$env:USERPROFILE\Desktop\LinkedServerCompare.csv"
)
Import-Module SqlServer -ErrorAction Stop
$query  = Get-Content -Path $QueryFile -Raw
$fields = 'product', 'provider', 'data_source', 'location', 'catalog', 'provider_string',
          'connect_timeout', 'query_timeout', 'data_access', 'rpc', 'rpc_out',
          'collation_compatible', 'use_remote_collation', 'collation_name',
          'lazy_schema_validation', 'remote_proc_transaction_promotion', 'login_mappings'

function Get-LinkedServers([string]$Instance) {
    $map = @{}                                       # linked server name -> its row (names compare case-insensitively)
    foreach ($row in Invoke-Sqlcmd -ServerInstance $Instance -Database master -Query $query -QueryTimeout 120 -ErrorAction Stop) {
        $map[$row.linked_server] = $row
    }
    $map
}

$ref = Get-LinkedServers $Reference
$result = foreach ($server in $Compare) {
    try   { $other = Get-LinkedServers $server }
    catch { Write-Warning "${server}: $($_.Exception.Message)"; continue }

    foreach ($name in (@($ref.Keys) + @($other.Keys) | Sort-Object -Unique)) {
        $a = $ref[$name]; $b = $other[$name]
        $details = ''
        $status = if (-not $b) { "Missing on $server" }
                  elseif (-not $a) { "Only on $server" }
                  else {
                      $diff = foreach ($f in $fields) {
                          if ("$($a.$f)" -ne "$($b.$f)") {
                              if ($f -eq 'provider_string') { 'provider_string (values hidden)' }   # may hold a password
                              else { "${f}: '$($a.$f)' vs '$($b.$f)'" }
                          }
                      }
                      if ($diff) { $details = $diff -join '; '; 'Different' } else { 'Same' }
                  }
        [pscustomobject]@{ LinkedServer = $name; Reference = $Reference; Server = $server; Status = $status; Details = $details }
    }
}

$result | Where-Object Status -ne 'Same' | Format-Table -AutoSize -Wrap
$result | Export-Csv -Path $CsvPath -NoTypeInformation -Encoding UTF8
"{0} linked servers compared, {1} not the same; all rows in {2}" -f @($result).Count, @($result | Where-Object Status -ne 'Same').Count, $CsvPath

Save it as C:\Scripts\Compare-LinkedServers.ps1 and run it:

C:\Scripts\Compare-LinkedServers.ps1 -Reference SQL01 -Compare SQL02

# Example output
LinkedServer Reference Server Status           Details
------------ --------- ------ ------           -------
HR           SQL01     SQL02  Missing on SQL02
ONLYB        SQL01     SQL02  Only on SQL02
REPORTS      SQL01     SQL02  Different        provider_string (values hidden)
SALESDB      SQL01     SQL02  Different        catalog: 'Sales' vs 'SalesArchive'; rpc_out: 'True' vs 'False';
                                               login_mappings: '(all other logins) -> (self); app_reader -> reader'
                                               vs '(all other logins) -> (no login); app_reader -> reader'

4 linked servers compared, 4 not the same; all rows in C:\Users\you\Desktop\LinkedServerCompare.csv

Tested on a Windows 11 test machine with two SQL Server 2025 LocalDB instances (17.0.4025.3) and made-up linked servers that point at nothing (only their definitions are compared, so nothing has to answer). Both list queries, sp_linkedservers, sp_helplinkedsrvlogin and the compare query ran as shown; the script, with the SqlServer module 22.4.5, found the missing, extra and changed linked servers above (a different catalog, RPC Out turned off, a changed "all other logins" mapping and a different provider string), matched names regardless of case, and reported a third instance that does not exist and skipped it. It was not run against production servers. In the same test, a linked server created with the SQL Server shorthand had RPC and RPC Out on, while one created with the MSOLEDBSQL provider had both off: two linked servers set up by hand can differ without anyone noticing.

What a query cannot compare

• Remote passwords. A mapping to a remote SQL login stores its password, and no catalog view or procedure returns it. Two servers that both map app_reader to reader look the same even if one has last year's password. The only check is to use the linked server: EXEC sp_testlinkedserver N'SALESDB'; on each server (or Test Connection on the linked server in SSMS) raises an error with the reason when the connection fails. Run it as a login that uses the mapping you want to test.
• Whether the remote side is the same. Two servers can define the linked server identically while one cannot reach the remote host, or the remote login lacks permissions there. Again, only a test connection or a real query tells you.
• Differences that are on purpose. On a pair of servers, each one often has a linked server that points at the other, so the data source differs by design. Decide which linked servers must match and review the rest by hand.

Why it matters on availability group replicas

An availability group copies its user databases to every replica, not master or msdb. Linked servers live in master, so a linked server you create on the primary does not exist on the secondaries until you create it there too. Microsoft's documentation on contained availability groups describes it: keeping objects in master and msdb the same on every replica is a manual job in a regular availability group. Nothing warns you until the failover: then every query, view, procedure or Agent job step that uses the linked server's four-part name (SALESDB.Sales.dbo.Orders) fails on the new primary with error 7202: Could not find server 'SALESDB' in sys.servers.

Run the comparison before a planned failover and after any change to a linked server, and include the mappings: a linked server that exists on both replicas but maps the application login only on the old primary fails just the same.

dbatools: Get-DbaLinkedServer and Copy-DbaLinkedServer

The free, community-run dbatools module can read linked servers from several instances and copy them, passwords included:

# Install-Module dbatools -Scope CurrentUser    (once)

# The linked servers of both servers, side by side by name
Get-DbaLinkedServer -SqlInstance SQL01, SQL02 | Sort-Object Name, SqlInstance |
    Format-Table Name, SqlInstance, ProviderName, DataSource, Catalog, RpcOut, DataAccess

# Which linked servers from SQL01 are missing on SQL02 (existing names are skipped)
Copy-DbaLinkedServer -Source SQL01 -Destination SQL02 -ExcludePassword -WhatIf

• Get-DbaLinkedServer returns the linked servers of one or more instances as SMO objects. It has no compare of its own; sorted by name, with the properties you care about as columns, two servers read side by side. Its RemoteUser column shows only one mapping per linked server, so use the mappings query above for those.
• Copy-DbaLinkedServer copies linked servers from a source to one or more destinations. Linked servers that already exist on the destination are skipped unless you add -Force, which drops and recreates them. With -WhatIf it prints "Migrating HR" for a missing one and "Linked server SALESDB exists on ..." for the others, so it tells you which ones are missing, not which ones differ.
• Passwords: according to its documentation, it copies the stored remote passwords by decrypting them, which needs sysadmin on both instances, Windows administrator rights on the source host and a dedicated administrator connection (DAC) to the source; -ExcludePassword copies only the definitions and leaves the passwords for you to set.
• Compare-DbaAvailabilityGroup compares Agent jobs, logins, credentials and operators across replicas, but not linked servers.

We ran the two commands above with dbatools 2.9.1 against two SQL Server 2025 LocalDB instances (the copy only with -WhatIf and -ExcludePassword). The password copy and Compare-DbaAvailabilityGroup are described from the dbatools help, not tested; check it against the version you have installed (Get-Help Copy-DbaLinkedServer -Full), and remember that without -WhatIf the copy changes the destination server.

Where comparing linked servers this way stops

• Names are the key. A linked server renamed on one server shows up as one missing and one extra.
• Passwords and the remote end are invisible to the query, as above; only a test connection proves them.
• A snapshot. The script tells you what differs now. It does not run on its own, keep a history or fix anything. Fixing means sp_serveroption for an option, sp_addlinkedsrvlogin for a mapping (with the password typed again), and for a different provider or data source usually dropping and recreating the linked server, which deletes its mappings and stored passwords.
• Older servers. STRING_AGG needs SQL Server 2017 or later; on older versions compare the mappings with the second query above instead.

Questions

Related

• Sync SQL Server logins and Agent jobs across availability group replicas
• Compare SQL Agent jobs between two SQL Servers
• SQLParity: keep your SQL Servers the same
• All guides

Sources (Microsoft Learn): sys.servers, sys.linked_logins, sp_linkedservers, sp_helplinkedsrvlogin, sp_serveroption, sp_addlinkedsrvlogin, sp_testlinkedserver, What is a contained availability group?, STRING_AGG, Invoke-Sqlcmd. dbatools: Get-DbaLinkedServer, Copy-DbaLinkedServer, Compare-DbaAvailabilityGroup.