Search code examples
azure-sql-databaseazure-powershellazure-monitoringaudit-logging

Poweshell to set Azure SQL Auditing

I am trying to use powershell and possibly diagnosticsettings to set the below values in sql server (red ones) enter image description here

and send the log to Event hub,

here is my ps script:

New-AzDiagnosticSetting -ResourceId "/subscriptions/subid/resourceGroups/rgname/providers/Microsoft.Sql/servers/servername" -Name "dignnosticsettingname" -EventHubAuthorizationRuleId "/subscriptions/subidofeventhub/resourceGroups/rgofeventhub/providers/Microsoft.EventHub/namespaces/namespaceofeventhub/authorizationrules/sqlserver-sendkey" -EventHubName "sqlserver" -Log @(
    (New-AzDiagnosticSettingLogSettingsObject -CategoryGroup "SQLSecurityAuditEvents" -Enabled $true),
    (New-AzDiagnosticSettingLogSettingsObject -CategoryGroup "SQLInsights" -Enabled $true)
)

Error:

New-AzDiagnosticSetting : CategoryGroup: 'SQLSecurityAuditEvents' is not supported, supported ones are: '' ,......

when I do it manually in the portal it appears as set server blob auditing policy while when I run my ps command it appears as create or update resource diagnostic settings in the operation type of activity log with some error similar to the above error

What is the correct PS syntax/command?

This MS document has some instructions but I can't figure out the correct syntax or command type

Update: It seems Diognastic setting gets updated, but not sure how, as nothing is changing in the portal

enter image description here

Also I am not sure whether SQL audit as part of Auditing (databsae and Server) is required as there is an audit category group on the SQL Database. enter image description here

enter image description here

Please note SQL Server only has Audit, Database has Diagnostic settings and Audit. Do we need to set Audit in both server and database?

UPDATE:

Following the answer provided by Pratik:

In case you can set the audit conf in the portal and not in the Powershell, make sure your subscription is set to the correct subscription as you may receive an error similar to The client ... does not have authorization to perform action 'Microsoft.Sql/servers/extendedAuditingSettings/read' over scope .... /extendedAuditingSettings/default or the scope is invalid.

To get the subscription: Get-AzContext

To set subscription: Set-AzContext -SubscriptionName SubscriptionName

SQL database Diagnostic settings can be done by he below command:

New-AzDiagnosticSetting -ResourceId "/subscriptions/subid/resourceGroups/rgname/providers/Microsoft.Sql/servers/servername/databases/dbname" -Name "diagniosticname" -EventHubAuthorizationRuleId "/subscriptions/subid/resourceGroups/rgname/providers/Microsoft.EventHub/namespaces/namespacename/authorizationrules/sharedaccesskeyname" -EventHubName "sqldatabaseserver" -Log @(
    (New-AzDiagnosticSettingLogSettingsObject -CategoryGroup "allLogs" -Enabled $true),
    (New-AzDiagnosticSettingLogSettingsObject -CategoryGroup "audit" -Enabled $true)
) -Metric @(
    (New-AzDiagnosticSettingMetricSettingsObject -Category "AllMetrics" -Enabled $true)
)

Also note by enabling Audit settings by using Set-AzSqlServerAudit, audit settings will be enabled on the database automatically: enter image description here

Solution

  • To enable Azure SQL Auditing and Auditing of Microsoft support operations in Azure SQL using Powershell you need to use below commands:

    To enable Azure SQL Auditing for SQL server:

    Set-AzSqlServerAudit -ResourceGroupName "ResourceGroupName" -ServerName "ServerName" -EventHubTargetState Enabled -EventHubName "EventHubName" -EventHubAuthorizationRuleResourceId "/subscriptions/subscriptionid/resourceGroups/resourceGroupsname/providers/Microsoft.EventHub/namespaces/eventhubname/authorizationrules/RootManageSharedAccessKey"

    To enable Auditing of Microsoft support operations for SQL server

    Set-AzSqlServerMSSupportAudit -ResourceGroupName "ResourceGroupName" -ServerName "ServerName" -EventHubTargetState Enabled -EventHubName "EventHubName" -EventHubAuthorizationRuleResourceId "/subscriptions/subscriptionid/resourceGroups/resourceGroupsname/providers/Microsoft.EventHub/namespaces/eventhubname/authorizationrules/RootManageSharedAccessKey"

    Final output: enter image description here

    Make sure the account or service principal being used has the necessary permissions to read the Extended Auditing Settings of an Azure SQL Database. To assign a role follow the below steps:

    Go to the SQL Server >> Access control (IAM) >> Add role assignment >> Assign Reader or Contributor role to the user or service principal >> Search for the specific user or service principal, select it, and save.