SCOM Management Pack for Azure SQL Managed Instance

Tyson Paul0 commentsAzureAzure ADAzure SQLManagement PacksSCOM 2016




This won’t be an exhaustive tutorial on all facets of the MP, only my notes on my experiences with manual vs automatic monitoring setup of the Azure SQL MI MP ( https://www.microsoft.com/en-US/download/details.aspx?id=101203 ). It will mostly be screenshots and notes of how I accomplished setup for both approaches specifically for my lab.

Basic Requirements

  • Your Azure SQL Managed Instance is already setup (in my case, with a private endpoint).
  • You can login to your MI with sysadmin rights to configure the instance for a “least-privilege” scenario.
  • You have an existing SCOM mgmt group and your SCOM mgmt server(s) can access your MI and Azure URLs below. The MI will be monitored by a management server resource pool, “Azure SQL MI Monitoring Pool“. Regardless of where your SCOM mgmt servers are located (Azure, on prem, your imagination) they will need to be able to reach the MI and the following URLs:
    https://login.windows.net
    https://management.azure.com/
    https://graph.windows.net
    https://database.windows.net/

Note: The configuration wizard provides only two options: Public or Private endpoint although apparently you can select Private which will still discover a vnet local endpoint.


Manual Setup (with SQL auth account, not AAD)

Specific Requirements

  • Your SQL login exists on the SQL instance. In my example, I used ‘svc-SQLMI1-SCOM1’.
  • Your basic SCOM account credential exists in SCOM. (You can also create it within the config wizard shown below)
SCOM account (Basic) exists.


Manual configuration with SQL Authentication

Select your account credential or create one here. It’s just a basic credential type with username and password. Input your SQL MI address.

Notice: In my experience with the Manual configuration approach, the only supported credential type is “SQL Authentication”, not SPN.

-This author who wasted a ton of time proving this


Discovery may take a few minutes. If your SQL MI is up/running and reachable, you should have no problems. Keep an eye on the OpsMan event log for any possible issues.

Note: Throughout my extensive testing I’ve seen a TON of inconsistent behavior with the discovery; random connectivity errors that don’t make much sense. I’ve had to clear cache, remove existing Automatic config items, force discovery purge, force snapshot synch, then create a fresh Automatic Azure SQL MI config item with the Add Monitoring Wizard, then wait for discovery (from 2-10 mins).
Also, it seems that when instance name filtering is used, any instance names that are rejected due to matching the filter mask will result in an event log Error (EventID: 4221), even though it’s not really an error.

That’s it. It’s that easy.

Notice: In my experience with the Manual configuration approach, the only supported credential type is “SQL Authentication”, not SPN. When attempting to use a manually created SPN and SCOM account credential, the following error occurs (see screenshot). I even tried an “auto-created” SCOM credential (for the SPN) with the SQL instance (which I had already successfully discovered and monitored during previous testing with the Automatic method) and the Manual wizard STILL failed. I even tried to provide a connection string within the wizard (which indicates that it can parse the connection string for the credentials) but apparently it cannot support Authentication=Active Directory Service Principal;.
There are a number of findings that support this claim:
1) The examples provided in the MP guide only demonstrate basic connection string keywords:
Server=<ServerAddress>;Database=<DatabaseName>;User Id=<UserName>;Password=<Password>;
2) The MP guide indicates that “You can get a connection string for a managed instance using the Azure portal.” The SQL MI in the Azure portal only provides 3 types of connection strings, in 4 different languages (ADO.NET, JDBC, ODBC, PHP), none of which include Authentication=Active Directory Service Principal;.
3) I’ve tried every other reasonable and clever approach to use the SPN credential. It would be nice if the documentation weren’t so vague and minimal.

Example of failed verification when using SPN with Manual configuration.



Automatic Setup (with SPN)

Specific Requirements (assumptions for this scenario)

  • You intend to use a service principal (app registration) and you have Azure Global Admin creds to use for the service principal creation (ahead of time or within the SCOM setup wizard w/Azure login prompt)
  • You have configured the SQL instance with an appropriate (app registration Name) login with the necessary permissions.
    See Least-privilege account configuration on the SQL MI below.


Auto-Create SPN Example

NOTE: This management pack uses a SPN in two ways:

  1. Used to automatically discover all Azure SQL Managed Instances for the tenant
  2. Used to authenticate to the SQL instance(s) for monitoring.

NOTE: In this “SPN Configuration” step below, a SPN is mandatory in this “Automatic Setup” approach to discover the SQL Instances (automatically). However, you can create the SPN manually ahead of this if you want. The app registration need only to exist with a client secret; no permissions are required. If the SPN already exists at this point, use the “Use Existing Run As Profile” which will prompt you to select an existing Basic SCOM credential. Details on how to manually create the SCOM credential for a SPN are shown further down in this article.

For the “Auto-Create SPN” example below, the Console server must have access to authenticate to Azure to approve the SPN creation.

Once the SPN (app registration) is auto-created, the values appear in the next screen. The AppID, TenantID, and Secret are used to auto-create a SCOM basic credential (shown further down).

This is what the app registration looks like. There’s not much to it.

As you can see below, there are zero permissions granted. None are needed. The SQL permissions are added to the SQL instance.

Notice: ID tokens is enabled.


Everything up to this point has been for discovery. Now we must specify which credentials to use for the monitoring of the SQL managed instance. In this example, we choose to use the SPN which was created in previous steps (for discovery). Here, it will also be used for the monitoring workflows.

Note: In my testing it seems that when instance name filtering is used, any instance names that are rejected due to matching the filter mask (either Exclude or Include) will result in an event log Error entry (EventID: 4221), although it’s not really an error.


By using the Automatic approach with a SPN, SCOM has created a Basic SCOM account credential for us that looks like this (below).


Manually Create SPN and SCOM Account Credential

The Basic SCOM credential Account Name field used the form: AppID@TenantID.
Example: 7d098e79-68c9-4add-8bb3-fe5897092377@8d382e56-3aba-479d-9380-8c81c835a648
…and the password is the app registration secret key. Now that we know how the sausage is made, we could create our own app registration (in Azure Entra ID) and create our own SCOM credential (in SCOM) manually. Creating the app registration is relatively straightforward and I’ve included a crash course below. SQL MI login permissions must be configured as well on the SQL instance. (shown further below)


Quick example of creating an app registration with Reader role and app Authentication (ID tokens) config:

Using the instructions here as a rough guide, customize the following:

Redirect URI – put a bogus address. It doesn’t appear to make one bit of difference as it’s optional.

App now exists.

Add client secret…

Record the secret. You won’t be able to view it again. If you fail to record the secret value, you can always create another Client secret. They’re free!

Enable the ID Tokens on the app Authentication.


Locate your subscription in Microsoft Entra Id-Subscriptions. Open it.

The new app/SPN must be assigned Reader role at the subscrition.

+Select members. You must enter the name of your new app before it will appear in the search list.

App is assigned the Reader role.


Quick example of creating a SCOM account credential for SPN authentication.

Distribute the account to the resource pool.



Least-privilege account configuration on the SQL MI

Specific Requirements (assumptions for this scenario)

  • Your app registration exists and you know the app registration Name.
    In my lab, my app reg name is: Azure_SQL_ManagedInstance_App_0d89baff-c233-4e14-b240-5aa90c94ad81

The only example provided in the official Guide is a TSQL script for SQL Authentication.
Below is a “SPN” version of the script. The script actions are noted in the comments as follows:

-- - Grants server-level permissions to the monitoring account.
-- - Creates a user and role in the master, msdb, and model databases and grants the required permissions to it.
-- - Creates a user and role in all user databases.
-- - Grants server-level permissions to the monitoring account.
-- - Creates a user and role in the master, msdb, and model databases and grants the required permissions to it.
-- - Creates a user and role in all user databases.
-- - Don't forget to replace the example name with your own App Registration name.

USE [master];
SET NOCOUNT ON
DECLARE @accountname SYSNAME = 'YOUR_Azure_SQL_ManagedInstance_App_NAME';  -- replace with YOUR app reg.
CREATE SERVER ROLE [MILowPriv_role];
GRANT VIEW ANY DEFINITION TO [MILowPriv_role];
GRANT VIEW ANY DATABASE TO [MILowPriv_role];
GRANT ALTER ANY DATABASE TO [MILowPriv_role];
GRANT VIEW SERVER STATE TO [MILowPriv_role];
DECLARE @createLoginCommand nvarchar(200);
SET @createLoginCommand = 'CREATE LOGIN '+ QUOTENAME(@accountname) + ' FROM EXTERNAL PROVIDER'
EXEC (@createLoginCommand);

EXEC sp_addsrvrolemember @loginame = @accountname, @rolename = 'MILowPriv_role';
DECLARE @createDatabaseUserAndRole nvarchar(max);
SET @createDatabaseUserAndRole = '';
SELECT @createDatabaseUserAndRole = @createDatabaseUserAndRole + 'USE ' + QUOTENAME(db.name) + ';
CREATE USER ' + QUOTENAME(@accountname) + ' FOR LOGIN ' + QUOTENAME(@accountname) + ';
CREATE ROLE [MILowPriv_role];
EXEC sp_addrolemember @rolename = ''MILowPriv_role'', @membername = ' + QUOTENAME(@accountname) + ''
FROM sys.databases db
WHERE db.database_id <> 2
AND db.user_access = 0
AND db.state = 0
AND db.is_read_only = 0;
EXEC (@createDatabaseUserAndRole);
GO
USE [master];
GRANT EXECUTE ON xp_readerrorlog TO [MILowPriv_role];
GRANT EXECUTE ON xp_instance_regread TO [MILowPriv_role];
GRANT EXECUTE ON xp_sqlagent_enum_jobs TO [MILowPriv_role];
GRANT EXECUTE ON sp_enumerrorlogs TO [MILowPriv_role];
USE [msdb];
GRANT EXECUTE ON sp_help_job TO [MILowPriv_role];
GRANT EXECUTE ON sp_help_jobactivity TO [MILowPriv_role];
GRANT SELECT ON sysjobschedules TO [MILowPriv_role];
GRANT SELECT ON backupset TO [MILowPriv_role];
EXEC sp_addrolemember @rolename='db_datareader', @membername='MILowPriv_role';
EXEC sp_addrolemember @rolename='db_owner', @membername='MILowPriv_role';
EXEC sp_addrolemember @rolename='SQLAgentReaderRole', @membername='MILowPriv_role';

You can see the results of running the query below. The SQL Login has been created for the SPN (app registration) as well as the role: MILowPriv_role.


Optional DBCC Checks Permission (SPN)

This script adds the monitoring account to the db_owner role, which may not be allowed. The db owner permissions are required to enable the management pack tasks to run DBCC checks. If you don’t need these tasks, don’t give these permissions.
Source: here

--Second script that adds MILowPriv user to db_owner role for master, msdb, model, and all user databases.
--This is only to enable running DBCC checks right on SCOM (Check Catalog, Check Database, Check Disk).
--If you don't need this functionality, don't run this script.
DECLARE @accountname sysname = 'YOUR_Azure_SQL_ManagedInstance_App_NAME';
DECLARE @createDatabaseUserAndRole nvarchar(max);
SET @createDatabaseUserAndRole = '';
SELECT @createDatabaseUserAndRole = @createDatabaseUserAndRole + 'USE ' + QUOTENAME(db.name) + ';
EXEC sp_addrolemember @rolename = ''db_owner'', @membername = ' + QUOTENAME(@accountname) + ''
FROM sys.databases db
WHERE db.database_id <> 2
AND db.user_access = 0
AND db.state = 0
AND db.is_read_only = 0
EXEC (@createDatabaseUserAndRole);
GO


Success



Page History:
2023.10.10: Updated manual SPN creation requirements.
2023.10.10: v1

← →

Leave a Reply

Your email address will not be published. Required fields are marked *