Logo of Trend Micro
Use the Conformity Knowledge Base AI to help improve your Cloud Posture

Configure 'user connections' Flag for SQL Server Database Instances

Trend Cloud One™ – Conformity is a continuous assurance tool that provides peace of mind for your cloud infrastructure, delivering over 1000 automated best practice checks.

Risk Level: Medium (should be achieved)
Rule ID: CloudSQL-020

Ensure that the "user connections" database flag configured for your Google Cloud SQL Server database instances has the optimal value, in accordance with your organization or project guidelines. The "user connections" flag specifies the maximum number of simultaneous user connections that are allowed on an SQL Server database instance. The actual number of user connections allowed also depends on the version of SQL Server that you are using, and also the limits of your application or applications and hardware. SQL Server allows a maximum of 32,767 user connections. Prior to running this conformity rule by the Trend Cloud One™ – Conformity engine, you need to define the "user connections" flag value used within your organization in the rule settings, on your Trend Cloud One™ – Conformity account console.

This rule resolution is part of the Conformity Security & Compliance tool for GCP.

Performance
efficiency
Reliability

If the "user connections" configuration flag is set to a low value, is not going to allow high-traffic applications or processes to handle all the requests. You can increase the number of simultaneous user connections that are allowed on an SQL Server database instance by configuring the "user connections" flag to meet the database workload requirements chosen by your organization. The new limit cannot exceed 32,767 user connections.

Note: Some database flag settings can affect instance availability and/or stability, and eventually remove the SQL Server instance from the Google Cloud SQL Service Level Agreement (SLA).

Audit

To determine if the "user connections" flag set for your SQL Server database instances has the appropriate configuration, perform the following operations:

Using GCP Console

01 Sign in to your Trend Cloud One™ – Conformity account, access the Configure 'user connections' Flag for SQL Server Database Instances rule, and note the value configured for the "user connections" database flag.

02 Sign in to the Google Cloud Management Console.

03 Select the Google Cloud Platform (GCP) project that you want to access from the top navigation bar.

04 Navigate to Cloud SQL Instances dashboard at https://console.cloud.google.com/sql/instances.

05 Click inside the Filter box, select Type and SQL Server <version>, then press Enter to list only the SQL Server database instances provisioned for the selected GCP project.

06 Click on the name (ID) of the database instance that you want to examine.

07 In the navigation panel, select Overview to access the configuration details available for the selected SQL Server instance.

08 In the Configuration section, under Database flags, check the value set for the user connections database flag. If user connections is not available in the Database flags list or the flag value is lower than the one identified at step no. 1, the "user connections" flag configuration for the selected Google Cloud SQL Server database instance is not compliant.

09 Repeat step no. 6 – 8 to check the "user connections" flag value for other SQL Server instances available within the selected project.

10 Repeat steps no. 3 – 9 for each project deployed in your Google Cloud account.

Using GCP CLI

01 Sign in to your Trend Cloud One™ – Conformity account, access the Configure 'user connections' Flag for SQL Server Database Instances rule, and note the value configured for the "user connections" database flag.

02 Run projects list command (Windows/macOS/Linux) with custom query filters to list the IDs of all the GCP projects available within your Google Cloud account: 

gcloud projects list
  --format="table(projectId)"

03 The command output should return the requested GCP project identifiers: 

PROJECT_ID
cc-ms-web-project-123123
cc-ms-data-project-123123

04 Run sql instances list command (Windows/macOS/Linux) with custom filtering to describe the name of each SQL Server database instance provisioned for the selected Google Cloud project: 

gcloud sql instances list
  --project cc-ms-web-project-123123
  --filter='DATABASE_VERSION:SQLSERVER*'
  --format="(NAME)"

05 The command output should return the requested database instance name(s): 

NAME:
cc-web-sql-server-instance
cc-app-sql-server-instance

06 Run sql instances describe command (Windows/macOS/Linux) using the name of the SQL Server database instance that you want to examine as the identifier parameter and custom query filters to describe the "user connections" flag configuration value set for the selected database instance: 

gcloud sql instances describe cc-web-sql-server-instance
  --format=json | jq '.settings.databaseFlags[] | select(.name=="user connections")|.value'

07 The command output should return the requested flag configuration value: 

"150"

If the value returned by the command output is lower than the one defined by your organization and identified at step no. 1 or the sql instances describe command does not produce an output, the "user connections" flag configuration for the selected Google Cloud SQL Server database instance is not compliant.

08 Repeat steps no. 6 and 7 to verify the "user connections" flag value for other SQL Server instances created for the selected project.

09 Repeat steps no. 3 – 7 for each project created within your Google Cloud account.

Remediation / Resolution

To configure the "user connections" database flag in accordance with your organization and project guidelines, perform the following operations:

Using GCP Console

01 Sign in to the Google Cloud Management Console.

02 Select the Google Cloud Platform (GCP) project that you want to access from the top navigation bar.

03 Navigate to Cloud SQL Instances dashboard at https://console.cloud.google.com/sql/instances.

04 Click inside the Filter box, select Type and SQL Server <version>, then press Enter to display only the SQL Server database instances provisioned for the selected GCP project.

05 Click on the name (ID) of the database instance that you want to reconfigure.

06 In the navigation panel, select Overview to access the configuration details available for the selected SQL Server instance.

07 Choose Edit from the dashboard top menu to modify the instance configuration.

08 In the Customize your instance section, choose Flags and parameters to expand the panel with the database flags and parameters configured for the selected SQL Server instance.

09 Find the user connections flag and type the appropriate value (integer) in the Value configuration box, in accordance with your organization and project guidelines. If the flag has not been set on the selected instance before, choose ADD FLAG, select the user connections flag from the Choose a flag dropdown list, and set its value (limit) accordingly. Choose DONE to close the panel.
IMPORTANT: Configuring "user connections" flag restarts the selected database instance.

10 Choose SAVE to apply the configuration changes. In the Changes require restart confirmation box, choose SAVE AND RESTART.

11 Repeat steps no. 5 – 10 to configure the specified flag for other SQL Server database instances available within the selected GCP project.

12 Repeat steps no. 2 – 11 for each project deployed in your Google Cloud account.

Using GCP CLI

01 Run sql instances patch command (Windows/macOS/Linux) using the name of the SQL Server database instance that you want to reconfigure as the identifier parameters, to set the appropriate value (limit) for the "user connections" database flag configured for the selected SQL Server instance, in accordance with your organization and project guidelines: 

gcloud sql instances patch cc-web-sql-server-instance
  --database-flags "user connections=4500"

IMPORTANT: Configuring "user connections" flag restarts automatically the selected database instance.

02 Type Y to confirm the database configuration change: 

The following message will be used for the patch API method.

{"name": "cc-web-sql-server-instance", "project": "cc-ms-web-project-123123", "settings": {"databaseFlags": [{"name": "user connections", "value": "4500"}]}}

WARNING: This patch modifies database flag values, which may require your instance to be restarted. Check the list of supported flags - https://cloud.google.com/sql/docs/sqlserver/flags - to see if your instance will be restarted when this patch is submitted.

Do you want to continue (Y/n)? Y

03 The output should return the sql instances patch command request status: 

Patching Cloud SQL instance...done.

Updated
[https://sqladmin.googleapis.com/sql/v1beta4/projects/cc-ms-web-project-12312/instances/cc-web-sql-server-instance].

04 Repeat steps no. 1 – 3 to configure the specified flag for other SQL Server database instances provisioned for the selected GCP project.

05 Repeat steps no. 1 – 4 for each project created within your Google Cloud account.

References

Publication date Apr 8, 2022

Related CloudSQL rules