SQL Servers
      Back to home
      1. Knowledge Base
      2. Azure Knowledge Base
      3. SQL Servers

      Audit Action Groups Disabled

      Risk Level: LOW

      Description: 

      This plugin makes sure that SQL Server Audit Actions and Groups are set up correctly. SUCCESSFUL DATABASE AUTHENTICATION GROUP, FAILED DATABASE AUTHENTICATION GROUP, and BATCH COMPLETED GROUP should all be included in SQL Server Audit Action and Groups. Auditing server-level and database-level groupings of events as well as individual events can be done using the SQL Server Audit feature.

      PingSafe strongly recommends if SQL Server Audit Action and Groups are not configured properly when enabling Auditing, these settings must be configured in Powershell.

      About the Service :

      Azure SQL is a set of managed, secure, and intelligent SQL Server database solutions that run in the Azure cloud. Because Azure SQL is based on the well-known SQL Server engine,  applications can be easily transferred while keeping the existing tools, languages, and resources. 

      Impact : 

      Auditing should be enabled to enable the "AuditActionGroup" property with the right configuration to record essential activities made on your Azure SQL databases. The "AuditActionGroup" should contain the following action groups to guarantee full audit logging for your SQL servers and SQL databases hosted on these servers: SUCCESSFUL DATABASE AUTHENTICATION GROUP – indicates that a principal successfully logged on to a contained database, FAILED DATABASE AUTHENTICATION GROUP – indicates that a principal attempted to log on to a contained database but failed (events in this class are triggered by new connections or connections reused from a connection pool), and BATCH COMPLETED GROUP – indicates that the Transact-SQL batch has been completed.

      Steps to Reproduce :

      1. Sign in to your Azure portal with your Azure account.
        https://portal.azure.com/#home 
      2. Navigate to Azure’s All Resources.
      3. In the Type filter select the value as SQL Servers and click Apply.
      4. Next, select the SQL Server that you want to examine.
      5. Now, navigate to the Powershell and type az login.
      6. A new tab will appear directing you to choose your azure account, choose your account and you will be logged in through the command line.
      7. To list all the sql servers run the command az sql server list
      8. Now, run the command
        az sql server audit-policy show --name=<server’s name> --resource-group=<resource group’s name>
        to audit the action groups.
      9. It shows the following output:
      10. There is no “AUDIT ACTION GROUPS ENABLED”. This suggests that audit action groups are not enabled.
      11. Repeat the same steps for other servers as well.

      Steps for Remediation :

      1. Sign in to your Azure portal with your Azure account.
        https://portal.azure.com/#home 
      2. Navigate to Azure’s All Resources.
      3. In the Type filter select the value as SQL Servers and click Apply.
      4. Next, select the SQL Server that you want to examine.
      5. Now, navigate to the PowerShell and type az login.
      6. A new tab will appear directing you to choose your azure account, choose your account and you will be logged in through the command line.
      7. To list all the sql servers run the command az sql server list
      9. Now, run the command:
        az sql server audit-policy show --name=<server’s name> --resource-group=<resource group’s name>
      10. It shows the following output:
      11. There is no “AUDIT ACTION GROUPS ENABLED”. This suggests that audit action groups are not enabled.
      12. Now, run the command:
      13. az sql server audit-policy update -g <group> -n <server> \    --actions FAILED_DATABASE_AUTHENTICATION_GROUP 'UPDATE on server::myserver by public'
      14. The audit action groups are now enabled.
      15. Repeat the same steps for other servers as well.

      References :

      Please feel free to reach out to support@pingsafe.ai with any questions that you may have.

      Thanks

      PingSafe Support