Bourges Cathedral

Excel with Google Cloud SQL Server

Google has added the SQL server as an option to the Google Cloud SQL service. In this article, I will explore how to set up a Google Cloud SQL server I received a question what was the best way to move a Cloud BI server to the GCP Cloud, and run VB Script from an Excel against that server.

Let’s take a look at how to set up an SQL server instance with Cloud SQL.

Create a Cloud SQL instance

Navigate to SQL in your GCP project, enable the SQL service and select create an instance, you should see something like this.

Fill in the details, select the type of server and create the instance. I called my instance excel-queries. You should see something like this after you created your instance.

Open up your instance and select connections

SQL Connections

In my case I was connected over the public internet, I checked the Public IP. For Public IP to work, you need to whitelist the IP address – you can check your IP Address with this tool https://www.whatsmyip.org/ and the IP address it shows add it to the Authorized Networks and when done click save.

SSL Connections

You can choose to have a secured or unsecured connection, don’t leave anything to a chance to choose the secure connections.

Azure Data Studio

Now you can test your instance, I used Azure Data Studio on my MacBook to connect to the SQL Server on Google SQL.

Download and install Azure Data Studio

Follow the instructions on the Azure Data Studio website on how to setup.

Make connections

Now you can make a connection from Azure Data Studio to your Google Cloud SQL server if it does not connect, check the authorized network that your IP address was set up correctly.

Excel

The reason to why I wanted to use the SQL server on Google SQL, was to be able to connect a Excel sheet that had Visual Basic code that has a connection to connect to a SQL server to push data to the SQL server and then make a SQL query to process the data and store it back in the Excel.

Depending on your current Excel sheet setup, you may need to install the Microsoft OLE DB Driver for SQL Server from here – https://docs.microsoft.com/en-us/sql/connect/oledb/download-oledb-driver-for-sql-server?view=sql-server-ver15

Now can you make a connection from your Excel sheet, if you get an error please check that you accept a secure connection?

Getting information about your server certificate

You can get information about your Google Cloud SQL server certificate, such as when it expires or what level of encryption it provides. By using this command

gcloud beta sql ssl server-ca-certs list --instance=[INSTANCE_NAME]

from your command line.

Excel VB Code Connection Strings

When you go through your Excel VB code you need to check what your Connection String looks like in your VB code. I had this in mind.

sLink = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=FR;Data Source=BI-SSIS"

To check the different Connection Strings – https://www.connectionstrings.com/

My VB code snippet only works for Excel on Microsoft platform, in my case I switch over to Microsoft OS to do the Excel process.

I had to change my connection string in my VB code to

sLink = "Provider=MSOLEDBSQL;Server=myServerAddress;Database=myDataBase;UID=myUsername;PWD=myPassword;"

as my VB code was written of an OLE DB driver, you can read more here – https://docs.microsoft.com/en-us/sql/connect/oledb/when-to-use-oledb-driver-for-sql-server?view=sql-server-ver15

and the connection strings here for OLE DB driver – https://www.connectionstrings.com/ole-db-driver-for-sql-server/

Finishing Up

I hope this can help you, regardless of how you set things up. You could use the Azure SQL server to connect your Excel file to, it does not need to be Google. If this was of any help, let me know, in the comment section below.


Posted

in

, , ,

by

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *