SQL Server Remove Access Flag Should Be Off

More Info:

The remote access option controls the execution of stored procedures from local or remote servers on which instances of SQL Server are running. This default value for this option is 1. This grants permission to run local stored procedures from remote servers or remote stored procedures from the local server. To prevent local stored procedures from being run from a remote server or remote stored procedures from being run on the local server, this must be disabled. The Remote Access option controls the execution of local stored procedures on remote servers or remote stored procedures on local server. Remote access functionality can be abused to launch a Denial-of-Service (DoS) attack on remote servers by off-loading query processing to a target, hence this should be disabled. This recommendation is applicable to SQL Server database instances.

Risk Level

Low

Address

Reliability, Security

Compliance Standards

• CIS GCP
• CIS GCP 2.0.0
• Cloudanix Best Practice

Triage and Remediation

Remediation

Remediation

Using Console

To remediate the "SQL Server Remove Access Flag Should Be Off" misconfiguration for GCP using the GCP console, you can follow these steps:

1. Log in to the GCP console and select the project that contains the SQL Server instance.

2. Navigate to the Cloud SQL instances page and select the instance that you want to remediate.

3. Click on the "Edit" button to edit the instance configuration.

4. Scroll down to the "Authorization" section and ensure that the "Allow only SSL connections" option is selected.

5. Under the "Authorized networks" section, ensure that only the necessary IP addresses or ranges are listed.

6. Scroll down to the "Flags" section and ensure that the "remove_access_flag" option is set to "off".

7. Click on the "Save" button to save the changes.

8. Verify that the misconfiguration has been remediated by running a vulnerability scan or security audit on the SQL Server instance.

By following these steps, you can remediate the "SQL Server Remove Access Flag Should Be Off" misconfiguration for GCP using the GCP console.

Using CLI

The SQL Server Remove Access Flag should be turned off to ensure that the data is not deleted accidentally. To remediate this issue in GCP using GCP CLI, follow these steps:

1. Open the Cloud Shell in the GCP Console.
2. Run the following command to list all the SQL instances in the project:

gcloud sql instances list

3. Identify the instance that has the Remove Access Flag turned on.
4. Run the following command to update the instance configuration:

gcloud sql instances patch [INSTANCE_NAME] --database-flags log-bin-trust-function-creators=on

Note: Replace [INSTANCE_NAME] with the name of the instance that has the Remove Access Flag turned on.

5. Verify that the Remove Access Flag has been turned off by running the following command:

gcloud sql instances describe [INSTANCE_NAME]

Note: Replace [INSTANCE_NAME] with the name of the instance that has been updated.

6. Check the configuration settings to ensure that the Remove Access Flag is set to off.

By following these steps, you can remediate the SQL Server Remove Access Flag turned on issue in GCP using GCP CLI.

Using Python

To remediate the SQL Server Remove Access Flag Should Be Off misconfiguration for GCP using python, you can follow the below steps:

1. First, you need to authenticate to the GCP project using the Google Cloud SDK. You can use the below command to authenticate:

gcloud auth login

2. Next, you need to install the google-cloud-sql python library. You can use the below command to install:

pip install google-cloud-sql

3. Once the library is installed, you can use the below python code to remediate the misconfiguration:

from google.cloud import sql_v1beta4
from google.oauth2 import service_account

# Set the SQL instance details
project_id = 'your_project_id'
instance_id = 'your_instance_id'
database_id = 'your_database_id'

# Set the service account details
credentials = service_account.Credentials.from_service_account_file('path/to/service_account.json')

# Create the SQL client
client = sql_v1beta4.CloudSqlInstancesServiceClient(credentials=credentials)

# Get the instance details
instance = client.get(project=project_id, instance=instance_id)

# Get the database details
database = client.get_database(project=project_id, instance=instance_id, database=database_id)

# Check if the remove_access_flag is set to true
if database.sqlserver_config.remove_access_flag:
    # Update the remove_access_flag to false
    database.sqlserver_config.remove_access_flag = False
    
    # Update the database configuration
    update_mask = {"paths": ["sqlserver_config.remove_access_flag"]}
    client.update_database(project=project_id, instance=instance_id, database=database_id, database=database, update_mask=update_mask)
    
    print("Remove Access Flag has been turned off successfully!")
else:
    print("Remove Access Flag is already turned off!")

In the above code, you need to replace the project_id, instance_id, database_id, and path/to/service_account.json with the actual values for your GCP project, SQL instance, database, and service account file path respectively.

This code will check if the remove_access_flag is set to true for the database and if it is, it will update the flag to false. If the flag is already false, it will print a message saying that the flag is already turned off.

Using Terraform

resource "google_sql_database_instance" "SQLSERVER_INSTANCE" {
  name             = "SQLSERVER_INSTANCE_NAME"          # replace with your instance name
  database_version = "SQLSERVER_2019_STANDARD"          # use your actual SQL Server version
  region           = "GCP_REGION"                       # e.g. us-central1

  settings {
    tier = "db-custom-2-7680"                           # replace with your machine tier

    # Ensure the SQL Server remote access flag is disabled
    database_flags {
      name  = "remote access"
      value = "0"
    }

    # ...any other existing settings (ip_configuration, backup_configuration, etc.)
  }
}

This changes only a database flag and will restart the Cloud SQL instance but will not force its replacement.

After updating your Terraform and running terraform plan, you should see either:

• a new database_flags block with name: "remote access" and value: "0" being added, or
• an existing database_flags entry for "remote access" changing its value from 1 (or another non‑zero) to 0.

Additional Reading:

• https://cloud.google.com/sql/docs/sqlserver/flags