SQL Server database auditing

This page describes database auditing in Cloud SQL using the functionality of SQL Server Audit.

Overview

In Cloud SQL, SQL Server Audit capabilities include the following:

Creating server audit specifications
Tracking and logging server-level and database-level events

For additional information about the capabilities of SQL Server Audit, see SQL Server Audit (Database Engine).

Before you begin

Before enabling database auditing, review the prerequisites in this section.

Cloud Storage bucket for audit files

Audit files (audit logs) are uploaded to a Cloud Storage bucket location. Thus, you may need to create a bucket owned by your Google Cloud account.

Alternatively, you can use a bucket location owned by another account. When you enable auditing, if you have the necessary permissions, the roles/storage.objectAdmin role is granted automatically for uploading audit files to the bucket location of the given service account. If you don't have the necessary permissions, you must grant them for the service account, later.

Valid user for enabling auditing

To enable auditing and to create audit specifications, the default sqlserver user must be available. When you created a Cloud SQL for SQL Server instance, the default sqlserver user was created for you.

Enabling auditing

When you enable auditing, a Cloud Storage location is required. The following are optional:

The log retention period on the instance
The upload interval (upload frequency)

Console

In the Google Cloud console, go to the Cloud SQL Instances page.

Go to Cloud SQL Instances
To open the Overview page of an instance, click the instance name.
Click Edit.
In the Customize your instance section, click Flags and parameters.
Check the checkbox next to Enable SQL Server audit.
Specify a Cloud Storage bucket as the location to which audit files are uploaded.
Click Advanced Options.
Optionally, specify a number of days for log retention (1 to 7 days; 7 days is the default).
Optionally, specify how often to upload logs (audit files), in minutes (1-720 minutes; 10 minutes is the default).
Click Save to apply your changes.

gcloud

The following command enables auditing:

gcloud sql instances patch INSTANCE_NAME --audit-bucket-path=gs://my-bucket --audit-retention-interval=24h --audit-upload-interval=10m

The following table summarizes this operation's gcloud parameters:

Parameter	Description	Allowed values	Default value
`--audit-bucket-path`	Required. The location, as a Cloud Storage bucket, to which audit files are uploaded.	Empty if audit is disabled. Otherwise, a bucket path that starts with: `gs://`	Empty, because by default, audit is disabled.
`--audit-retention-interval`	Optional. The number of days for audit log retention on disk.	1 to 7 days. Only days are allowed.	7 days.
`--audit-upload-interval`	Optional. How often to upload audit logs (audit files).	1-720 minutes.	10 minutes.

REST v1

Using the REST API, you can enable auditing for an instance. As shown in the following request prototype, you can specify a Cloud Storage bucket, a number of days for audit file retention, and a frequency for the uploading of audit files. Only the bucket location is required. For more information, see SqlServerAuditConfig:

{
   "databaseVersion":"database-version",
   "name":"instance-id",
   "region":"region",
   "rootPassword":"password",
   "settings":{
      "tier":"machine-type",
      "sqlServerAuditConfig":{
         "bucket":"gs://mybucket",
         "retentionInterval":"24h",
         "uploadInterval":"10m"
      }
   }
}

REST v1beta4

{
   "databaseVersion":"database-version",
   "name":"instance-id",
   "region":"region",
   "rootPassword":"password",
   "settings":{
      "tier":"machine-type",
      "sqlServerAuditConfig":{
         "bucket":"gs://mybucket",
         "retentionInterval":"24h",
         "uploadInterval":"10m"
      }
   }
}

Disabling auditing

This section contains the options for disabling auditing. When you disable auditing, all audit files, including those not yet uploaded, are deleted from the instance. Additionally, all server audits are disabled and must be re-enabled if auditing is to resume. Audit logs that were uploaded to the Cloud Storage bucket may remain, depending on your retention settings for the bucket.

The following are the options for disabling auditing.

Console

In the Google Cloud console, go to the Cloud SQL Instances page.

Go to Cloud SQL Instances
To open the Overview page of an instance, click the instance name.
Click Edit.
In the Customize your instance section, click Flags and parameters.
Uncheck the checkbox next to Enable SQL Server audit.
Click Save to apply your changes.

gcloud

The following command, which omits a value for the --audit-bucket-path parameter, disables auditing:

gcloud sql instances patch INSTANCE_NAME --audit-bucket-path=

REST v1

The following, which omits the fields of the sqlServerAuditConfig object, is a request prototype for disabling auditing:

{
   "databaseVersion":"database-version",
   "name":"instance-id",
   "region":"region",
   "rootPassword":"password",
   "settings":{
      "tier":"machine-type",
      "sqlServerAuditConfig":{
      }
   }
}

REST v1beta4

The following, which omits the fields of the sqlServerAuditConfig object, is a request prototype for disabling auditing:

{
   "databaseVersion":"database-version",
   "name":"instance-id",
   "region":"region",
   "rootPassword":"password",
   "settings":{
      "tier":"machine-type",
      "sqlServerAuditConfig":{
      }
   }
}

Automatic uploading of audit files

After you enable auditing, the generated audit files are uploaded automatically to your specified Cloud Storage bucket.

The generated audit files also are stored with the instance until the configured retention period (interval) expires. After that period, the audit files are permanently deleted, even the files that could not be uploaded.

Creating server audits

After you enable auditing, the default sqlserver user has permissions to create, alter and drop server audits.

Use the CREATE SERVER AUDIT command to define new server audits. You also can use the user interface of SQL Server Management Studio (SSMS) to create server audits.

The following categories of parameters are enforced for all server audits:

Category of parameter	Allowed values
On audit log failure	Continue or fail
Audit destination	File
Path	`/var/opt/mssql/audit`
Maximum file size	2-50 MB
Maximum rollover files	Must be: not configured
Maximum files	Must be: not configured
Reserve disk space	Off

Reading audits

Reading audits from an instance

To retrieve the data from an audit file created by a server audit, you can use the following stored procedure: msdb.dbo.gcloudsql_fn_get_audit_file. The msdb.dbo.gcloudsql_fn_get_audit_file procedure accepts the same parameters as the sys.fn_get_audit_file function.

Thus, for information about using that stored procedure, see sys.fn_get_audit_file.

Here is example of using the msdb.dbo.gcloudsql_fn_get_audit_file procedure to retrieve audit data:

SELECT event_time, statement FROM msdb.dbo.gcloudsql_fn_get_audit_file('/var/opt/mssql/audit/*', NULL, NULL) WHERE statement LIKE '%INSERT%'

Reading audits from a bucket

To read audits from a Cloud Storage bucket, you can download the files from the bucket to a SQL Server instance. That SQL Server instance could be:

A Compute Engine Windows instance
A Compute Engine Linux instance
Another type of instance that is running SQL Server

Audit files from the bucket should be transferred to a location accessible to that instance, such as its local disk. Then, to return information from the audit files, run the sys.fn_get_audit_file function using a member of the serveradmin fixed server role. For example, from a Windows instance, if you transferred audit files to D:\Audit, you could use a command similar to the following:

SELECT event_time, statement FROM sys.fn_get_audit_file('D:\Audit\*.*', NULL, NULL) WHERE statement LIKE '%INSERT%'

Monitoring metrics

You can monitor audit-related operations as part of the Cloud SQL metrics, available through Metrics Explorer:

database/sqlserver/audits_upload_count. This metric indicates the total of the audit file upload attempts, for successful and failed attempts. Check this metric to monitor whether uploads were successful.
database/sqlserver/audits_size. This metric indicates how much disk space is used by audit files stored on the instance, at the time of a measurement. This metric provides the total size of the audit files on the instance, in bytes.

Best practices

To avoid transferring large files across regions, consider:
- Creating multi-regional Cloud Storage buckets, or
- Setting up the destination Cloud Storage bucket in the same region as the instance
Before using the functionality of SQL Server Audit, consider enabling automatic storage increases. Audit files, depending on their total size and retention intervals, may need significant disk space.
After enabling audit, verify that your instance can upload audit files into your Cloud Storage bucket. If the requester who set up auditing lacked the necessary permissions for uploading files to the Cloud Storage bucket, the permissions aren't granted automatically.
Set up a metric-based alerting policy to notify you about failed upload attempts and excessive disk usage.
To avoid retaining too many audit files for too long, set up a data retention policy for your Cloud Storage bucket.