Prerequisites for monitoring Azure SQL Database

This section provides information about the prerequisites for monitoring SQL Azure database and Azure Elastic Pools.

This section covers the following key areas:

Permissions for monitoring Azure SQL database

Following are the permissions required to monitor Azure SQL database:

  • CREATE USER—the lowest permission level, allowing access to each database solely for reading its metadata.

    The CREATE USER permission is not included in the script, as this command is not supported within a batch in Azure SQL.

  • VIEW DATABASE STATE—required by all tiers (General Purpose, Hyperscale, and Business Critical) on the vCore model.

    On the DTU model:
    Basic, Standard 0, and Standard 1 tiers require server admin permission or an Azure Active Directory admin account (VIEW SERVER STATE permission).
    Standard 2 and above tiers as well as Premium tiers require VIEW DATABASE STATE permission.

Permissions for monitoring Elastic Pools

The following permissions are required at the elastic pool level:

  • Map Foglight Login to a database user on the master database.

Running the Grant Permissions Script

The file used for manually granting permissions, DB_Azure_Grant_Permission_Script.sql, can be obtained by clicking on the ‘View script’ link located under the Instances table. This option is accessible through either of the following methods:

  • When running the Monitor Azure SQL Database wizard, the script link is in the Insufficient Privileges dialog screen.
  • In the Cartridges - Components for Download screen.

    Running this file requires one of the following server roles:
    Server admin
    Active Directory admin
    Member of the db_owner

To manually run the Grant Permissions script:

  1. Run the CREATE USER command on a database to be monitored. Upon successful execution of this command, the login is transformed into a user within the designated database, thereby granting the ability to read the metadata of the database.
  2. Open the DBSS_Azure_Permissions_User_Databases.sql file in SQL Server Management Studio (SSMS).
  3. Find the Select @LoginName = ? section at the beginning of this file.
  4. Replace the question mark with the login name to which the requested permissions are to be assigned.
  5. Execute the script

After setting the pre-requisites, you can install and monitor the Azure SQL server instance. For more information, refer to Installing and Monitoring a Single Azure SQL Server Instance.