This page describes database auditing in Cloud SQL using thefunctionality 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, seeSQL 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 tocreate a bucket ownedby your Google Cloud account.

Alternatively, you can use a bucket location owned by another account. When youenable auditing, if you have the necessary permissions,theroles/storage.objectAdminroleis granted automatically for uploading audit files to the bucket location of thegivenservice account. Ifyou don't have the necessary permissions, you must grant them for theservice account, later.

Valid user for enabling auditing

To enable auditing and to create audit specifications, the defaultsqlserveruser must be available. When you created a Cloud SQL for SQL Serverinstance, the defaultsqlserver user was created for you.

Enable auditing

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

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

gcloudsqlinstancespatchINSTANCE_NAME--audit-bucket-path=gs://my-bucket--audit-retention-interval=24h--audit-upload-interval=10m

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

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

Disable auditing

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

The following are the options for disabling auditing.

gcloudsqlinstancespatchINSTANCE_NAME--audit-bucket-path=

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

{"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 automaticallyto your specified Cloud Storage bucket.

The generated audit files also are stored with the instance until the configuredretention period (interval) expires. After that period, the audit files arepermanently deleted, even the files that couldn't be uploaded.

Create server audits

After you enable auditing, the defaultsqlserver user has permissions tocreate, alter and drop server audits.

Use theCREATE SERVER AUDITcommand to define new server audits. You also can use the user interface ofSQL Server Management Studio (SSMS)to create server audits.

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

Read audits

The following sections discuss different ways to read audits.

Read audits from an instance

To retrieve the data from an audit file created by a server audit, you can usethe following stored procedure:msdb.dbo.gcloudsql_fn_get_audit_file. Themsdb.dbo.gcloudsql_fn_get_audit_file procedure accepts the sameparameters as thesys.fn_get_audit_file function.

Thus, for information about using that stored procedure, seesys.fn_get_audit_file.

Here is example of using themsdb.dbo.gcloudsql_fn_get_audit_file procedure toretrieve audit data:

SELECTevent_time,statementFROMmsdb.dbo.gcloudsql_fn_get_audit_file('/var/opt/mssql/audit/*',NULL,NULL)WHEREstatementLIKE'%INSERT%'

This stored procedure is better suited for smaller to moderate volumes of data.If you want to read audit logs for very large volumes of data, we recommendreading audit logs from a Cloud Storageusing a Compute Engine instance.

Read audits from a bucket

To read audits from a Cloud Storage bucket, you can download the files from thebucket 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 tothat instance, such as its local disk. Then, to return information from theaudit files, run thesys.fn_get_audit_filefunction using a member of the serveradmin fixed server role. For example, froma Windows instance, if you transferred audit files toD:\Audit, youcould use a command similar to the following:

SELECTevent_time,statementFROMsys.fn_get_audit_file('D:\Audit\*.*',NULL,NULL)WHEREstatementLIKE'%INSERT%'

Monitor metrics

You can monitor audit-related operations as part of theCloud SQL metrics,available throughMetrics Explorer:

• database/sqlserver/audits_upload_count. This metric indicates thetotal 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 diskspace is used by audit files stored on the instance, at the time of ameasurement. This metric provides the total size of the audit files on theinstance, in bytes.

Best practices

• To avoid transferring large files across regions, consider the following:
  • Creating multi-regional Cloud Storage buckets
  • Setting up the destination Cloud Storage bucket in the sameregion as the instance
• Before using the functionality of SQL Server Audit, considerenabling automatic storage increases.Audit files, depending on their total size and retention intervals, may needsignificant disk space.
• After enabling audit, verify that your instance can upload audit files intoyour Cloud Storage bucket. If the requester who set up auditinglacked the necessary permissions for uploading files to the Cloud Storagebucket, then the permissions aren't granted automatically. For moreinformation about permissions, see the following resources:
  • Cloud Audit Logs roles
  • Cloud Storage permissions
• Set up ametric-based alerting policyto notify you about failed upload attempts and excessive disk usage.
• To avoid retaining too many audit files for too long, set up a dataretention policy for your Cloud Storage bucket.