SQL Server's authentication mode is a server-level configuration stored in the Windows Registry. To change this setting programmatically using Transact-SQL, the extended stored procedure xpinstanceregwrite is used. This procedure allows writing values to the SQL Server-specific keys in the registry. To enable mixed mode authentication, the LoginMode value under the HKEYLOCALMACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\...\MSSQLServer key must be set to 2. xpinstanceregwrite is the correct tool among the options to perform this specific registry modification. A restart of the SQL Server service is required for the change to take effect.

A. spaddremotelogin: This is a deprecated procedure used for managing remote server logins, which is unrelated to the instance's primary authentication mode.

B. xpgrantlogin: This procedure is used to grant a Windows user or group access to SQL Server, which is a login management task, not a server configuration change.

C. spchangeuserslogin: This is a deprecated procedure used to remap database users to server logins, typically for resolving orphaned users. It does not alter the server authentication mode.

1. Microsoft. (2023). Change server authentication mode. Microsoft Learn.

Section: "To change the authentication mode using Transact-SQL"

Content: This official document explicitly provides a T-SQL script that uses EXEC xpinstanceregwrite ... N'LoginMode'

REGDWORD

2 to change the server authentication to mixed mode.

2. Microsoft. (2023). xpinstanceregwrite (Transact-SQL). Microsoft Learn.

Section: "Syntax" and "Remarks"

Content: This document describes the function of xpinstanceregwrite as a procedure that writes a value to the registry for a given instance of SQL Server

confirming its suitability for the task described in the question.