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 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 extension 1.1.2717.190
(June 2024) or later will have least privileged configuration automatically applied. This application will happen gradually.
To prevent automatic application of least privilege, block extension upgrades to version 1.1.2717.190
or later.
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 configuration with least privilege is not currently supported on Linux.
Other requirements, as listed in Prerequisites - SQL Server enabled by Azure Arc still apply. Including the SQL Server service account must be a member of the sysadmin fixed server role on each SQL Server instance.
Tools
To complete the steps in this article, you need the following tools:
- Azure CLI
arcdata
Azure CLI extension version1.5.9
or later- Azure extension for SQL server version
1.1.2504.99
or later
Enable least privilege
Log in with Azure CLI.
az login
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
Enable least privilege with Azure CLI.
To enable least privilege, set the
LeastPrivilege
feature flag totrue
. 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 namedmyrg
: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:
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
.Open task scheduler in the server and check that an event driven task with name
SqlServerExtensionPermissionProvider
is created underMicrosoft\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 underMicrosoft\SqlServerExtension
.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
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");