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.
- 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
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" } } }
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.
- 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.