[QUESTION] Support for Azure SNI connection through tunnel

[OverHash]

Question I'm connecting to a Azure MS SQL database through the use of a GCP IAP tunnel. Our IAP tunnel is configured via socat to forward requests from port 5432 to xxxx.database.windows.net:1433. This connection works fine.

On local dev machines, I am trying to use tedious to connect to the database (by first going through the iap tunnel), with Azure Active Directory Service Principle credentials. I suspect that somewhere in the secret exchange done in Tedious, it fails to authenticate (maybe due to an invalid SNI?).

Here's the config that works:

new tedious.Connection({
  authentication: {
    type: 'azure-active-directory-service-principal-secret',
    options: {
      clientId: process.env.AZURE_CLIENT_ID,
      clientSecret: process.env.AZURE_CLIENT_SECRET,
      tenantId: process.env.AZURE_TENANT_ID,
    } satisfies tedious.ConnectionAuthentication['options'],
  },
  options: {
    database: process.env.DATABASE_NAME,
    port: 5432,
    encrypt: true,
    trustServerCertificate: true,
    serverName: 'xxxx.database.windows.net',
  },
  server: process.env.DATABASE_HOST!,
});

this only works once I edit /etc/hosts with

127.0.0.1  xxxx.database.windows.net

because the SNI matches (as I understand) which prevents a cert refusal somewhere in the chain. This works because (to my understanding) tedious will perform the request to xxxx.database.windows.net, and my OS will route that to 127.0.0.1:5432, which goes through an iap tunnel and out to xxxx.database.windows.net from an authenticated IAP host. Since the original request was for xxxx.database.windows.net, all cert checks match.

Problem

It would be nice if I didn't have to edit my /etc/hosts to do this mapping, and instead tedious could somehow perform this authentication handshake with Azure before then using my local tunnel for all database queries. I am wondering if this is currently possible, or where the blocking issue is?

For example, if I adjust the connection to

new tedious.Connection({
  authentication: {
    type: 'azure-active-directory-service-principal-secret',
    options: {
      clientId: process.env.AZURE_CLIENT_ID,
      clientSecret: process.env.AZURE_CLIENT_SECRET,
      tenantId: process.env.AZURE_TENANT_ID,
    } satisfies tedious.ConnectionAuthentication['options'],
  },
  options: {
    database: process.env.DATABASE_NAME,
    port: 5432,
    encrypt: true,
    trustServerCertificate: true,
    serverName: 'xxxx.database.windows.net',
  },
  -server: process.env.DATABASE_HOST!,
  +server: "localhost", // or "127.0.0.1"
});

I will get the following output:

ConnectionError: Cannot open server "localhost" requested by the login.  The login failed.
    at Login7TokenHandler.onErrorMessage (/project/node_modules/.pnpm/[email protected]/node_modules/tedious/lib/token/handler.js:186:19)
    at Readable.<anonymous> (/project/node_modules/.pnpm/[email protected]/node_modules/tedious/lib/token/token-stream-parser.js:19:33)
    at Readable.emit (node:events:518:28)
    at addChunk (node:internal/streams/readable:559:12)
    at readableAddChunkPushObjectMode (node:internal/streams/readable:536:3)
    at Readable.push (node:internal/streams/readable:391:5)
    at nextAsync (node:internal/streams/from:194:22)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
  code: 'ELOGIN'
}

but I see on my iap tunnel (via socat logging) that some requests have indeed been forwarded successfully.

---

I'm wondering if anyone has experienced this, or has any ideas for how to resolve this such that developers don't have to modify /etc/hosts to get the requests working?

[OverHash]

I added

tediousConnection.on('debug', (txt) => logDebug(txt));
tediousConnection.connect((e) => {
  if (e) {
    logError(e.message);
  }
});
tediousConnection.on('connect', (e) => {
  logDebug('connected!');
  if (e) {
    logError(e.message);
  }
});

to try debug some of the info exchanged by tedious and see the following in the logs:

• State change: Initialized -> Connecting
• connected to localhost:5432
• PreLogin - version:18.6.1.0, encryption:0x01(ON), instopt:0x00, threadId:0x00000000, mars:0x00(OFF)
• State change: Connecting -> SentPrelogin
• Sent type:0x12(PRELOGIN), status:0x01(EOM), length:0x0035, spid:0x0000, packetId:0x01, window:0x00 ...
• Received type:0x04(TABULAR_RESULT), status:0x01(EOM), length:0x0031, spid:0x0000, packetId:0x01, window:0x00 ...
• PreLogin - version:12.0.5751.0, encryption:0x01(ON), instopt:0x00, threadId:0x00000000, mars:0x00(OFF)
• State change: SentPrelogin -> SentTLSSSLNegotiation
• Sent type:0x12(PRELOGIN), status:0x01(EOM), length:0x0123, spid:0x0000, packetId:0x01, window:0x00 ... (this seems to include my xxxx.database.windows.net in there)
• Received type:0x12(PRELOGIN), status:0x00(), length:0x1000, spid:0x0000, packetId:0x00, window:0x00 (includes Microsoft Azure RSA TLS Issuing CA 040 and a reference to http://www.microsoft.com/pkiops/certs/Microsoft%20Azure%20RSA%20TLS%20Issuing%20CA%2004%20-%20xsign.crt as well as www.digicert.com)
• Received type:0x12(PRELOGIN), status:0x01(EOM), length:0x02C1, spid:0x0000, packetId:0x00, window:0x00
• Sent type:0x12(PRELOGIN), status:0x01(EOM), length:0x00A6, spid:0x0000, packetId:0x01, window:0x00
• Received type:0x12(PRELOGIN), status:0x01(EOM), length:0x003B, spid:0x0000, packetId:0x00, window:0x00
• TLS negotiated (ECDHE-RSA-AES256-GCM-SHA384, TLSv1.2)
• Login7 - TDS:0x74000004, PacketSize:0x00001000, ClientProgVer:0x00000000, ClientPID:0x00016896, ConnectionID:0x00000000 Flags1:0xF0, Flags2:0x00, TypeFlags:0x00, Flags3:0x18, ClientTimezone:-780, ClientLCID:0x00000409 with Hostname:'[my desktop hostname]', Username:'undefined', Password:'undefined', AppName:'Tedious', ServerName:'localhost', LibraryName:'Tedious' Language:'us_english', Database:'xxxx', SSPI:'undefined', AttachDbFile:'undefined', ChangePassword:'undefined'
• State change: SentTLSSSLNegotiation -> SentLogin7Withfedauth
• Sent type:0x10(LOGIN7), status:0x01(EOM), length:0x00EA, spid:0x0000, packetId:0x01, window:0x00 with a packet that looks to be including my desktop host name, tedious, and localhost, followed by tedious us.english and the database xxxx to connect to
• Received type:0x04(TABULAR_RESULT), status:0x01(EOM), length:0x00CA, spid:0x0000, packetId:0x01, window:0x00 with I cannot open server "localhost" requested by the login. The login failed.

so it looks like it might be somewhere in the login step there? Will try compare with the working example now

[OverHash]

I think the incorrect packet there is the Login7 packet which sends ServerName:'localhost' instead of ServerName: 'xxxx.database.windows.net. Is there any way to configure that?

https://github.com/tediousjs/tedious/blob/ebb023ed90969a7ec0e4b036533ad52739d921f7/src/connection.ts#L2487

in the mean time, I have patched it so that connectOnPort in connection.ts uses host: "localhost" in connectOpts. If there was a way to set a proxy or make this behavior more configurable, it would improve robustness.

[laurayco]

https://github.com/tediousjs/tedious/blob/master/examples/custom-connector.js

Have you seen this? You might be able to achieve what you're describing by changing the 'server' property and the host found on line 15. I worry that this repo might be abandoned tbh, which is unfortunate as this library is referenced by microsoft's docs.