SQL Server Contained DB Authentication Enabled on GCP Cloud SQL

If you run SQL Server on Google Cloud SQL, you might have flipped on contained database authentication at some point to make a database more portable, or because a vendor app asked for it. That setting changes how authentication works in a way that has real security consequences, and it is easy to enable and then forget about. This Lensix check, sql_containeddbauth, flags any Cloud SQL for SQL Server instance where the feature is turned on so you can confirm it is actually needed.

---

What this check detects

The check looks at the database flags on each GCP Cloud SQL SQL Server instance and reports any instance where contained database authentication is set to on (a value of 1).

Contained databases store their own authentication metadata inside the database itself, rather than relying on logins defined at the SQL Server instance level. When the feature is enabled, you can create contained database users that authenticate directly against a specific database. They never touch the master database and they do not need a corresponding server login.

---

Why it matters

The portability that makes contained databases attractive is exactly what makes them risky from a security standpoint. Here is what changes when the feature is on.

Authentication bypasses the central login layer

Contained users authenticate without a server-level login. That means the controls and auditing you rely on at the instance level, including password policies tied to logins and centralized account reviews, can be sidestepped. A database administrator with rights over a single contained database can create users without anyone managing the server ever seeing them.

Lateral movement between databases

A well-known risk applies when a contained database is set to a higher trust level than the databases around it. A user with elevated rights inside a contained database can potentially guess or collide with credentials used elsewhere on the same instance. Microsoft documents this scenario directly: a user with ALTER ANY USER permission in a contained database can create users that may grant access beyond the intended boundary if other databases trust the same authentication.

Auditing and compliance gaps

Most database hardening baselines, including the CIS Benchmark for SQL Server, recommend leaving contained database authentication off unless there is a specific need. Auditors expect to see authentication centralized and logged. Contained users that live only inside a database are easy to miss during access reviews, which creates findings in PCI DSS, SOC 2, and HIPAA assessments.

---

How to fix it

The fix is to set the contained database authentication flag to off. Before you do, confirm that no application or user actually depends on a contained database user, because turning the flag off will break authentication for those accounts.

Step 1: Check for existing contained databases and users

Connect to the instance and run the following to find any contained databases:

SELECT name, containment, containment_desc
FROM sys.databases
WHERE containment <> 0;

For each contained database returned, list the contained users:

USE [your_contained_db];
SELECT name, type_desc, authentication_type_desc
FROM sys.database_principals
WHERE authentication_type = 2;

Step 2: Migrate contained users to server logins (if any exist)

For each contained user that needs to keep working, create a server login and map a regular database user to it:

-- Create a server-level login
CREATE LOGIN [app_user] WITH PASSWORD = 'StrongP@ssphrase-Here';

-- In the previously contained database, map the user to the new login
USE [your_contained_db];
ALTER USER [app_user] WITH LOGIN = [app_user];

Step 3: Set the database back to non-contained

ALTER DATABASE [your_contained_db] SET CONTAINMENT = NONE;

Step 4: Disable the instance flag with gcloud

Cloud SQL exposes this as the database flag contained database authentication. Set it to off:

gcloud sql instances patch INSTANCE_NAME \
  --database-flags="contained database authentication=off"

Applying this flag does not require a restart for SQL Server contained authentication on Cloud SQL, but verify the change took effect:

gcloud sql instances describe INSTANCE_NAME \
  --format="json(settings.databaseFlags)"

Console steps

1. Open the Cloud SQL instances page in the Google Cloud console.
2. Select the SQL Server instance and click Edit.
3. Expand Flags.
4. Find contained database authentication and set it to Off, or remove the flag entirely.
5. Click Save.

---

How to prevent it from happening again

Manual fixes drift back over time. Bake the desired state into your infrastructure code and gate it in CI.

Set the flag in Terraform

If you manage Cloud SQL with Terraform, pin the flag in the resource so any out-of-band change gets reverted on the next apply:

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

  settings {
    tier = "db-custom-2-7680"

    database_flags {
      name  = "contained database authentication"
      value = "off"
    }
  }
}

Add a policy-as-code check

Use Open Policy Agent with Conftest, or Sentinel if you run Terraform Cloud, to fail any plan that leaves the flag unset or set to on. A simple OPA rule against a Terraform plan in JSON might look like this:

package cloudsql

deny[msg] {
  resource := input.resource_changes[_]
  resource.type == "google_sql_database_instance"
  flag := resource.change.after.settings[_].database_flags[_]
  flag.name == "contained database authentication"
  flag.value != "off"
  msg := sprintf("Instance %s has contained database authentication enabled", [resource.address])
}

Continuous detection with Lensix

Policy gates catch problems in code, but they do not see resources created by hand, by another team, or before the policy existed. Lensix runs the sql_containeddbauth check continuously across your GCP projects so an instance that gets the flag flipped on after deployment shows up on your dashboard rather than waiting for the next audit.

---

Best practices

• Centralize authentication. Keep authentication at the server-login level so you have one place to manage, rotate, and audit accounts. Better still, integrate Cloud SQL for SQL Server with IAM or Active Directory where your environment supports it.
• Treat exceptions as documented decisions. If a workload genuinely requires a contained database, record why, scope it to a dedicated instance, and review it on a schedule rather than leaving it as a silent default.
• Audit database principals regularly. Run periodic queries for users with authentication_type = 2 across your instances so contained users cannot accumulate unnoticed.
• Apply least privilege to database admins. The lateral-movement risk grows when many people hold ALTER ANY USER in databases. Restrict that permission to the accounts that truly need it.
• Pair this flag with the rest of the SQL Server baseline. Contained authentication is one line item in a hardening standard. Check it alongside flags like cross db ownership chaining, external scripts enabled, and remote access so you close the whole set, not just one.

Disabling contained database authentication is a low-effort change with a clear payoff: you keep authentication centralized, auditable, and harder to abuse. Confirm nothing depends on it, set the flag to off, codify it in Terraform, and let continuous scanning catch any drift.