Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from http://cloud.google.com/sql/docs/sqlserver/db-audit below:

SQL Server database auditing | Cloud SQL for SQL Server

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

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 enabling database auditing, review the prerequisites in this section.

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.

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.

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)

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

   Go to Cloud SQL Instances

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

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:

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

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

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.

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

   Go to Cloud SQL Instances

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

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

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

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":{
      }
   }
}

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":{
      }
   }
}

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.

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:

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%'

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%'

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.

• 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.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4