Create a linked server in SQL 2025 connecting to a SQL 2017 Instance

Replace SQLNCLI11 with MSOLEDBSQL and explicitly set encryption when recreating the linked server on SQL Server 2025.

SQL Server 2025 uses Microsoft OLE DB Driver 19 for MSOLEDBSQL. Unlike the old provider, it requires an Encrypt value; without one, it defaults to mandatory encryption and the connection fails unless SQL Server 2017 presents a valid trusted certificate.^1^

Create the linked server

For production, use Encrypt=Mandatory and configure the SQL Server 2017 instance with a CA-signed certificate whose name matches the server FQDN:

USE master;
GO

EXEC sys.sp_addlinkedserver
    @server     = N'SQL2017_LINK',
    @srvproduct = N'',
    @provider   = N'MSOLEDBSQL',
    @datasrc    = N'SQL2017Server\InstanceName',
    @provstr    = N'Encrypt=Mandatory;';
GO

Encrypt=Mandatory requires a trusted CA-signed certificate; a self-signed certificate is not accepted by default.^2^

If the SQL Server 2017 instance does not currently have a trusted certificate, use this compatibility configuration while planning the certificate deployment:

EXEC sys.sp_addlinkedserver
    @server     = N'SQL2017_LINK',
    @srvproduct = N'',
    @provider   = N'MSOLEDBSQL',
    @datasrc    = N'SQL2017Server\InstanceName',
    @provstr    = N'Encrypt=Optional;';
GO

Encrypt=Optional does not require a certificate, but the linked-server traffic is not encrypted. Avoid TrustServerCertificate=Yes as a production workaround because it disables certificate validation and also requires the corresponding client registry setting.^1^

Configure authentication

Choose one mapping model.

Windows credential delegation:

EXEC sys.sp_addlinkedsrvlogin
    @rmtsrvname = N'SQL2017_LINK',
    @locallogin = NULL,
    @useself     = N'True';
GO

This passes the caller’s Windows security context. For remote clients, Kerberos delegation must be configured; otherwise, the common “double-hop” authentication issue can prevent access.

Dedicated SQL Server login:

EXEC sys.sp_addlinkedsrvlogin
    @rmtsrvname = N'SQL2017_LINK',
    @locallogin = NULL,
    @useself     = N'False',
    @rmtuser     = N'LinkedServerLogin',
    @rmtpassword = N'<strong-password>';
GO

With @locallogin = NULL, the mapping applies to all local logins. For tighter security, replace NULL with a specific local login and grant the remote login only the required permissions.^3^

Be aware that scripted SQL credentials can be exposed in saved scripts, backups, or logs; do not use an administrator account.

Test the migration

Run the test under the same local login or SQL Agent execution context that will use the linked server:

EXEC sys.sp_testlinkedserver
    @servername = N'SQL2017_LINK';
GO

SELECT name
FROM [SQL2017_LINK].[master].[sys].[databases];
GO

sp_testlinkedserver raises the underlying connection or authentication error when the test fails, and its caller must have an applicable login mapping.^4^

SSMS alternative

In SSMS:

1. Connect to SQL Server 2025.
2. Open Server Objects → Linked Servers.
3. Right-click Linked Servers → New Linked Server.
4. Select Other data source.
5. Set:
   • Provider: Microsoft OLE DB Driver for SQL Server
   • Product name: leave blank
   • Data source: SQL2017Server\InstanceName
   • Provider string: Encrypt=Mandatory; or Encrypt=Optional;
6. Configure the mapping on the Security page.
7. Right-click the resulting linked server and select Test Connection.

Do not reinstall or continue using SQLNCLI11: SQL Server Native Client was removed from SQL Server 2022 and later and is not recommended for new development. MSOLEDBSQL19/MSOLEDBSQL is the supported current provider family.^5^

References

1. Linked servers (Database Engine)
2. sys.sp_addlinkedserver (Transact-SQL)
3. sys.sp_addlinkedsrvlogin (Transact-SQL)
4. sp_testlinkedserver (Transact-SQL)
5. Microsoft OLE DB Driver for SQL Server