Ensure 'user connections' database flag for Cloud SQL SQL server instance is set to a non-limiting value

A Cloud SQL for SQL Server instance with the user connections flag set to a low number refuses every connection past that number, and the application sees dropped requests rather than slow ones. The control requires the flag to equal 0, which is its default, and no other value satisfies the audit.

What the benchmark asks for

The check reads the user connections flag on each Cloud SQL for SQL Server instance and expects the value 0. SQL Server treats 0 as self-configuring. It allocates connection objects as sessions arrive, up to the engine maximum of 32,767. Any other value is a ceiling, and once the instance holds that many sessions the next login attempt fails.

Because 0 is the default, an instance whose flags nobody has changed passes. An instance fails only where somebody set user connections to a limit.

How it works

Cloud SQL exposes user connections as a database flag, which is the mechanism Google gives for the SQL Server configuration options it permits customers to change. The flag name contains a space, and that space matters everywhere the name is typed.

The accepted values are narrower than the benchmark's range suggests. Google's SQL Server flag reference gives the permitted input as 0 or an integer between 10 and 32767, so there is no way to set a limit of 1 through 9. A pipeline that computes a connection cap from a formula can produce a single-digit result and fail the patch rather than apply it.

The flag requires a restart. Google marks each SQL Server flag with whether changing it restarts the instance, and user connections is one that does, while trace flags such as 1204 and 1222 take effect without one. A restart closes every open session. If the instance is the primary of a read replica, the replica restarts too, so its configuration stays aligned with the primary's.

The flag set behaves as a whole, not as individual entries. A gcloud sql instances patch call that passes --database-flags replaces the instance's entire flag set, and any flag not repeated in that command returns to its default. The Admin API behaves the same way. A PATCH request carrying settings.databaseFlags substitutes the array rather than merging into it. An instance running 1222 for deadlock logging loses that flag the moment somebody patches user connections alone.

What the control does not cover

The control reads one flag. It does not tell you whether the instance can actually serve 32,767 connections. Every connection object consumes memory, so a small machine type such as db-custom-1-3840 runs out of memory before it reaches the engine limit, and it fails in a messier way than a clean connection refusal.

It also does not cover the other places a connection ceiling can live. Application connection pools set their own maximums, and a pool configured for 20 connections limits the workload regardless of what the flag says. A page that passes this control can still be the one that drops connections under load.

The control makes no statement about authentication or network exposure. A connection limit is not an access control, and what decides who reaches the instance at all is its network configuration and its authentication mode.

What it costs to comply

The direct cost is a restart. Setting or clearing user connections on a Cloud SQL for SQL Server instance restarts it, and Google warns that changing flags can bring unforeseen complications as well. Applications without connection retry will surface errors to their users for the length of the restart. Schedule the change in a maintenance window, and tell whoever operates the applications on that instance before you start.

If the instance has read replicas, the restart propagates. Reporting workloads pointed at a replica go down alongside the primary, so a change that looks like it touches one instance takes out the whole set.

Run a patch that names only user connections and every other flag on that instance reverts to default in the same operation. A trace flag such as 1222, a remote query timeout (s) value, a contained database authentication setting, anything the command does not repeat disappears silently, and the instance keeps running with behavior nobody changed on purpose. Read the current flag set first and carry it forward in the same command.

There are cases where the cost is not worth paying. An instance capped because its machine type cannot support more connections needs a larger machine type first, so the resize comes before the flag change rather than after it. A development instance that nobody connects to is also not worth a restart on its own schedule, and the change can wait until the next time it restarts for another reason.

How to check your environment

Read the current configuration of a single instance:

gcloud sql instances describe INSTANCE_NAME

The flags appear in the databaseFlags collection under settings. An instance with no databaseFlags entry at all has never had a flag set and passes the control.

To sweep a project, list the SQL Server instances first and then describe each one. The value is a string in the API output, so a comparison against the number zero will not match:

gcloud sql instances list --filter="databaseVersion:SQLSERVER*" --format="value(name)"

Record the full flag set, not just user connections. A patch replaces every flag at once, so you need the others to write it, and this is the only point in the procedure where they are easy to collect.

How to implement it

Where the flag is set to a limit and the instance should not have one, clear it or set it explicitly. Clearing all flags is the operation that restores user connections to its default:

gcloud sql instances patch INSTANCE_NAME --clear-database-flags

That command removes every other flag as well, and it prompts for confirmation before it restarts the instance.

Where other flags must survive, set them all in one command. The name needs quoting because of the space in it, the same way Google quotes "remote query timeout (s)"=300 in its own examples:

gcloud sql instances patch INSTANCE_NAME \
  --database-flags="user connections"=0,1222=on

Every flag in that command is the complete flag set afterwards. Build the list from the describe output you collected.

Over the Admin API, send a PATCH to the instance resource with the full array:

{"settings": {"databaseFlags": [{"name": "user connections", "value": "0"}]}}

The same substitution rule applies, so the array must list every flag the instance should keep.

Further reading

• Configure database flags for Cloud SQL for SQL Server. The supported flag list, the permitted value for each flag, whether a change restarts the instance, and the gcloud and API syntax for setting and clearing them.