Connecting to MS SQL on Compute in GCP Using Cloud IAP

By Bryan Nicholson

Note: This article assumes that you have the Google Cloud SDK installed on whichever OS you are using and that it is already configured to connect to your project with your credentials. (Windows Cloud SDK Quickstart | MacOS Cloud SDK Quickstart)

mssql img01
High-Level View of Cloud IAP

In a cloud world, where zero-trust is a must and context is key, using Cloud IAP to secure your cloud resources is vital. There are several reasons to use Cloud IAP as opposed to a traditional VPN. First and foremost, it’s secure⁠— it works, and it’s never been compromised (view the latest round of discovered vulnerabilities with traditional VPNs here). Some other reasons include, but are not limited to: reducing the attack surface of your environment; building context/intelligence around access; and providing a lightweight & secure way for your internal staff to manage infrastructure from anywhere. Cloud IAP was initially developed as part of Google’s BeyondCorp model, as a way to allow their own employees to connect to internal resources from anywhere (permitting the context made sense). Cloud IAP essentially tunnels/forwards TCP traffic via HTTPS. As a result, it allows you to connect securely from anywhere.

Recently, I had a client ask me how to set up a VPN in GCP, at which point, I directed them towards Cloud IAP, as I typically do. They leveraged the GitHub IAP RDC plugin to allow RDP connections to the SQL server instance itself. However, they had several people who prefer to manage their MS SQL environment via SQL Management Studio, installed locally instead. They were wondering how to leverage Cloud IAP in this use case, in order to uniformly secure access to their SQL environment using Cloud IAP.

Presuming you have already initially configured IAP for your GCP project (if you haven’t, there are many amazing walkthroughs available), here’s how to allow secure access via SQL Management Studio, Azure Data Studio, or really any other standard management software. There are only a few simple steps involved, and Cloud IAP can be set up and access to your environment secured in a matter of minutes.

Firewall Rules

To keep things simple, in this use case I leveraged network tags. In higher security environments though, you would ideally use firewall rules based on service accounts.

Create a new firewall rule for inbound traffic and specify the following:

  1. Name: Select a name that identifies the purpose according to your convention
  2. Network: Select the network your SQL instances live in. If they live in multiple networks, you’ll have to create separate firewall rules.
  3. Direction of Traffic: Ingress
  4. Action on Match: Allow
  5. Targets: Select ‘Specified Target Tags’ from the drop-down menu
  6. Target tags: Enter the tag used for your MS SQL instances (in my case, ‘mssql’). If you haven’t entered tags on your instances already, you’ll have to edit your compute instances running MS SQL and add the network tag you specify here.
  7. Source Filter: Select ‘IP Ranges’ from the drop-down menu
  8. Source IP Ranges: 35.235.240.0/20 (this is the Cloud IAP CIDR block)
  9. Protocols and Ports: Select ‘Specified protocols and ports’, then check the box next to ‘TCP’ and enter 1433 (or 1433,3389 to enable RDP access also via tunnel) next to it
mssql img02

Leave any other options at their default and select ‘Save’. This will allow the Cloud IAP API to access your MS SQL instances.

Grant Permissions to Access the IAP-Secured Tunnel

Next, we will need to grant access to users to access the secured IAP resources to tunnels. One of the great things about IAP is that you have the option to granularly grant access to users on a per-instance basis. Ideally, you will grant users access via a group they have membership in⁠— this would be best practice and make permission management easier and more scalable.

  1. Click the menu in the top left
  2. Go down to security
  3. Select ‘Identity-Aware Proxy’
  4. Click the ‘SSH and TCP Resources’ tab
  5. Select the checkboxes next to the resources you would like to grant access to (You can choose to do this by selecting all resources, individual resources, or zonal/regional resources⁠— whatever makes sense in your environment. Simply check the boxes next to the resources you want to apply the permissions to)
  6. In the frame on the right-hand side, click ‘Add Member’
  7. New Members: Enter the Google group email address for users (or individual user’s email, but using groups would be best practice)
  8. Select a Role: Click the drop-down under ‘Select a Role’, then select ‘Cloud IAP’ and finally, select ‘IAP-secured Tunnel User’.
  9. Condition (optional, but recommended): You can choose to begin building context around access by selecting ‘Add Condition’ under Condition. The possibilities are pretty extensive as far as the context you can select. To keep things simple for now, simply restrict access to port 1433 (and optionally also 3389 to enable RDP access). After clicking ‘Add Condition’, enter a title to identify the condition, then select the ‘Condition Editor’ tab. In the text box, enter the following text, without the quotes, then click ‘Save’: ‘destination.port == 1433 || destination.port == 3389’
mssql img03
View of Condition Limiting IAP Tunnel Access to Specified Destination Ports

Essentially, at this point, you have set up the Firewall policies and granted the appropriate Cloud IAP permissions necessary to access your MS SQL instances via cloud IAP. The only thing left is building the tunnel/TCP listener on your local computer.

Building the Tunnel From Your Local Machine

This is actually incredibly easy, given that you have the Cloud SDK installed and configured on your computer.

Note: If you manage several projects via your account, set the project that contains the resources you want to connect to first by running: gcloud config set project <project-id>

There is only one command to build the Cloud IAP tunnel from the local machine and start the TCP listener locally.

Open the cloud shell on Windows or Terminal on MacOS (or whichever OS you’re using) and type the following:

gcloud compute start-iap-tunnel <instancename> 1433 — zone <zoneinstanceisin> — local-host-port=localhost:1433

Replace <instancename> with the instance name you’d like to tunnel to. Replace <zone> with the zone that the instance resides in. If you would like to build a tunnel to a different port, simply replace 1433 with the desired port.

— local-host-port=localhost:1433 is the command that sets the local listener which will receive traffic, then push it through the tunnel to the set endpoint.

mssql img04
View of Active Tunnel/TCP Listener

Note: The tunnel does not tear itself down. When you are done with your session, simply stop the listener (ctr + c on Windows or MacOS) or close the instance of cloud shell with the listener. This will effectively terminate your Cloud IAP tunnel.

That’s all! To connect to your remote instance, point your management software at the localhost (127.0.0.1) on port 1433.

mssql img05
Connecting to MS SQL on GCP Compute Instance Via Local Listener/Cloud IAP Tunnel

Congrats! You’ve now secured connections to your MS SQL instances on GCP Compute via Cloud IAP, a zero-trust, context-aware method!

Final Note: An important part of cloud security is audit logging. You can enable logging of access or denials and details for Cloud IAP by enabling audit logging for the Identity Aware Proxy API. These logs are then viewable in Stackdriver logging > resource being accessed > log type: data_access … the IAP API entries will have detailed information regarding IAP access.If you’re comfortable within CLI, it is quick and easy to enable audit logging for IAP API via command line and applying a short YAML file (view instructions here). If you don’t feel that comfortable in the CLI, this can also be accomplished using the GUI. To configure via the Console/GUI, follow these instructions to enable audit logging for Cloud Identity-Aware Proxy API.

Google Cloud Logo Icon

THE GCP VS AWS DEBATE

We spoke to dozens of customers who shared their experiences with both cloud providers. The overwhelming trends tell a big story. Download the eBook to learn more.

Solve not just for today but for what's next.

We'll help you harness the immense power of Google Cloud to solve your business challenge and transform the way you work.

Scroll to Top