Search code examples
sql-serverssliisconnection-stringgoogle-cloud-sql

Connect to SQL Server using a pem file, username and password

I have a SQL Server (which is a Google cloud SQL Server). The connection with it needs to be ssl encrypted and authenticated with a username and password.

I can get the .pem file for the ssl certificate :

enter image description here

I also have the username, password, and ip of the server. In the SqlClient connection string syntax I was able to find the flags defining the username (uid), password (pwd) and ip (Data Source). But I can't find how to place the .pem file.

Does the .pem file not belong in the connection string? If so, how do I use it ?

Thanks,
nimrod

Edit: I tried opening the connection with trustServerCertificate=true, but still got error: 40 - Could not open a connection to SQL Server.

Solution:
The SSL wasn't the issue. Adding the TrustServerCertificate and Encrypt flags should have allowed SSL. With these flags the client just accepts any certificate from the sever, and proceeds with the SSL handshake.
My issue was that I didn't add the client network to the trusted networks list for the cloud SQL. This is apparently a requirement even though the cloud SQL is set to us a public IP. I added the 0.0.0.0/0 network as trusted, and the connection worked.

Solution

  • Does the .pem file not belong in the connection string?

    No.

    The client demands protocol encryption using the Encrypt connection string option. If the server's certificate is not trusted by the client, you either need to install the certificate on the client or opt to TrustServerCertificate in the connection string.