Auditing Linked Server Security: Who Connects as Whom

2026-11-08 · by Hannah Vernon · in security

On my test instance, a login with no server roles and no access to the database ran a SELECT against a salary table and got every row back. It didn’t need a grant on the table because it went through a linked server, and the linked server connected as a different login.

A DBA inspects a turnstile that hands every person crossing a sky bridge between two server buildings the same gold master key.

A linked server lets one SQL Server instance run queries and procedure calls against another data source. The local instance has to log in to the remote server on behalf of whoever runs the query. The login mapping decides which credentials it uses. If every local login maps to one fixed remote login, every local login gets that remote login’s permissions on the remote server. I tested that on SQL Server 2025 and wrote an audit query that finds it.

Where the Settings Live

sys.servers has one row per linked server, plus a row for the local server with server_id 0.^[1] The columns I check first:

is_linked: 1 for a standard linked server, 0 for an old-style remote server added with sp_addserver.
is_data_access_enabled: whether the server can be used in distributed queries (four-part names, OPENQUERY).
is_rpc_out_enabled: whether outgoing remote procedure calls are allowed, which includes EXEC (...) AT.
provider and data_source: the OLE DB provider and the remote server it points at.
provider_string: extra connection properties. It’s NULL unless the caller has ALTER ANY LINKED SERVER.

sys.linked_logins has one row per login mapping.^[2] local_principal_id is the local login the mapping applies to, and 0 means the mapping applies to every login that has no mapping of its own. uses_self_credential is 1 when the session uses its own credentials. When it’s 0, the session uses remote_name and a stored password. The password isn’t exposed in any catalog view.

The Four Mapping Behaviours

sp_addlinkedsrvlogin creates the mappings. When you create a linked server with sp_addlinkedserver, SQL Server adds a default mapping for all local logins that uses each login’s own credentials.^[3] These four behaviours matter most for an audit:

Behaviour	How it’s set	Row in sys.linked_logins
Every login uses its own credentials	`@useself = 'true'`, `@locallogin = NULL` (the default)	`local_principal_id` 0, `uses_self_credential` 1
Every login connects as one remote login	`@useself = 'false'`, `@locallogin = NULL`, `@rmtuser`, `@rmtpassword`	`local_principal_id` 0, `uses_self_credential` 0, `remote_name` set
Only specific local logins can connect	`@locallogin = N'name'` for each login	one row per login, non-zero `local_principal_id`
Other logins can’t connect (“Not be made”)	drop the default mapping with `sp_droplinkedsrvlogin` and `@locallogin = NULL`	no row with `local_principal_id` 0

In my test the last two go together: sat_ls_mapped has one explicit mapping and no default. The SSMS Security page offers a fifth option, “Be made without using a security context”.^[4] With sp_addlinkedsrvlogin, @useself = 'false' with @rmtuser and @rmtpassword NULL connects with no login or password.^[3] I didn’t test it.

For self-mapping with a SQL login, the remote server needs a login with the same name and password.^[4] For a Windows login, the documentation says delegation is required when the connection makes more than one hop.^[3] Whatever the mapping, permissions on remote objects are decided by the remote server, for the login it sees.^[3]

The Test

I used one instance, SQL Server 2025 (17.0.5015.4, Enterprise Developer), on my workstation. I created three loopback linked servers that point back at the same instance, so “remote” here means the same instance reached through the linked server. I also created two SQL logins with random passwords:

sat_ls_user: a test login with no server roles and no user in any database I created.
sat_ls_remote: the fixed remote login. Its only permission is SELECT on dbo.salary in a scratch database, sat_ls_audit.

The three linked servers:

sat_ls_self: the default mapping, so every login uses its own credentials.
sat_ls_fixed: the default mapping replaced with a fixed mapping to sat_ls_remote for all logins.
sat_ls_mapped: no default mapping, and one explicit mapping for sat_ls_remote only.

This is the setup for sat_ls_fixed. The password is generated on the server and is never sent to the client:

USE [master];

DECLARE @remote_pwd nvarchar(128) = CONVERT(nvarchar(36), NEWID()) + N'Zq7!' + CONVERT(nvarchar(36), NEWID());
DECLARE @sql        nvarchar(max) = N'CREATE LOGIN [sat_ls_remote] WITH PASSWORD = ' + QUOTENAME(@remote_pwd, N'''') + N', CHECK_POLICY = ON;';

EXEC (@sql);

/* Test instances only: TrustServerCertificate skips certificate validation, which allows a man-in-the-middle attack. */
EXEC [master].[dbo].[sp_addlinkedserver]
      @server     = N'sat_ls_fixed'
    , @srvproduct = N''
    , @provider   = N'MSOLEDBSQL19'
    , @datasrc    = N'.\SQL2025'
    , @provstr    = N'TrustServerCertificate=Yes;User ID=sat_ls_uid';

EXEC [master].[dbo].[sp_droplinkedsrvlogin] @rmtsrvname = N'sat_ls_fixed', @locallogin = NULL;

EXEC [master].[dbo].[sp_addlinkedsrvlogin]
      @rmtsrvname  = N'sat_ls_fixed'
    , @useself     = 'false'
    , @locallogin  = NULL
    , @rmtuser     = N'sat_ls_remote'
    , @rmtpassword = @remote_pwd;

EXEC [master].[dbo].[sp_serveroption] @server = N'sat_ls_fixed', @optname = N'rpc out', @optvalue = N'true';

USE [master];

DECLARE @remote_pwd nvarchar(128) = CONVERT(nvarchar(36), NEWID()) + N'Zq7!' + CONVERT(nvarchar(36), NEWID());

DECLARE @sql nvarchar(max) = N'CREATE LOGIN [sat_ls_remote] WITH PASSWORD = ' + QUOTENAME(@remote_pwd, N'''') + N', CHECK_POLICY = ON;';

EXEC (@sql);

/* Test instances only: TrustServerCertificate skips certificate validation, which allows a man-in-the-middle attack. */

EXEC [master].[dbo].[sp_addlinkedserver]

@server = N'sat_ls_fixed'

, @srvproduct = N''

, @provider = N'MSOLEDBSQL19'

, @datasrc = N'.\SQL2025'

, @provstr = N'TrustServerCertificate=Yes;User ID=sat_ls_uid';

EXEC [master].[dbo].[sp_droplinkedsrvlogin] @rmtsrvname = N'sat_ls_fixed', @locallogin = NULL;

EXEC [master].[dbo].[sp_addlinkedsrvlogin]

@rmtsrvname = N'sat_ls_fixed'

, @useself = 'false'

, @locallogin = NULL

, @rmtuser = N'sat_ls_remote'

, @rmtpassword = @remote_pwd;

EXEC [master].[dbo].[sp_serveroption] @server = N'sat_ls_fixed', @optname = N'rpc out', @optvalue = N'true';

TrustServerCertificate=Yes is there because of the driver. Starting with SQL Server 2025, linked servers that use MSOLEDBSQL get version 19 of the OLE DB driver, the same driver MSOLEDBSQL19 names.^[5] Version 19 encrypts by default, and it always checks the server’s certificate when the connection is encrypted.^[6] My lab instances don’t force encryption, but they have no certificate configured, so the only one they offer is self-signed. A query through sat_ls_self showed encrypt_option TRUE, so the driver encrypted the connection anyway.

With no provider string, my linked servers failed with “The certificate chain was issued by an authority that is not trusted” until I added TrustServerCertificate=Yes. That’s fine for a lab, but it skips certificate validation, which allows a man-in-the-middle attack.^[7] On a real server I’d install a certificate signed by a certificate authority both servers trust, because with mandatory encryption the documentation says the self-signed certificate isn’t accepted.^[5] The other documented choice is Encrypt=Optional in the provider string. That leaves the connection unencrypted unless the server requires encryption.^[6] The sp_addlinkedserver page also documents trace flag 17600 to keep the version 18 behaviour when you can’t change the linked server definition.^[5] I explain the User ID in that provider string further down.

Then I connected as sat_ls_user and ran the same two statements against each linked server:

EXEC (N'SELECT SUSER_SNAME() AS [remote_login], ORIGINAL_LOGIN() AS [original_login], IS_SRVROLEMEMBER(N''sysadmin'') AS [is_sysadmin];') AT [sat_ls_fixed];

SELECT
      [employee_id]
    , [salary]
FROM
    [sat_ls_fixed].[sat_ls_audit].[dbo].[salary]
ORDER BY
      [employee_id];

EXEC (N'SELECT SUSER_SNAME() AS [remote_login], ORIGINAL_LOGIN() AS [original_login], IS_SRVROLEMEMBER(N''sysadmin'') AS [is_sysadmin];') AT [sat_ls_fixed];

SELECT

[employee_id]

, [salary]

FROM

[sat_ls_fixed].[sat_ls_audit].[dbo].[salary]

ORDER BY

[employee_id];

What the Test Login Could Do

Run directly, the SELECT from sat_ls_audit.dbo.salary failed with Msg 916, because sat_ls_user can’t access the database. Through the linked servers:

Linked server	Login on the remote side	Read of dbo.salary
sat_ls_self	sat_ls_user	Msg 7314, table not found or no permission
sat_ls_fixed	sat_ls_remote	all 3 rows returned
sat_ls_mapped	none, Msg 7416	Msg 7416, no login mapping

Through sat_ls_fixed, both SUSER_SNAME() and ORIGINAL_LOGIN() on the remote side returned sat_ls_remote. Neither function showed which local login started the call. A login audit on the remote server can only record what it sees, and here that was the fixed login.

An INSERT through sat_ls_fixed failed with Msg 229, because sat_ls_remote only has SELECT. The remote login’s permissions decided it, and sat_ls_user‘s own permissions never came into it. My Windows login, a sysadmin, also connected as sat_ls_remote through sat_ls_fixed, and got IS_SRVROLEMEMBER 0 on the remote side. Through sat_ls_mapped, the same sysadmin login got Msg 7416, because it had no mapping and the default mapping was gone. sat_ls_remote, which has an explicit mapping, connected.

Why a Fixed sysadmin Login Is the One to Find

I gave sat_ls_remote one SELECT grant on purpose. I didn’t test a fixed mapping to a sysadmin login. The mechanism doesn’t change with the permission level, though: the remote server checks the remote login’s permissions.^[3] If the fixed remote login is a sysadmin on the remote server and RPC OUT is on, any login that doesn’t have its own mapping can send any statement to the remote server with EXEC (...) AT and run it as that sysadmin. Microsoft’s documentation warns that with this option, any user on the instance can access the remote server using that security context.^[4] For a mapping that stores a SQL login and password, the sp_addlinkedsrvlogin page also says never to use an administrator’s password.^[3]

On a loopback linked server like mine, the remote server is the local server. A fixed sysadmin mapping on a loopback link would let every login without its own mapping run statements as sysadmin on that same instance.

RPC OUT and Data Access Are Separate Switches

sp_serveroption sets both options, and changing them requires ALTER ANY LINKED SERVER.^[8] I turned each one off on sat_ls_fixed in turn and repeated the test as sat_ls_user:

Setting	EXEC (…) AT	Four-part name read
rpc out off, data access on	Msg 7411, not configured for RPC	3 rows
rpc out on, data access off	returned sat_ls_remote	Msg 7411, not configured for DATA ACCESS

If a linked server is only used for four-part name queries, turning RPC OUT off blocks EXEC (...) AT and remote procedure calls. It doesn’t change the login mapping, and queries that use data access still run as the mapped login.

What a Low-Privilege Login Can See

As sat_ls_user, sys.servers listed sat_ls_self and sat_ls_fixed, plus one unrelated lab linked server, with provider_string NULL. It didn’t list sat_ls_mapped, which has no default mapping. That matches the documentation: the default mapping is what makes a linked server visible to all logins.^[1] sys.linked_logins returned no rows. On SQL Server 2022 and later it requires VIEW SERVER SECURITY STATE.^[2] I ran the audit below as a sysadmin.

The Audit Query

This lists every linked and remote server with its default mapping, any explicit mappings, and a rough risk label. It reads only catalog views, and it needs SQL Server 2017 or later because it uses STRING_AGG.^[9] I ran it on both instances. On SQL Server 2025 it returned my three test servers plus one unrelated lab linked server. On SQL Server 2019 (15.0.4500.2), which has no linked servers, it ran and returned no rows.

USE [master];

SELECT
      [s].[name]                   AS [linked_server]
    , [s].[provider]
    , [s].[data_source]
    , [s].[is_data_access_enabled] AS [data_access]
    , [s].[is_rpc_out_enabled]     AS [rpc_out]
    , CASE
          WHEN [d].[server_id] IS NULL THEN N'not be made'
          WHEN [d].[uses_self_credential] = 1 THEN N'own credentials'
          WHEN [d].[remote_name] IS NULL THEN N'no security context'
          ELSE N'fixed: ' + [d].[remote_name]
      END                          AS [unmapped_logins]
    ,
      (
          SELECT
              STRING_AGG
              (
                    CONVERT(nvarchar(max), [lp].[name] + N' -> '
                        + CASE
                              WHEN [ll].[uses_self_credential] = 1 THEN N'self'
                              ELSE COALESCE([ll].[remote_name], N'(no context)')
                          END)
                  , N', '
              )
          FROM
              [sys].[linked_logins] AS [ll]
          INNER JOIN
              [sys].[server_principals] AS [lp]
                  ON [lp].[principal_id] = [ll].[local_principal_id]
          WHERE
              [ll].[server_id] = [s].[server_id]
      )                            AS [explicit_mappings]
    , CASE
          WHEN [d].[uses_self_credential] = 0 AND [d].[remote_name] IS NOT NULL THEN N'HIGH: every login runs as ' + [d].[remote_name]
          WHEN [d].[uses_self_credential] = 0 THEN N'REVIEW: anonymous connection for every login'
          WHEN [d].[uses_self_credential] = 1 THEN N'MEDIUM: every login can try its own credentials'
          ELSE N'LOW: only mapped logins connect'
      END                          AS [mapping_risk]
FROM
    [sys].[servers] AS [s]
LEFT OUTER JOIN
    [sys].[linked_logins] AS [d]
        ON  [d].[server_id] = [s].[server_id]
        AND [d].[local_principal_id] = 0
WHERE
    [s].[server_id] > 0
ORDER BY
      [s].[name];

USE [master];

SELECT

[s].[name] AS [linked_server]

, [s].[provider]

, [s].[data_source]

, [s].[is_data_access_enabled] AS [data_access]

, [s].[is_rpc_out_enabled] AS [rpc_out]

, CASE

WHEN [d].[server_id] IS NULL THEN N'not be made'

WHEN [d].[uses_self_credential] = 1 THEN N'own credentials'

WHEN [d].[remote_name] IS NULL THEN N'no security context'

ELSE N'fixed: ' + [d].[remote_name]

END AS [unmapped_logins]

(

SELECT

STRING_AGG

(

CONVERT(nvarchar(max), [lp].[name] + N' -> '

+ CASE

WHEN [ll].[uses_self_credential] = 1 THEN N'self'

ELSE COALESCE([ll].[remote_name], N'(no context)')

END)

, N', '

)

FROM

[sys].[linked_logins] AS [ll]

INNER JOIN

[sys].[server_principals] AS [lp]

ON [lp].[principal_id] = [ll].[local_principal_id]

WHERE

[ll].[server_id] = [s].[server_id]

) AS [explicit_mappings]

, CASE

WHEN [d].[uses_self_credential] = 0 AND [d].[remote_name] IS NOT NULL THEN N'HIGH: every login runs as ' + [d].[remote_name]

WHEN [d].[uses_self_credential] = 0 THEN N'REVIEW: anonymous connection for every login'

WHEN [d].[uses_self_credential] = 1 THEN N'MEDIUM: every login can try its own credentials'

ELSE N'LOW: only mapped logins connect'

END AS [mapping_risk]

FROM

[sys].[servers] AS [s]

LEFT OUTER JOIN

[sys].[linked_logins] AS [d]

ON [d].[server_id] = [s].[server_id]

AND [d].[local_principal_id] = 0

WHERE

[s].[server_id] > 0

ORDER BY

[s].[name];

The output for my three test servers, with the unrelated row and the provider and data_source columns removed to fit (both columns were MSOLEDBSQL19 and .\SQL2025 for my servers):

linked_server | data_access | rpc_out | unmapped_logins      | explicit_mappings     | mapping_risk
sat_ls_fixed  | 1           | 1       | fixed: sat_ls_remote | NULL                  | HIGH: every login runs as sat_ls_remote
sat_ls_mapped | 1           | 1       | not be made          | sat_ls_remote -> self | LOW: only mapped logins connect
sat_ls_self   | 1           | 1       | own credentials      | NULL                  | MEDIUM: every login can try its own credentials

The labels are my own rough ranking, not a standard. A HIGH row tells you which remote login to look up next. The local catalog can’t tell you what that login can do on the remote server, so connect to the server in data_source and check its roles and grants there. Mapping Permission Sprawl with AI covers tracing those permissions. Explicit mappings to a fixed remote login deserve the same check, because whoever controls that local login gets the remote login’s rights.

A Lab Detail: Msg 7416 and the Provider String

My first run used TrustServerCertificate=Yes as the whole provider string. sat_ls_user got Msg 7416, “no login-mapping exists”, from all three linked servers, including the two with a default mapping. The sp_addlinkedserver documentation lists the same error for logins that aren’t in sysadmin, and the fix it gives is adding User ID to the provider string.^[5]

After I added User ID=sat_ls_uid (a name that doesn’t exist), each linked server behaved as its mapping describes. In a separate probe, a fixed mapping to sat_ls_remote with User ID=sat_ls_user in the provider string still connected as sat_ls_remote. On this build the mapping decided the remote login, and the User ID value didn’t. The provider string can hold other connection properties as well, so I’d read provider_string in sys.servers during an audit, as a login with ALTER ANY LINKED SERVER.

Limits of This Test

I tested one build, on one workstation, with loopback linked servers. A real remote server can add network, Kerberos and certificate behaviour that I didn’t test.
I didn’t test Windows logins through a self-mapping, delegation, or the “no security context” option.
I didn’t test a sysadmin remote login. That section relies on the mechanism I measured and on Microsoft’s documentation.
I created the linked servers with the MSOLEDBSQL19 provider. Other providers may behave differently, especially with provider strings.

What I’d Change After the Audit

For each HIGH row, I’d find out who uses the linked server, then replace the default fixed mapping with explicit mappings for those logins and drop the default. I’d give the remote login only the permissions those callers need, and turn RPC OUT off where nothing uses EXEC (...) AT or remote procedures. Changing a mapping with sp_addlinkedsrvlogin needs ALTER ANY LOGIN.^[3] Test the change on a non-production instance first, because a job or report that relied on the default mapping can start failing with Msg 7416.

Security Audits: Finding What You Missed lists linked servers among the checks worth running regularly. If your linked servers are slow rather than over-privileged, Slow inserts across a linked server covers one cause.

Related: What a Linked Server Query Sends: Four-Part Names, OPENQUERY and EXEC AT (available 2026-11-09), Cross-Database Ownership Chaining: What a db_owner Can Reach and Finding Unencrypted SQL Server Connections, and the Instances That Accept Them (available 2026-11-12).

If you’ve run an audit like this and found a fixed mapping to a sysadmin login, or a mapping I haven’t covered, I’d like to hear about it. Find me on Bluesky or LinkedIn.

References

sys.servers (Transact-SQL) - Microsoft Learn. Defines is_linked, is_rpc_out_enabled, is_data_access_enabled, provider, data_source and provider_string (NULL without ALTER ANY LINKED SERVER), and says the default login mapping makes linked servers visible to all logins. ↩
sys.linked_logins (Transact-SQL) - Microsoft Learn. Defines local_principal_id (0 = wildcard or public), uses_self_credential and remote_name, says the password isn’t exposed, and says SQL Server 2022 and later require VIEW SERVER SECURITY STATE. ↩
sys.sp_addlinkedsrvlogin (Transact-SQL) - Microsoft Learn. Describes the default self mapping, @useself and @locallogin, delegation for multiple hops, permissions being decided by the linked server, the warning against administrator passwords, and the ALTER ANY LOGIN requirement. ↩
Create linked servers (SQL Server Database Engine) - Microsoft Learn. Lists the Security page options (Not be made, without a security context, current security context, this security context) and the caution that any user can use a fixed security context. ↩
sys.sp_addlinkedserver (Transact-SQL) - Microsoft Learn. Says that starting with SQL Server 2025, MSOLEDBSQL uses OLE DB Driver 19, which needs a valid CA-signed certificate or encrypt=optional, that the self-signed certificate isn’t accepted with encrypt=mandatory, and that trace flag 17600 keeps version 18 behaviour. Also lists Msg 7416 for logins outside sysadmin, with User ID in the provider string as the resolution. ↩
MSOLEDBSQL Major Version Differences - Microsoft Learn. Shows that the Encrypt default is Mandatory in version 19 and no in version 18, that certificate validation is always evaluated when encryption occurs in version 19, and that Optional means unencrypted unless the server requires it. ↩
TDS 8.0 - Microsoft Learn. Describes TrustServerCertificate=true as not recommended because the driver then doesn’t validate the server TLS/SSL certificate, and states that strict encryption doesn’t allow TrustServerCertificate=true, in order to prevent a man-in-the-middle attack. ↩
sp_serveroption (Transact-SQL) - Microsoft Learn. Defines the data access and rpc out options and the ALTER ANY LINKED SERVER permission. ↩
STRING_AGG (Transact-SQL) - Microsoft Learn. Applies to SQL Server 2017 (14.x) and later versions. ↩

Tags: auditing, linked-servers, permissions, security, SQL Server, SQL Server 2025

Auditing Linked Server Security: Who Connects as Whom

Where the Settings Live

The Four Mapping Behaviours

The Test

What the Test Login Could Do

Why a Fixed sysadmin Login Is the One to Find

RPC OUT and Data Access Are Separate Switches

What a Low-Privilege Login Can See

The Audit Query

A Lab Detail: Msg 7416 and the Provider String

Limits of This Test

What I’d Change After the Audit

References

Related

Let us know your thoughts on this post Click here to cancel reply.

Search

Categories

Pages

Meta

Auditing Linked Server Security: Who Connects as Whom

Where the Settings Live

The Four Mapping Behaviours

The Test

What the Test Login Could Do

Why a Fixed sysadmin Login Is the One to Find

RPC OUT and Data Access Are Separate Switches

What a Low-Privilege Login Can See

The Audit Query

A Lab Detail: Msg 7416 and the Provider String

Limits of This Test

What I’d Change After the Audit

References

Share this:

Related

Let us know your thoughts on this post Click here to cancel reply.

Search

Categories

Pages

Meta