How do I connect to Google Cloud SQL Server using C#?

Question

I just set up a SQL Server on the "Google Cloud Platform." I created a database and tables there. I used Microsoft's "SQL Server Management Studio" (SSMS) to connect and create the database and tables. That all worked. I set up a proxy address 127.0.0.1:1443 according to Google's Cloud SQL instructions. That all worked.

However, what I want to do is connect to this remote server using a C# application. I have no problem using the C# program to connect to the SQL Server on my machine, but I can't figure out how to configure to connect to the one on the remote one on the Google Cloud Platform.

For my code I'm using the System.Data.SqlClient library.

My connection string is below. The code below is hopefully enough to tell you what is going on. When I call loadInfoFromDatabase(), it fails when it hits `connection.Open(). I wrote "FAILS HERE" next to it in the code below.

The connection string is a bit of a guess after trying different things. Any help would be appreciated.

Thanks!

public string _connectionString = @"Server=127.0.0.1:1443;Database=MarsDatabase;User Id=sqlserver;Password=AAABBBCCC";

private void loadInfoFromDatabase()
{
    string firstName, middleName, lastName, email, password, assignedTables;
    string connectionString;
    SqlDataReader dataReader;

    connectionString = _connectionString;
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open(); //FAILS HERE... 

        SqlCommand command = connection.CreateCommand();
        SqlTransaction transaction;

        // Start a local transaction.
        transaction = connection.BeginTransaction("loadInfoFromDatabse");

        // Must assign both transaction object and connection
        // to Command object for a pending local transaction
        command.Connection = connection;
        command.Transaction = transaction;

        try
        {                  
            command.CommandText = "Select * From Info";

            // Attempt to commit the transaction.
            dataReader = command.ExecuteReader();
           
            while (dataReader.Read())
            {
               ...
            }

            dataReader.Close();
            command.Dispose();
            // connection.Close();
            transaction.Commit();
            Console.WriteLine("Selection from managers table worked.");
        }
        catch (Exception ex)
        {
           
        }
    }
}

You state on the remote one on the Google Cloud Platform. What is the service you are using (Compute Engine, App Engine, etc)? Each service has a guide for setting up Cloud SQL connections. — John Hanley
@John Hanley -- I'm not sure. I'm new to this. I assumed the "SQL-Server" was the service. I'm in over my head on this one. I did come across "Compute Engine" when I was setting up, but I didn't select it. I'm looking at the Google Cloud Platform console now, and I don't see anything that stands out. — John Alway
@John Hanley -- I don't understand the question, which is likely part of my problem. :-) There is a proxy, Cloud SQL Auth proxy, that is supposed to allow you to connect via the local IP, 127.0.0.1. It works for Microsoft's SSMS. I'm using C# in Visual Studio on my local Windows 10 machine. — John Alway
Review @Dondi's answer. In addition, you will need to whitelist your Windows 10 public IP address so that you can connect to Cloud SQL unless you have configured the Cloud SQL Auth Proxy. cloud.google.com/sql/docs/sqlserver/configure-ip AND cloud.google.com/sql/docs/sqlserver/sql-proxy — John Hanley

Dondi Dondi · Accepted Answer · 2021-06-24T03:48:23

I see that you're using Cloud SQL Proxy to connect to the SQL Server instance using TCP.

The problem is with your connection string because you're missing the TCP prefix. By looking at Server on ConnectionString property you can see the description where:

The TCP format must start with the prefix "tcp:". (ex. server=tcp:servername, portnumber)

To fix the issue, change your connection string to:

@"Server=tcp:127.0.0.1,1443;Database=MarsDatabase;User ID=sqlserver;Password=AAABBBCCC";

How do I connect to Google Cloud SQL Server using C#?

1 Answers