Edytuj

Udostępnij za pośrednictwem

Operate SQL Server enabled by Azure Arc with least privilege

  • Artykuł

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 does not 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

Existing servers with the extension from the November 2024 release or later will automatically have least privileged configuration applied. This application will happen gradually.

To prevent automatic application of least privilege, block extension upgrades to the November 2024 release.

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, disable least privilege configuration, or uninstall the Azure extension for SQL Server. Revocation ensures that no permissions remain after they're no longer required.

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
  • SQL Server 2012 or later
  • 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 is not currently supported on Linux.

Other requirements, as listed in Prerequisites - SQL Server enabled by Azure Arc 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 temporarily runs as NT AUTHORITY\SYSTEM when:

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

Deployer.exe impersonates the SQL Server service account to connect to SQL Server and add or remove permissions in server and database roles depending on which features are enabled or disabled to ensure that the Azure extension for SQL Server uses the least privileges required. To modify these permissions, the SQL Server service account must be a member of the sysadmin server role.

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

  1. Temporarily add the SQL Server service account to the sysadmin 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 role.

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

Tools

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

Enable least privilege

  1. Log 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 under the 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

    Prior to the July, 2024 release, SqlServerExtensionPermissionProvider is a scheduled task. It runs hourly.

    Open task scheduler in the server and check that a scheduled task with name SqlServerExtensionPermissionProvider is created under Microsoft\SqlServerExtension.

  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");

    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");

Related content