MySQL and SQL Server wire protocols

Check what is bound, not what is configured

The available flag in /v1/capabilities means configuration is complete. It is a spawn precondition, not an observed bound port. A listener can report available: true and still have refused to bind - which is exactly what the two refusals below do. So check the socket, then check a real login.

# 1. Does the tenant report the listener as CONFIGURED?
#    The families sit at the TOP level of the payload, not under a wrapper.
curl -s "https://$OC_HOST/v1/capabilities" \
  | jq '.mysql_wire_dialect.available, .mssql_tds_dialect.available'

# 2. Is a socket actually LISTENING?
#    (1) can say "configured" while (2) refused to bind, so run both -
#    and give the port scan a control, or a silent "no" proves nothing.
nc -vz $OC_SQL_HOST 443      # control: this MUST answer
nc -vz $OC_SQL_HOST 3306     # MySQL wire
nc -vz $OC_SQL_HOST 1433     # TDS
# If the control line fails too, your path is blocked and you have
# measured your own network, not our listener.

# 3. Prove it end to end with a real login, not a port scan.
mysql --host=$OC_SQL_HOST --port=3306 --user=$OC_DB_USER \
      --ssl-mode=REQUIRED -e "SELECT 1"

There is no default port. Each listener binds whatever address it is configured with, and we follow the convention each client expects - 3306 for the MySQL wire, 1433 for TDS - so a client's own defaults usually need no override. The host and port your tenant is actually published on are the ones we give you when the listener is enabled. Use those, not the examples on this page.

Credential models, and which one your tenant is on

Both listeners support two models, and the choice is not free - it is decided by whether your tenant uses database RBAC.

Two refusals you should expect, and what they mean

These are designed behaviour, not faults. The engine refuses to bind a listener whose rules it could not enforce, rather than binding one that quietly serves more than it should.

1. A shared credential against a tenant that has RBAC:

DENIED: this tenant has database RBAC configured on N object(s) (...).
ANY grant counts

Remedy: switch the listener to per-user login. Any grant counts - one grant on one object is enough to make a shared credential inadmissible, and so does any row-level security policy; a row policy is not the lighter case. Note what that buys and what it costs: the session becomes enforced, and read-only.

2. Per-user login against a tenant with no usable database user:

DENIED: per-user login is configured but this tenant has NO enabled
database user with a password verifier, so no session could ever be
admitted. Refusing to bind.

Remedy: create at least one enabled database user that has a password verifier.

-- Give the tenant at least one ENABLED database user WITH a password
-- verifier, and the per-user listener has something it can admit.
CREATE USER app_service WITH PASSWORD 'generate-a-real-secret-here';
GRANT SELECT, INSERT, UPDATE ON invoices TO app_service;

MySQL wire protocol

Point any client that speaks the MySQL wire protocol at $OC_SQL_HOST:3306, authenticate with your tenant's shared or per-user credential, and issue SQL.

Worked example - Sequelize 6.37 over mysql2 3.24

// Verified against the engine with Sequelize 6.37 on mysql2 3.24.
import { Sequelize, DataTypes } from "sequelize";

const sequelize = new Sequelize(
  process.env.OC_DATABASE,
  process.env.OC_DB_USER,
  process.env.OC_DB_PASSWORD,
  {
    host: process.env.OC_SQL_HOST,
    port: 3306,
    dialect: "mysql",
    dialectModule: require("mysql2"),
    dialectOptions: {
      // Required in practice. See "TLS is not optional here" below.
      ssl: { minVersion: "TLSv1.2", rejectUnauthorized: true },
    },
    // The engine cannot reflect a schema yet, so DEFINE every model
    // explicitly and never call sequelize.sync() to discover one.
    define: { timestamps: false, freezeTableName: true },
  },
);

const Invoice = sequelize.define("invoices", {
  id: { type: DataTypes.INTEGER, primaryKey: true },
  customer_ref: DataTypes.STRING,
  amount_cents: DataTypes.INTEGER,
  status: DataTypes.STRING,
});

await sequelize.authenticate();

// Single-table reads and writes work, transactions included.
const tx = await sequelize.transaction();
try {
  await Invoice.create(
    { id: 4101, customer_ref: "ACME-77", amount_cents: 129900, status: "open" },
    { transaction: tx },
  );
  await Invoice.update({ status: "settled" }, { where: { id: 4101 }, transaction: tx });
  await tx.commit();
} catch (err) {
  await tx.rollback(); // Rollback is honoured; the row does not appear.
  throw err;
}

const open = await Invoice.findAll({
  where: { status: "open" },
  order: [["amount_cents", "DESC"]],
  limit: 50,
});

What this does not do

Measured against the real engine, on a listener using the shared credential model: a full ORM connects, authenticates, and runs a complete single-table workload - reads, writes, and transactions that both commit and roll back. On a per-user listener the writes in that sentence do not happen at all - see below. Two further things it cannot do on either model:

• Schema reflection. The ORM cannot read table shapes back from the listener, so anything that discovers a schema - describeTable, sync, model autoloading, and the migration tooling built on them - does not work. Declare your models by hand.
• Associations. Related models do not load. Eager loading with include and the lazy accessors that go with it do not resolve.

The practical consequence is worth stating plainly: this suits a single-table application, not a modelled domain. If your application's value is in its object graph, the MySQL listener is not the right door - use the SQL endpoint or the PostgreSQL wire, where joins are served. Fixes for both gaps are in flight. They are not shipped, and you should not plan against them.

// NOT served today. Both of these fail against the MySQL listener.

// 1. Schema reflection - there is nothing to read a table shape back from.
await sequelize.getQueryInterface().describeTable("invoices");   // fails
await sequelize.sync();                                          // fails

// 2. Associations - a modelled domain does not load.
Invoice.hasMany(LineItem, { foreignKey: "invoice_id" });
await Invoice.findAll({ include: [LineItem] });                  // fails

// Do this instead: declare every model by hand (as above) and issue the
// second query yourself, joining in your own application code.
const invoices = await Invoice.findAll({ where: { status: "open" } });
const items = await LineItem.findAll({
  where: { invoice_id: invoices.map((i) => i.id) },
});

TDS - the SQL Server wire protocol

Point a TDS client at $OC_SQL_HOST:1433. The same two credential models apply.

Worked example - mssql-jdbc 13.4.0 and 12.10.1

// Verified with mssql-jdbc 13.4.0 and 12.10.1 - 34 of 34 checks passed.
String url = "jdbc:sqlserver://" + System.getenv("OC_SQL_HOST") + ":1433"
    + ";databaseName=" + System.getenv("OC_DATABASE")
    + ";encrypt=true"                    // mandatory - the listener is TLS-only
    + ";trustServerCertificate=false"
    + ";hostNameInCertificate=" + System.getenv("OC_SQL_HOST")
    + ";loginTimeout=30";

try (Connection conn = DriverManager.getConnection(
        url, System.getenv("OC_DB_USER"), System.getenv("OC_DB_PASSWORD"))) {

    // Prepared statements, including server-side handles and reuse.
    try (PreparedStatement ps = conn.prepareStatement(
            "SELECT id, customer_ref, amount_cents FROM invoices"
          + " WHERE status = ? ORDER BY amount_cents DESC")) {
        ps.setString(1, "open");
        try (ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                System.out.println(rs.getInt("id") + " " + rs.getString("customer_ref"));
            }
        }
    }

    // Batch execution.
    conn.setAutoCommit(false);
    try (PreparedStatement ins = conn.prepareStatement(
            "INSERT INTO invoices (id, customer_ref, amount_cents, status)"
          + " VALUES (?, ?, ?, ?)")) {
        for (Object[] row : rows) {
            ins.setInt(1, (Integer) row[0]);
            ins.setString(2, (String) row[1]);
            ins.setInt(3, (Integer) row[2]);
            ins.setString(4, (String) row[3]);
            ins.addBatch();
        }
        ins.executeBatch();
    }
    conn.commit();
}

Worked example - microsoft/go-mssqldb 1.11.0

// Verified with microsoft/go-mssqldb 1.11.0 - 21 of 21 checks passed.
package main

import (
    "context"
    "database/sql"
    "fmt"
    "net/url"
    "os"

    _ "github.com/microsoft/go-mssqldb"
)

func main() {
    dsn := (&url.URL{
        Scheme: "sqlserver",
        User:   url.UserPassword(os.Getenv("OC_DB_USER"), os.Getenv("OC_DB_PASSWORD")),
        Host:   os.Getenv("OC_SQL_HOST") + ":1433",
        RawQuery: url.Values{
            "database": {os.Getenv("OC_DATABASE")},
            // Mandatory - the listener is TLS-only and validates.
            "encrypt":                {"true"},
            "TrustServerCertificate": {"false"},
        }.Encode(),
    }).String()

    db, err := sql.Open("sqlserver", dsn)
    if err != nil {
        panic(err)
    }
    defer db.Close()

    rows, err := db.QueryContext(context.Background(),
        "SELECT id, customer_ref, amount_cents FROM invoices"+
            " WHERE status = @p1 ORDER BY amount_cents DESC",
        sql.Named("p1", "open"))
    if err != nil {
        panic(err)
    }
    defer rows.Close()

    for rows.Next() {
        var id, amount int
        var ref string
        if err := rows.Scan(&id, &ref, &amount); err != nil {
            panic(err)
        }
        fmt.Println(id, ref, amount)
    }
}

How far this has been proven

The driver-level defects that previously blocked both commercial drivers are fixed, and interop is proven end to end against the real engine - the framed TLS handshake, prepared-statement handles and batch execution included. The measured results were 34 of 34 for mssql-jdbc (13.4.0 and 12.10.1) and 21 of 21 for microsoft/go-mssqldb 1.11.0.

Applications written for Oracle Database

# An application written for Oracle Database connects with a PostgreSQL
# driver, on the PostgreSQL wire port. There is no TNS listener to point
# an Oracle client library at.

psql "host=$OC_SQL_HOST port=5432 dbname=$OC_DATABASE user=$OC_DB_USER sslmode=require"

Served, and equivalent within scope

Served, but narrower than Oracle Database

Each of these ships and is useful, and each is narrower than the construct you know. Where a shape falls outside the served scope it is refused by name rather than executed with the clause quietly dropped - a dropped clause would return a confident, wrong answer.

Refused today, with the portable rewrite

-- Served, and equivalent within the documented scope:
SELECT NVL(discount_pct, 0), NVL2(shipped_at, 'shipped', 'pending') FROM orders;
SELECT seq_invoice.NEXTVAL FROM DUAL;
SELECT sku FROM catalogue MINUS SELECT sku FROM discontinued;

-- Served, but narrower than Oracle Database (see the scope column):
SELECT LEVEL, id, manager_id FROM staff
  START WITH manager_id IS NULL
  CONNECT BY PRIOR id = manager_id;
SELECT DECODE(status, 'O', 'open', 'C', 'closed', 'other') FROM invoices;
MERGE INTO invoices t USING staging s ON (t.id = s.id)
  WHEN MATCHED THEN UPDATE SET t.status = s.status;

-- ROWNUM row caps port, with Oracle's counter semantics kept:
SELECT id FROM invoices WHERE ROWNUM <= 10;          -- becomes a row cap
SELECT id FROM invoices WHERE ROWNUM > 1;            -- returns NOTHING, as in Oracle
SELECT * FROM (                                      -- the pagination idiom, verbatim
  SELECT a.*, ROWNUM rn FROM ( SELECT id, ref FROM invoices ORDER BY id ) a
  WHERE ROWNUM <= 20
) WHERE rn > 10;

-- REFUSED by name, because ROWNUM is assigned BEFORE the sort: this is
-- ten arbitrary rows THEN sorted in Oracle, not the top ten by date.
-- SELECT * FROM invoices WHERE ROWNUM <= 10 ORDER BY created_at;
SELECT * FROM invoices ORDER BY created_at FETCH FIRST 10 ROWS ONLY;

-- NOT served. ROWID has no equivalent; row identity is the PRIMARY KEY.
-- SELECT ROWID, id FROM invoices;                   -- refused

A note on the names used on this page

Every third-party name here is used to describe protocol or dialect compatibility, and for no other purpose. MySQL and Oracle are registered trademarks of Oracle Corporation and/or its affiliates. Microsoft and SQL Server are trademarks of the Microsoft group of companies. PostgreSQL is a registered trademark of the PostgreSQL Community Association of Canada. Sequelize, mysql2, mssql-jdbc and go-mssqldb are the property of their respective owners. OriginChainDB is not affiliated with, endorsed by, or sponsored by any of them. OriginChainDB is not any of these products - it is an independent database that speaks their wire protocols and accepts parts of their SQL.