تحرير

Operate SQL Server enabled by Azure Arc with least privilege

Applies to: SQL Server

The information security principle of least privilege asserts that accounts and applications only have access to the data and operations they require. With SQL Server enabled by Azure Arc, you can run the agent extension service with least privilege. This article explains how to run the agent extension service with least privilege.

To optionally configure the service to run with least privilege, follow the steps in this article. Currently, the service doesn't automatically run with least privilege.

Configure Windows service accounts and permissions for Azure Extension for SQL Server describes the least privilege permissions for the agent extension service.

Note

Currently, least privileged configuration is not applied by default.

Existing servers with extension version 1.1.2859.223 or greater will eventually have the least privileged configuration applied. This extension was released in November, 2024. To prevent the automatic application of least privilege, block extension upgrades after 1.1.2859.223.

After you configure the agent extension service to run with least privilege, it uses the NT SERVICE\SqlServerExtension service account.

The NT SERVICE\SqlServerExtension account is a local Windows service account:

  • Created and managed by the Azure Extension for SQL Server when least privilege option is enabled.
  • Granted the minimum required permissions and privileges to run the Azure Extension for SQL Server service on the Windows operating system. It only has access to folders and directories used for reading and storing configuration or writing logs.
  • Granted permission to connect and query in SQL Server with a new login specifically for that service account that has the minimum permissions required. Minimum permissions depend on the enabled features.
  • Updated when permissions are no longer necessary. For example, permissions are revoked when you disable a feature. Revocation ensures that no permissions remain after they're no longer required.
  • NT SERVICE\SqlServerExtension is removed when Azure Extension for SQL Server is uninstalled or when least privilege configuration is disabled.

Prerequisites

This section identifies the system requirements and tools you need to complete the example in this article.

System requirements

The configuration with least privilege requires:

  • Windows Server 2012 or later versions.
  • SQL Server 2012 or later versions.
  • The SQL Server service account must be a member of the sysadmin fixed server role.
  • All databases must be online and updateable.

The configuration with least privilege isn't currently supported on Linux.

Other requirements, as listed in Prerequisites, still apply.

SQL Server service account

By default, the SQL Server service account is a member of the sysadmin fixed server role.

As listed in prerequisites, the SQL Server service account must be a member of the sysadmin fixed server role on each SQL Server instance. The Azure Extension for SQL Server has a process called Deployer.exe that runs as NT AUTHORITY\SYSTEM when:

  • Features are enabled or disabled
  • SQL Server instances are added or removed

Note

Deployer.exe impersonates the SQL Server service account when it connects to SQL Server. Once connected, it adds or removes permissions in the server and database roles depending on which features are enabled or disabled. This process ensures that the Azure Extension for SQL Server uses the least privileges required. Therefore, the SQL Server service account must be a member of the sysadmin fixed server role.

If you want to manage this process with more control, such that the SQL Server service account isn't a member of the sysadmin fixed server role all the time, follow these steps:

  1. Temporarily add the SQL Server service account to the sysadmin fixed server role.
  2. Allow Deployer.exe to run at least once so that the permissions are set.
  3. Remove the SQL Server service account from the sysadmin fixed server role.

Repeat this procedure any time that features are enabled or disabled or SQL Server instances are added to allow Deployer.exe to grant the least privileges required.

Important

The Azure Extension for SQL Server Deployer.exe requires NT AUTHORITY\SYSTEM to be able to connect to SQL Server, with CONNECT SQL permission, in both standard and least privilege modes. This requirement exists because Deployer.exe always runs under the LocalSystem account, regardless of which service account the extension uses after provisioning.

If NT AUTHORITY\SYSTEM can't connect to SQL Server, Deployer.exe can't create the NT SERVICE\SqlServerExtension login or grant the required permissions. Before you enable least privilege mode, verify that NT AUTHORITY\SYSTEM has an active SQL Server login with CONNECT SQL permission. See Prerequisites for verification steps.

Tools

To complete the steps in this article, you need the following tools:

Enable least privilege

  1. Sign in with Azure CLI.

    az login

  2. Verify the arcdata extension version.

    az extension list -o table

    If the results include a supported version of arcdata, skip to the next step.

    If necessary, install or update the arcdata Azure CLI extension.

    To install the extension:

    az extension add --name arcdata

    To update the extension:

    az extension update --name arcdata

  3. Enable least privilege with Azure CLI.

    To enable least privilege, set the LeastPrivilege feature flag to true. To complete this task, run the following command with updated values for the <resource-group> and <machine-name>.

    az sql server-arc extension feature-flag set --name LeastPrivilege --enable true --resource-group <resource-group> --machine-name <machine-name>

    For example, the following command enables least privilege for a server named myserver in a resource group named myrg:

    az sql server-arc extension feature-flag set --name LeastPrivilege --enable true --resource-group myrg --machine-name myserver

Verify least privilege configuration

To verify that your SQL Server enabled by Azure Arc is configured to run with least privilege:

  1. In the Windows services, locate Microsoft SQL Server Extension Service service. Verify that the service is running as the service account NT SERVICE\SqlServerExtension.

  2. Open task scheduler in the server and check that an event-driven task with name SqlServerExtensionPermissionProvider is created under Microsoft\SqlServerExtension.

    Note

    Before the July 2024 release, SqlServerExtensionPermissionProvider was a scheduled task that ran hourly.

  3. Open SQL Server Management Studio and check the login named NT SERVICE\SqlServerExtension. Verify that the account is assigned these permissions:

    • Connect SQL
    • View Database State
    • View Server State

  4. Validate the permissions with the following queries:

    To verify server-level permissions, run the following query:

    EXECUTE AS LOGIN = 'NT SERVICE\SqlServerExtension';

SELECT *
FROM fn_my_permissions(NULL, 'SERVER');

REVERT;

    To verify database-level permissions, replace <database name> with the name of one of your databases, and run the following query:

    EXECUTE AS LOGIN = 'NT SERVICE\SqlServerExtension';

USE <database name>;
SELECT * FROM fn_my_permissions(NULL, 'database');

REVERT;

Related content

  • Last updated on