Cannot use Connector with SQL Server if SSL is enforced

[edosrecki]

Bug Description

I guess this should be considered a bug. I know that we do not need to enforce SSL when using a Connector, since it handles secure connection for us. However, on the other hand, with PostgreSQL and MySQL, we can use Connector even when SSL is enforced, so this makes SQL Server an exception where it doesn't work.

I know that Connector returns tedious options with encrypt: false so that connection is not double-encrypted. But perhaps there should be a way for tedious to know that we are already passing secure connection.

Example code

import { AuthTypes, Connector, IpAddressTypes } from '@google-cloud/cloud-sql-connector'
import knex from 'knex'

const clientOpts = await connector.getTediousOptions({
  instanceConnectionName: 'my-project.my-region.my-instance',
  ipType: IpAddressTypes.PUBLIC,
  authType: AuthTypes.PASSWORD,
})

const database = knex({
  client: 'mssql',
  connection: {
    server: '0.0.0.0', # dummy value due to a bug
    user: 'user',
    password: 'password',
    database: 'database',
    options: {
      ...clientOpts,
    },
  }
})

const result = await database.first(database.raw(`'True' AS connected`))

Stacktrace

ConnectionError: Server requires encryption, set 'encrypt' config option to true.
    at /project/node_modules/tedious/src/connection.ts:3251:34
    at processTicksAndRejections (node:internal/process/task_queues:95:5) {
  code: 'EENCRYPT',
  isTransient: undefined
}

How to reproduce

1. Create Cloud SQL Server instance.
2. Check "Allow only SSL connections" checkbox.
3. Connect using above example.

Environment details

• OS: MacOS 11.6
• Node.js version: v18.18.0
• npm version: v9.8.1
• connector version: 1.0.0
• tedious version: 16.4.0

[enocom]

@edosrecki Have you tried with the following?

const clientOpts = await connector.getTediousOptions({
  instanceConnectionName: 'my-project.my-region.my-instance',
  ipType: IpAddressTypes.PUBLIC,
  authType: AuthTypes.PASSWORD,
  // adding these two options
  encrypt: true,
  trustServerCertificate: true 
})

This is likely a known issue where SQL Server requires double encryption with the Connectors, but only when "Allow only SSL connections" is enabled.

[edosrecki]

@enocom Yes that definitely works, I just wanted to point out that it is not possible without double encryption, but I wasn't aware that it is a known issue.

Thanks!

[enocom]

This is related to how SQL Server doesn't have support for Unix sockets -- which is how Postgres and MySQL avoid the problem. They accept unencrypted connections through a Unix socket that's backed by the Proxy server.

In the meantime, I'll make this a documentation issue since we don't own the backend and the change is currently below other higher priority items.