SQL SERVER – Loopback Linked Servers and OPENQUERY with Current Drivers

A LoopBack Linked Server connects back to the same SQL Server instance. I review its provider, identity and transaction restrictions first.

A reader wanted OPENQUERY to call a local stored procedure. My earlier example used OPENROWSET. The new question asked how to define a linked server pointing back to the same instance. That connection needs its own configuration review.

-- Administrator template, not a universal one-line setup.
-- Replace with the exact instance endpoint and certificate-matching name.
-- EXEC master.dbo.sp_addlinkedserver
--     @server = N'loopback', @srvproduct = N'',
--     @provider = N'MSOLEDBSQL',
--     @datasrc = N'YourServer\YourInstance',
--     @provstr = N'Encrypt=Mandatory';
-- EXEC master.dbo.sp_serveroption @server = N'loopback',
--     @optname = N'data access', @optvalue = N'true';
SELECT name, provider, data_source, is_data_access_enabled
FROM sys.servers WHERE is_linked = 1;

The old article used SQLNCLI. That provider is deprecated, so new configurations should use a supported Microsoft OLE DB Driver. SQL Server 2025 uses Driver 19 for MSOLEDBSQL and has changed encryption requirements. Verify the installed provider, endpoint and certificate configuration for the exact version.

Creating the server definition is only part of the setup. Review the login mappings created by sp_addlinkedserver and restrict access to the intended principals. The caller still needs appropriate permissions on the returned-to instance; data access does not grant those permissions.

Loopback connections have special transaction limitations and are not a general workaround for an application design problem. Test the exact procedure, returned result shape and transaction context in a lab before relying on OPENQUERY. The read-only catalog query above can inspect an existing definition without creating one.

Reference: Linked servers and current driver requirements.

Related reading

• How to Insert Results of Stored Procedure into a Temporary Table?

A loopback connection is not the original local session, it is a separate connection with its own requirements.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.