Review Cloud SQL SQL Server session defaults

Description

SQL Server user options is a bitmask of default session options for new connections. A nonzero value does not itself grant dangerous permissions; the example value 32 selects ANSI_NULLS.

Sessions can change options with SET statements, and existing connections do not immediately adopt changed server defaults. Unsuitable defaults can alter query behavior or data-processing results.

Potential impact

Unexpected NULL comparisons or other session behavior can cause application errors.
Resetting defaults indiscriminately can disrupt workloads that depend on particular options.

Remediation

Review each user options bit and the session options set by applications. Remove only unnecessary global defaults; do not treat 0 as a universal security fix.
Test effective options and query results on new connections, and plan connection-pool renewal and rollback.

Examples

These excerpts compare part of the instance settings. Use a supported engine version and SQL Server machine type, and supply var.sql_server_tier and omitted required inputs. Changing this flag does not require the engine-version change shown in the examples.

Before

hcl

resource "google_sql_database_instance" "db" {
  name             = "sqlserver-instance"
  database_version = "SQLSERVER_2017_EXPRESS"
  region           = "us-central1"

  settings {
    database_flags {
      name  = "user options"
      value = "32"
    }
  }
}

After

hcl

resource "google_sql_database_instance" "db" {
  name             = "sqlserver-instance"
  database_version = "SQLSERVER_2019_STANDARD"
  region           = "us-central1"

  settings {
    tier = var.sql_server_tier

    database_flags {
      name  = "user options"
      value = "0"
    }
  }
}

Explanation:

Before: 32 sets ANSI_NULLS as a default for new connections. It does not itself grant additional access.
After: 0 specifies no additional user options bits. Verify that required session behavior is preserved.