Replication Security Parameters Changed in SQL Server 2022 and 2025
I don’t like finding authentication changes while I am already in the middle of an upgrade. Replication setup scripts can keep working for years, but upgrades can still expose changes.
This post is part of the Undocumented Changes in SQL Server 2022 and 2025 series. The contents page lists every part in order.
The changed-procedure posts for SQL Server 2022 and SQL Server 2025 both had replication changes in the background. This post focuses on security-related parameters added to replication stored procedures between SQL Server 2019, SQL Server 2022, and SQL Server 2025.
I stripped comments from each definition file first. Then I parsed the parameter list between CREATE PROCEDURE and the first AS. The input files came from the same Dedicated Admin Connection (DAC) inventory as the first post in the series. DAC means a special administrator connection that can return system module definitions that a normal connection returns as NULL.
This is not a replication configuration test. I did not create a publication, configure a distributor, run a replication setup procedure, or change SQL Server settings. The findings below come from definition text and Microsoft Learn pages rather than runtime behaviour.
The count
I found 31 added security-related procedure and parameter pairs. Nineteen appeared from SQL Server 2019 to SQL Server 2022. Twelve appeared from SQL Server 2022 to SQL Server 2025.
The first group is mostly job and subscriber authentication. The second group is mostly Transport Layer Security (TLS), the protocol that encrypts network traffic, for distributor connections.
| Procedure | New parameter | Definition default | Documented |
|---|---|---|---|
sys.sp_MSadd_distribution_agent |
@job_security_mode |
NULL |
no |
sys.sp_MSadd_logreader_agent |
@job_security_mode |
NULL |
no |
sys.sp_MSadd_snapshot_agent |
@job_security_mode |
NULL |
no |
sys.sp_MSchange_logreader_agent_properties |
@job_security_mode |
NULL |
yes |
sys.sp_MSchange_snapshot_agent_properties |
@job_security_mode |
NULL |
yes |
sys.sp_MSrepl_testadminconnection |
@multi_subnet_failover |
0 |
no |
sys.sp_MSrepl_testadminconnection |
@encrypt_distributor_connection |
NULL |
no |
sys.sp_MSrepl_testadminconnection |
@trust_distributor_certificate |
NULL |
no |
sys.sp_MSrepl_testadminconnection |
@host_name_in_distributor_certificate |
NULL |
no |
sys.sp_adddistributor |
@security_mode |
NULL |
yes |
sys.sp_adddistributor |
@multi_subnet_failover |
0 |
yes |
sys.sp_adddistributor |
@encrypt_distributor_connection |
NULL |
yes |
sys.sp_adddistributor |
@trust_distributor_certificate |
NULL |
yes |
sys.sp_adddistributor |
@host_name_in_distributor_certificate |
NULL |
yes |
sys.sp_addlogreader_agent |
@job_security_mode |
NULL |
yes |
sys.sp_addpublication_snapshot |
@job_security_mode |
NULL |
yes |
sys.sp_addpullsubscription_agent |
@job_security_mode |
NULL |
yes |
sys.sp_addpushsubscription_agent |
@job_security_mode |
NULL |
yes |
sys.sp_addsubscription |
@subscriber_security_mode |
NULL |
yes |
sys.sp_addsubscription |
@subscriber_login |
NULL |
yes |
sys.sp_addsubscription |
@subscriber_password |
NULL |
yes |
sys.sp_changelogreader_agent |
@job_security_mode |
NULL |
yes |
sys.sp_changepublication_snapshot |
@job_security_mode |
NULL |
yes |
sys.sp_get_redirected_publisher |
@multi_subnet_failover |
0 |
yes |
sys.sp_get_redirected_publisher |
@encrypt_distributor_connection |
NULL |
yes |
sys.sp_get_redirected_publisher |
@trust_distributor_certificate |
NULL |
yes |
sys.sp_get_redirected_publisher |
@host_name_in_distributor_certificate |
NULL |
yes |
sys.sp_validate_redirected_publisher |
@multi_subnet_failover |
0 |
yes |
sys.sp_validate_redirected_publisher |
@encrypt_distributor_connection |
NULL |
yes |
sys.sp_validate_redirected_publisher |
@trust_distributor_certificate |
NULL |
yes |
sys.sp_validate_redirected_publisher |
@host_name_in_distributor_certificate |
NULL |
yes |
The sp_MS* names identify the procedures I would treat most cautiously. They are system procedures and some have Learn pages, but I would not treat the undocumented ones as public scripting contracts. The public procedures are the ones I would review in an upgrade script first: sp_addsubscription, sp_addlogreader_agent, sp_addpublication_snapshot, sp_addpullsubscription_agent, sp_addpushsubscription_agent, sp_changelogreader_agent, sp_changepublication_snapshot, sp_adddistributor, sp_get_redirected_publisher, and sp_validate_redirected_publisher.
SQL Server 2022 added job and subscriber security parameters
SQL Server 2022 added @job_security_mode to eleven replication agent procedures in my definition diff. The public wrapper procedures pass the value through to the lower-level replication procedures. The internal add-agent definitions also use it when they choose the security mode stored for the agent connection.
The Learn pages I checked document @job_security_mode for the public add and change procedures. On the pages for sp_addlogreader_agent, sp_addpublication_snapshot, sp_addpullsubscription_agent, sp_addpushsubscription_agent, sp_changelogreader_agent, and sp_changepublication_snapshot, Microsoft marks the parameter as internal-only, or documents it in a procedure page that otherwise explains the agent security context.[1]
The SQL Server 2022 definitions also add three subscriber parameters to sp_addsubscription. They are @subscriber_security_mode, @subscriber_login, and @subscriber_password. The procedure does not use those values locally in the wrapper. It passes them to sys.sp_MSrepl_addsubscription with the rest of the subscription arguments.
The Learn page for sp_addsubscription lists those three subscriber parameters, but each one is marked internal-only.[2] The parameters are present in the signature and the page, but Microsoft does not present them as supported input for normal scripts.
sp_adddistributor also gained @security_mode between 2019 and 2022. In the 2025 definition, a value of 5 takes a different linked-server branch: the provider string starts with Authentication=ActiveDirectoryMSI, and the password requirement is skipped. I am not expanding MSI beyond the product term used by the definition, because I did not test that branch.
The Learn page for sp_adddistributor lists @security_mode, but marks it internal-only.[3] The agent procedure pages also have the older warning that remote distributor setup can send parameter values, including job login and password values, to the distributor as plain text unless the connection is encrypted.[4]
MultiSubnetFailover arrived before the TLS parameters
Four procedures gained @multi_subnet_failover in the 2022 comparison: sp_adddistributor, sp_MSrepl_testadminconnection, sp_get_redirected_publisher, and sp_validate_redirected_publisher. The definition default is 0 for all four.
In sp_adddistributor and sp_MSrepl_testadminconnection, a value of 1 adds MultiSubnetFailover=Yes to the provider string for the linked server used to talk to the distributor. In sp_get_redirected_publisher, the value is passed to sp_validate_redirected_publisher. In sp_validate_redirected_publisher, the value is passed again to sys.sp_hadr_create_linked_server.
The Learn pages document @multi_subnet_failover for sp_get_redirected_publisher and sp_validate_redirected_publisher. They say the parameter applies to SQL Server 2022 CU10 and later. The sp_adddistributor page I fetched documents the parameter as applying to SQL Server 2025 CU8 and later, even though my SQL Server 2022 CU25 definition has the parameter.[5] I did not find a Learn page for sp_MSrepl_testadminconnection.
SQL Server 2025 added distributor encryption parameters
SQL Server 2025 added three distributor certificate and encryption parameters to four procedures: sp_adddistributor, sp_MSrepl_testadminconnection, sp_get_redirected_publisher, and sp_validate_redirected_publisher.
Those parameters are @encrypt_distributor_connection, @trust_distributor_certificate, and @host_name_in_distributor_certificate. The definitions give all three a default of NULL. The parameter comments in the definition list values such as no, yes, true, false, Optional, Mandatory, and Strict for encryption, and no or yes for certificate trust.
In two of the procedures, these parameters are used only when a feature switch is enabled. sp_adddistributor and sp_MSrepl_testadminconnection call sys.sp_MSrepl_concat_encryption_params only when a feature switch named ConfigureEncryptionDuringTranReplLinkedServerCreation is enabled. If the helper returns a non-empty connection string, the procedure appends it to the linked server provider string. sp_get_redirected_publisher passes the three values to sp_validate_redirected_publisher, and sp_validate_redirected_publisher passes them to sys.sp_hadr_create_linked_server.
Microsoft documents the three parameters differently depending on the procedure. The sp_adddistributor page describes them as SQL Server 2025 and later parameters. It says @encrypt_distributor_connection maps to the OLE DB provider Encrypt property, @trust_distributor_certificate maps to TrustServerCertificate, and @host_name_in_distributor_certificate supplies the certificate host name when it differs from the distributor name.[6]
The same live page says @encrypt_distributor_connection and @trust_distributor_certificate can’t be NULL, even though the SQL Server 2025 CU9 definitions default both to NULL. I did not run the procedures, so I am treating this as a documentation-and-definition difference, not as a runtime finding.
For sp_get_redirected_publisher and sp_validate_redirected_publisher, Microsoft lists the same three parameters, but marks them internal-only.[7] I did not find a Learn page for sp_MSrepl_testadminconnection.
Why the 2025 parameters exist
The SQL Server 2025 documentation gives the context. Microsoft says SQL Server 2025 introduces TDS 8.0 support for replication, including TLS 1.3 for replication agents and default encryption for inter-instance linked server communication in replication topologies.[8]
The breaking-changes page is more specific for remote distributors. It says replication components might fail after an upgrade if the instance is a replication publisher, has a remote distributor, and is not configured with a trusted certificate. It also says remote distributor communication uses a linked server, and the OLE DB 19 provider secure default requires TrustServerCertificate=False.[9]
That page gives two paths. The preferred path is to configure the distributor SQL Server instance with a trusted certificate. The less secure option is to override the default and set trust_distributor_certificate=yes, either through sp_changedistributor_property after upgrade or through sp_adddistributor when adding a remote distributor.[10]
The agent pages describe the same TLS behaviour. The Log Reader Agent and Distribution Agent pages document -EncryptionLevel values from 0 through 4. Values 3 and 4 relate to TLS 1.3 for SQL Server 2025 and Azure SQL Managed Instance connection paths, and value 4 requires installing the certificate on SQL Server hosts that receive those connections.[11]
What I would review before an upgrade
I would review any replication setup script that hard-codes procedure signatures, especially wrapper scripts that generate calls from metadata. A procedure can add a parameter with a default and still leave old calls working, but a script that validates parameter lists can fail before SQL Server gets the call.
For SQL Server 2022, I would look for scripts that create or change replication agent jobs. The new @job_security_mode and subscriber security parameters are mostly documented as internal-only, but they are present in public procedure signatures.
For SQL Server 2025, I would start with remote distributor topologies. The documentation says the linked-server encryption default can affect replication components after an upgrade. These parameters appear to control that connection path. I would not set trust_distributor_certificate=yes first. Microsoft calls that override less secure than configuring a trusted certificate.
I did not configure replication for this post. That was deliberate. The definition diff identifies scripts to review. The Learn pages also indicate that remote distributors need a certificate plan for SQL Server 2025.
Have you had one of these replication procedure changes affect an upgrade script? Tell me on Bluesky or LinkedIn, and I’ll update the notes.
References
- sys.sp_addlogreader_agent (Transact-SQL), sys.sp_addpublication_snapshot (Transact-SQL), sys.sp_addpullsubscription_agent (Transact-SQL), sys.sp_addpushsubscription_agent (Transact-SQL), sys.sp_changelogreader_agent (Transact-SQL), sys.sp_changepublication_snapshot (Transact-SQL), sys.sp_MSchange_logreader_agent_properties (Transact-SQL) and sys.sp_MSchange_snapshot_agent_properties (Transact-SQL) - Microsoft Learn. Procedure pages checked for
@job_security_mode, agent security wording, and internal-only parameter wording. ↩ - sys.sp_addsubscription (Transact-SQL) - Microsoft Learn. Lists
@subscriber_security_mode,@subscriber_login, and@subscriber_password, and marks them internal-only. ↩ - sys.sp_adddistributor (Transact-SQL) - Microsoft Learn. Lists
@security_modeand marks it internal-only. ↩ - sys.sp_addlogreader_agent (Transact-SQL), sys.sp_addpublication_snapshot (Transact-SQL), and sys.sp_addpushsubscription_agent (Transact-SQL) - Microsoft Learn. These pages warn that parameter values, including job login and password values, can be sent to a remote distributor as plain text unless the connection is encrypted. ↩
- sys.sp_adddistributor (Transact-SQL), sys.sp_get_redirected_publisher (Transact-SQL), and sys.sp_validate_redirected_publisher (Transact-SQL) - Microsoft Learn. Documents
@multi_subnet_failoverand the applicable version notes on the fetched pages. ↩ - sys.sp_adddistributor (Transact-SQL) - Microsoft Learn. Documents SQL Server 2025 distributor encryption, certificate trust, and certificate host-name parameters. ↩
- sys.sp_get_redirected_publisher (Transact-SQL) and sys.sp_validate_redirected_publisher (Transact-SQL) - Microsoft Learn. Lists the distributor encryption and certificate parameters and marks them internal-only. ↩
- Transactional Replication, Snapshot Replication, and Merge Replication - Microsoft Learn. SQL Server 2025 TLS 1.3 sections for replication and default encryption for inter-instance linked server communication. ↩
- Database Engine: Breaking Changes - SQL Server 2025 - Microsoft Learn. Describes replication component failures after upgrade with remote distributors and untrusted certificates. ↩
- Database Engine: Breaking Changes - SQL Server 2025 - Microsoft Learn. Gives the trusted-certificate path and the less secure
trust_distributor_certificate=yesoverride. ↩ - Replication Log Reader Agent and Replication Distribution Agent - Microsoft Learn. Documents
-EncryptionLevelvalues, TLS 1.3 notes, and certificate requirements for value4. ↩