SQL Compliance Manager displays a refined architecture that permits registering Azure Managed Instances. In this process the CM Agent communicates with the SQL Instance and, using T-SQL instructs to emit the desired audit records into an Azure Blob Container.
Important notes on Azure MI auditing
Each CM Agent virtual machine should not have a greater number of CM AzureSQL Agents deployed into it than the number of cores in the virtual machine.
Multiple CM Agent virtual machines will be required for a large number of monitored Azure Manage Instances.
Prerequisites
- SQL Compliance Manager 7.0 installed on a machine.
- Azure SQL Managed Instance.
- Blob storage account with container and SAS token for access. For the blob storage make sure that:
- Default access tier is "Hot".
- It's best practice for the Storage Account to be located in the same region where the monitoring SQL instance will be (e.g. North Europe).
- Ensure the "Enable storage account key access" setting is checked. The option is available during creation, or under the "Configuration" blade afterward.
- Make sure that the storage account is accessible from the SQL-managed instance.
- When generating a Shared Access Signature, make sure its expiry is sufficient in the future. Upon expiry, the SAS token must be updated from the SQL Server itself.
Configuration steps
- Obtain BLOB storage URL and SAS key.
- Azure blob storage credentials obtained from Step 1 need to be added to the managed instance to allow it to save audit files to the blob storage.
- Connect to your managed instance via SQL Management Studio or any other supported tool.
- Execute the following T-SQL statement to create a new credential using the container URL and SAS token:
CREATE CREDENTIAL [<container_url>]
WITH IDENTITY='SHARED ACCESS SIGNATURE',
SECRET = '<SAS KEY>'
GO - Start the SQLCM console, go to the Explore Activity tab, and right-click on Audited SQL Servers to add a New Registered SQL Server.
- From the Server Type menu select Azure SQL Managed Instance. In the SQL Server field enter the URL of the managed instance followed by the port.
- Click on the Next button and choose the type of authentication against the Managed Instance and enter the user credentials for Azure SQL.
- Click next and specify the blob connection details. For Blob Name, you can enter the container's name.
- You can use the Test Connection button to verify that SQLCM can successfully connect to the blob storage.
- Click the Next button and on the next screen enter the host where you want to deploy the SQL CM agent for this managed instance. For deploying on the machine where you have the SQL CM console installed you can specify the hostname and for remote deployment please enter the remote machine’s IP address.
- Click the Next button and choose the deployment option.
- Click the Next button and provide the credentials for the service account that should be running the SQL CM agent for the particular managed instance.
- Click the Next button and specify the trace directory where the agent should place the audit files.
- Click the Next button to initiate the deployment of the agent.
- Once deployment of the agent completes you will be presented with the list of the databases which exist on the server to choose which ones you may want to audit.
- Select the databases for auditing and click the Next button to specify the Audit Collection Level.
- Click on the Next button and the permissions check will run.
- Click the Next button for a summary of the settings for the newly added Azure SQL Managed Instance.
- Click the Finish button to complete the process. Once completed you should see the new Managed Instance listed in the Audited SQL Servers list in the Explore Activity tab.
