Configure SQL Auditing to Storage Account using Managed Identity for SQL Managed Instance.
Published May 23 2023 03:18 AM 4,687 Views
Microsoft

With User Managed Identity support for SQL Managed Instance, Auditing to Storage Account target can be configured with the following authentication methods for SQL MI: 

  • Managed Identity  
  • Storage Access Keys 

Managed Identity can be a System-assigned Managed Identity (SMI) or User-assigned Managed Identity (UMI). 

In this blog, we will walk you through the steps to configure auditing using managed identity for SQL Managed Instance.  

SMI and UMI can be used for any storage account within or behind the firewall.  

 

User-assigned Managed Identity 

UMI gives users the flexibility to create and maintain their own UMI for a given tenant. The UMI can be used as server identities for Azure SQL. It is managed by the user, compared to a system-assigned managed identity, which identity is uniquely defined per server, and assigned by the system. 

For more information about UMI, see Managed identities in Azure AD for Azure SQL 

 

To configure auditing using Managed Identity, follow the below steps. 

  1. Assign identity to SQL Managed Instance: 

Go to the identity blade for SQL Managed Instance in azure portal. There you can see if system identity is set to on. You can create a user managed identity and assign it to SQL Managed Instance and chose primary identity. When auditing is configured to use the identity, by default it picks the primary identity. If there is no primary identity assigned, then it uses the system managed identity. Customer can choose which identity they want to use.  

SravaniSaluru_0-1684835653155.png

 

 2. Set up auditing for your managed instance to Azure Storage 

The following section describes the configuration of auditing on your managed instance. 

  1. Go to the Azure portal. 
  2. Create an Azure Storage container where audit logs are stored. 
  3. Navigate to the Azure storage account where you would like to store your audit logs. 
  • Use a storage account in the same region as the managed instance to avoid cross-region reads/writes. 
  • If your storage account is behind a Virtual Network or a Firewall, see Grant access from a virtual network. 
  • If you change retention period from 0 (unlimited retention) to any other value, retention will only apply to logs written after retention value was changed (logs written during the period when retention was set to unlimited are preserved, even after retention is enabled).
  • In the storage account, go to Overview and select Blobs. 

SravaniSaluru_4-1684835653169.png

 

5. In the top menu, select + Container to create a new container. 

SravaniSaluru_5-1684835653169.png

 

6. Provide a container Name, set public access level to Private, and then select OK. 

SravaniSaluru_6-1684835653170.png

 

7. After you create the container for the audit logs and grant required permissions, there are two ways to configure it as the target for the audit logs: using T-SQL or using the SQL Server Management Studio (SSMS) UI: 

 

 

3. Assign the required permissions to Storage Account.  

Before auditing can be set up to send logs to your storage account, the managed identity assigned to the server needs to have the Storage Blob Data Contributor role assignment. This assignment is required for SQL managed instance as the auditing is configured using t-sql commands.  

  1. Go to the Azure portal. 
  2. Create a user-assigned managed identity if you have not already done so. For more information, see creating user assigned managed identity. 
  3. Go to your storage account that you want to configure for auditing. 
  4. Select the Access Control (IAM) menu. 
  5. Select Add > Add role assignment. 
  6. In the Role tab, search and select Storage Blob Data Contributor. Select Next. 
  7. In the Members tab, select Managed identity in the Assign access to section, and then Select members. You can select the Managed identity that was created for your server (you can assign this role to SMI or UMI). 
  8. Select Review + assign. 

 

 UMI 

SravaniSaluru_2-1684835653162.png

 

 

 

SMI 

 

To assign permissions to System Managed Identity you can chose the SQL Managed Instance under System assigned Managed Identity. 

 

 

SravaniSaluru_3-1684835653165.png

 

 For more information, see Assign Azure roles using portal. 

 

Configure blob storage for audit logs using T-SQL: 

  • In the containers list, select the newly created container and then select Container properties. 

SravaniSaluru_7-1684835653171.png

 

 

  • Copy the container URL by selecting the copy icon and save the URL (for example, in Notepad) for future use. The container URL format should be https://<StorageName>.blob.core.windows.net/<ContainerName> 

SravaniSaluru_8-1684835653172.png

 

 

  • Connect to your managed instance via SQL Server Management Studio or any other supported tool. 
  • Execute the following T-SQL statement to create a new credential using the container URL and managed identity.  

CREATE CREDENTIAL [<container_url>] 

WITH IDENTITY='managed identity’ 

Execute the following T-SQL statement to create a new server audit (choose your own audit name and use the container URL that you created in the previous steps). If not specified, the RETENTION_DAYS default is 0 (unlimited retention): 

CREATE SERVER AUDIT [msaudit] 

TO URL (PATH ='<container_url>', RETENTION_DAYS = <integer>). 

GO 

Create SERVER AUDIT SPECIFICATION [audit_sepcification2] 

FOR SERVER AUDIT [msaudit] 

ADD (FAILED_DATABASE_AUTHENTICATION_GROUP), 

ADD (SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP), 

ADD (BATCH_COMPLETED_GROUP) 

WITH (STATE = ON); 

GO 

ALTER SERVER AUDIT [msaudit] WITH (STATE=ON); 

GO 

 

You can view the audit logs from SSMS view audit logs.  

 

 

SravaniSaluru_9-1684835653174.png

 

With this we have successfully configured auditing to use managed identity to access storage account target.  

Co-Authors
Version history
Last update:
‎May 23 2023 03:30 AM
Updated by: