I'm currently working on setting up a distribution database using the sp_adddistributiondb stored procedure in SQL Server. I've come across the @Security_mode parameter and I'm trying to understand its implications.
If I set @Security_mode = 1, which indicates Windows Authentication Mode, does it mean that SQL scripts like instdist.sql will be executed under the security context of the SQL instance's service account that's acting as the Distributor? Or does setting @Security_mode = 1 imply that the Distributor will use integrated (Windows) authentication when it needs to establish a connection to the Publisher?
I appreciate any insights or references that can help clarify these points. Thanks in advance for your help!
Here's the snippet of the code for reference:
EXEC sp_adddistributiondb
@database = N'distribution',
@data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Data',
@log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Data',
@log_file_size = 256,
@min_distretention = 0,
@max_distretention = 72,
@history_retention = 48,
@security_mode = 1
