Babelfish for PostgreSQL on Ubuntu 24.04 on Azure User Guide

Overview

This guide covers the deployment and configuration of Babelfish for PostgreSQL on Ubuntu 24.04 on Azure using cloudimg Azure Marketplace images.

Babelfish for PostgreSQL gives one PostgreSQL cluster a second front door. Alongside the usual PostgreSQL frontend protocol it runs a TDS listener — TDS is the wire protocol Microsoft SQL Server speaks — so an unmodified SQL Server client connects, issues T-SQL, and gets SQL Server semantics: @@VERSION, SELECT TOP n, GETDATE(), sys.databases, CREATE LOGIN, and the rest. The data lives in PostgreSQL the whole time and is readable through ordinary SQL with psql or any PostgreSQL tool.

That is the entire point of the product, and it is what makes it useful: an application written against SQL Server can be pointed at PostgreSQL without rewriting its queries or swapping its driver.

The cloudimg image ships Babelfish 5.4.0 on the project's own patched PostgreSQL 17.7 engine, compiled from source against exact upstream commits. Backed by 24/7 cloudimg support.

What is included:

• Babelfish 5.4.0 — all four extensions (babelfishpg_common, babelfishpg_money, babelfishpg_tds, babelfishpg_tsql) built and installed into an initialised cluster
• The patched PostgreSQL 17.7 engine Babelfish requires. Babelfish does not run on community PostgreSQL, and no community PostgreSQL server is installed on this image
• A cluster that is already initialised — sys.initialize_babelfish has run, master, tempdb and msdb exist, and the appliance answers a T-SQL query the moment it finishes booting
• multi-db migration mode and the standard sql_latin1_general_cp1_ci_as server collation, both of which are fixed at initialisation and cannot be changed afterwards
• TLS built in. Upstream's documented build omits OpenSSL, which leaves the TDS listener unable to offer encryption at all; this build enables it and every VM generates its own key pair on first boot
• No credential in the image. Every account ships with no password, not a default one
• The FreeTDS tsql client, so you can verify the appliance from the VM itself without installing anything
• A full dependency licence inventory at /usr/share/babelfish/dependency-licences.txt and third-party notices at /usr/share/babelfish/THIRD-PARTY-NOTICES

There is no web interface

Babelfish is a database engine. There is no dashboard, no admin console and no REST API on this image, and no web server is installed. You administer it over SSH and you use it with a SQL client. If you want a graphical tool, point SQL Server Management Studio, Azure Data Studio, DBeaver or pgAdmin at it from your own workstation.

Ports, and the one thing to do before anything else

Restrict the network security group to your own network before you put data on this VM. An internet-reachable database is the classic breach, and it is the first thing scanners look for on port 1433. cloudimg gives every VM a strong, unique password and ships no default credential at all — but a firewall is not something a password replaces.

Port	Protocol	Purpose	Reachable on a fresh VM
22	TCP	SSH — how you administer the appliance	Yes
1433	TCP	TDS — the SQL Server wire protocol. This is the product	Yes
5432	TCP	PostgreSQL frontend protocol	Yes, see below

Both database ports are bound, and that is not a configuration choice. Babelfish's TDS listener does not create its own sockets from its own setting: it iterates PostgreSQL's listen_addresses and creates one TDS socket per entry. Setting listen_addresses = 'localhost' to hide 5432 also binds the TDS listener to loopback and makes the product unreachable. The two ports stand or fall together.

What this image does instead is take away everything 5432 could reach that 1433 does not already reach. pg_hba.conf admits remote clients only to the Babelfish database, and rejects every other remote connection outright, so postgres, template0 and template1 are unreachable from the network by any protocol. A remote psql therefore has exactly the reach a remote SQL Server client already has, and no more. You can see that rule set, and check it yourself, in Verifying the appliance below.

Restricting the ports

In the Azure Portal, open the VM's Networking blade and edit the inbound rules so that 22, 1433 and 5432 have a Source of IP Addresses set to your own office or VPN range, not Any. From the Azure CLI, on your own workstation:

# Replace 203.0.113.0/24 with your own network, and the names with yours.
az network nsg rule create \
  --resource-group my-resource-group \
  --nsg-name my-vm-nsg \
  --name allow-tds-from-office \
  --priority 300 \
  --source-address-prefixes 203.0.113.0/24 \
  --destination-port-ranges 1433 5432 \
  --access Allow --protocol Tcp

If you would rather not expose the database at all, leave 1433 and 5432 closed in the NSG and reach them over an SSH tunnel from your workstation:

ssh -L 1433:127.0.0.1:1433 -L 5432:127.0.0.1:5432 azureuser@<vm-ip>

Your SQL client then connects to 127.0.0.1,1433 on your own machine.

Prerequisites

• An Azure subscription with permission to create virtual machines
• An SSH key pair for VM access
• A VM size of Standard_B2ms (2 vCPU, 8 GiB) or larger for real work; Standard_B2s (4 GiB) is fine for evaluation

The recommendation is honest rather than cautious. The appliance is not heavy at rest — the engine idles at a few hundred MB and no swap is created at any point — but every T-SQL backend loads the ANTLR-generated T-SQL parser, so working memory grows faster per connection than plain PostgreSQL. 8 GiB is comfortable; 4 GiB will run a demo. Upstream's own 4 GiB floor applies to compiling Babelfish, which you never have to do, because this image is already built.

Launching the VM

From the Azure Portal

1. Search the Azure Marketplace for Babelfish for PostgreSQL by cloudimg and select Create.
2. Choose your subscription, resource group and region.
3. Pick Standard_B2ms or larger.
4. Select SSH public key authentication with the username azureuser.
5. Under Inbound port rules, allow SSH (22) only for now — you will add 1433 with a restricted source afterwards.
6. Create, and wait for deployment to finish.

From the Azure CLI

az vm create \
  --resource-group my-resource-group \
  --name babelfish-01 \
  --image cloudimg:babelfish-ubuntu-24-04:default:latest \
  --size Standard_B2ms \
  --admin-username azureuser \
  --generate-ssh-keys \
  --public-ip-sku Standard

Accept the image terms once per subscription if prompted. This is an Azure control-plane command, run from your own machine:

az vm image terms accept --urn cloudimg:babelfish-ubuntu-24-04:default:latest

First boot, and your credentials

The image contains no password at all — not a default one, not a well-known one. Every login role ships with PASSWORD NULL, which under the shipped client-authentication rules cannot authenticate over the network by any method. Until first boot finishes, the appliance is genuinely unusable by anybody.

On first boot the appliance:

1. generates 128 bits of entropy per account from /dev/urandom,
2. generates this VM's own TLS key pair,
3. starts the engine bound to 127.0.0.1 only,
4. sets the passwords,
5. proves the product works — a real T-SQL session over TDS creates a table, inserts a row, reads it back, and PostgreSQL then reads the same row — and tears the proof down again,
6. writes the credentials to /root/babelfish-credentials.txt, mode 0600, owned by root,
7. and only then opens the routable listener and audits every socket on the machine.

If any step fails, the port never opens. That ordering is the point.

First boot takes well under a minute. SSH in and read your credentials:

sudo cat /root/babelfish-credentials.txt

You will see something of this shape, with values unique to your VM:

BABELFISH_HOST=10.0.0.13
BABELFISH_TDS_PORT=1433
BABELFISH_LOGIN=babelfish_user
BABELFISH_PASSWORD=****************************
BABELFISH_APP_LOGIN=cloudimg_app
BABELFISH_APP_PASSWORD=****************************
POSTGRES_USER=postgres
POSTGRES_PASSWORD=****************************
POSTGRES_PORT=5432
POSTGRES_DATABASE=babelfish_db

Three accounts are provisioned:

Account	What it is	Use it for
babelfish_user	The Babelfish sysadmin login. Owns the Babelfish database and is a PostgreSQL superuser	Administration: creating databases, creating logins, schema changes
cloudimg_app	An unprivileged T-SQL login, created through T-SQL itself on first boot	Your application's day-to-day connection
postgres	The PostgreSQL superuser	Maintenance through psql

Copy these into your own secret store and then delete the file if you prefer; nothing on the appliance reads it after first boot except the start-up guard, which only checks that it exists and is 0600 root:root.

Confirm first boot completed and the service is up:

test -f /var/lib/cloudimg/babelfish-firstboot.done && echo "first boot complete"
systemctl is-active babelfish.service

Verifying the appliance

Every listening socket on the machine is enumerated from the kernel, and the appliance ships an audit that refuses anything unexpected:

sudo ss -lntuH
sudo /usr/local/sbin/babelfish-socket-audit.sh --expect-running

The audit classifies every socket by whether its bind address is reachable from off the machine, rather than by a list of port numbers, and exits non-zero if anything other than SSH, TDS, PostgreSQL and the DHCP client is routable.

You can read the client-authentication rules that confine port 5432 yourself:

sudo grep -vE '^\s*#|^\s*$' /var/lib/babelfish/5.4.0/data/pg_hba.conf

The host babelfish_db all 0.0.0.0/0 md5 line is what lets remote SQL Server clients in; the host all all 0.0.0.0/0 reject line beneath it is what stops any remote connection reaching anything else.

Why md5 and not scram-sha-256. Babelfish's TDS login handler accepts only trust, password, md5 and gssapi, and rejects a scram-sha-256 rule outright — with the PostgreSQL 14+ default of scram-sha-256 here, psql works and every SQL Server client is refused. So the rules say md5, while password_encryption is scram-sha-256, meaning what is actually stored is a SCRAM verifier and never an MD5 hash. You can confirm that:

sudo -u postgres /opt/babelfish/5.4.0/bin/psql -h /var/run/postgresql -tAc \
  "SELECT rolname, left(rolpassword, 13) FROM pg_authid WHERE rolcanlogin AND rolpassword IS NOT NULL;"

Your first T-SQL session

The appliance ships the FreeTDS tsql client so you can verify it from the VM itself. GO terminates each batch, exactly as in sqlcmd.

tsql -H 127.0.0.1 -p 1433 -U babelfish_user -P '<BABELFISH_PASSWORD>' -D master <<'SQL'
SELECT @@VERSION
GO
SQL

That reports Babelfish for PostgreSQL with SQL Server Compatibility, the Babelfish version, and the PostgreSQL engine underneath.

Now do something real — create a table, insert rows, and read them back with T-SQL syntax that plain PostgreSQL would reject:

tsql -H 127.0.0.1 -p 1433 -U babelfish_user -P '<BABELFISH_PASSWORD>' -D master <<'SQL'
CREATE TABLE inventory (id INT, part VARCHAR(40), qty INT, updated DATETIME)
GO
INSERT INTO inventory VALUES (1, 'gasket', 42, GETDATE()), (2, 'flange', 17, GETDATE())
GO
SELECT TOP 2 id, part, qty FROM inventory ORDER BY id
GO
SELECT name, database_id FROM sys.databases
GO
SQL

SELECT TOP, GETDATE() and sys.databases are all SQL Server constructs. They work here because the T-SQL parser and the SQL Server catalogue views are doing their job, not because PostgreSQL happens to understand them.

Connecting from your own machine

From a workstation, connect exactly as you would to SQL Server, using the VM's address and port 1433. With Microsoft's sqlcmd:

sqlcmd -S <vm-ip>,1433 -U babelfish_user -P '<password>' -d master -N -C -Q "SELECT @@VERSION"

-N -C asks for an encrypted connection and accepts the VM's self-signed certificate. mssql-tools18 requires encryption by default, which is exactly why this build enables TLS. If you use a tool that offers "Trust server certificate", tick it — or replace the certificate with one of your own, as described below.

SQL Server Management Studio, Azure Data Studio, DBeaver and any .NET SqlClient connection string work the same way: server <vm-ip>,1433, SQL Server authentication, the login and password from your credentials file.

Babelfish speaks TDS 7.0 to 7.4. It does not implement TDS 8.0, so SQL Server 2022 strict encryption (sqlcmd -N s) will not connect. Use the standard encrypted mode shown above.

The same data, through PostgreSQL

This is what makes Babelfish more than a compatibility shim. The rows you just wrote with T-SQL are ordinary PostgreSQL rows. In multi-db mode, a T-SQL database's dbo schema is <database>_dbo, so the master database's tables live in master_dbo.

sudo -u postgres /opt/babelfish/5.4.0/bin/psql -h /var/run/postgresql -d babelfish_db \
  -c "SELECT id, part, qty FROM master_dbo.inventory ORDER BY id;"

The same rows, the same table, reached through a completely different wire protocol. Every PostgreSQL tool you already own — pg_dump, monitoring, replication, extensions — works against this data.

You can see what is installed in the cluster:

sudo -u postgres /opt/babelfish/5.4.0/bin/psql -h /var/run/postgresql -d babelfish_db \
  -c "SELECT extname, extversion FROM pg_extension ORDER BY extname;"

Tidy up the demonstration table when you are done:

tsql -H 127.0.0.1 -p 1433 -U babelfish_user -P '<BABELFISH_PASSWORD>' -D master <<'SQL'
DROP TABLE inventory
GO
SQL

Creating databases and logins

T-SQL CREATE DATABASE creates a Babelfish database, which appears as a set of schemas inside babelfish_db:

tsql -H 127.0.0.1 -p 1433 -U babelfish_user -P '<BABELFISH_PASSWORD>' -D master <<'SQL'
CREATE DATABASE salesdemo
GO
USE salesdemo
GO
CREATE TABLE orders (id INT, customer VARCHAR(60), total MONEY)
GO
INSERT INTO orders VALUES (1, 'Contoso', 199.95)
GO
SELECT id, customer, total FROM orders
GO
DROP TABLE orders
GO
USE master
GO
DROP DATABASE salesdemo
GO
SQL

The block removes the demonstration database again so you can run it as many times as you like. Drop the DROP DATABASE line if you want to keep it.

Logins are created with T-SQL too. The unprivileged cloudimg_app login already exists for your application; create more the same way:

tsql -H 127.0.0.1 -p 1433 -U babelfish_user -P '<BABELFISH_PASSWORD>' -D master <<'SQL'
CREATE LOGIN reporting WITH PASSWORD = 'ChangeThisToSomethingStrong!2026'
GO
SELECT name FROM sys.server_principals WHERE name = 'reporting'
GO
SQL

Verify from the PostgreSQL side that the login is not a superuser, then remove it:

sudo -u postgres /opt/babelfish/5.4.0/bin/psql -h /var/run/postgresql -tAc \
  "SELECT rolname, rolsuper FROM pg_roles WHERE rolname = 'reporting';"
tsql -H 127.0.0.1 -p 1433 -U babelfish_user -P '<BABELFISH_PASSWORD>' -D master <<'SQL'
DROP LOGIN reporting
GO
SQL

What is fixed at initialisation

Three settings are decided when Babelfish is initialised and cannot be changed afterwards without building a new cluster. This image sets them, and they ship as chosen:

Setting	Value on this image	Meaning
babelfishpg_tsql.migration_mode	multi-db	Each T-SQL database gets its own schema namespace, <database>_dbo. This is upstream's recommendation and what a migrating SQL Server application expects
babelfishpg_tsql.database_name	babelfish_db	The PostgreSQL database that holds everything Babelfish
babelfishpg_tsql.server_collation_name	sql_latin1_general_cp1_ci_as	The SQL Server default: case-insensitive, accent-sensitive

sudo -u postgres /opt/babelfish/5.4.0/bin/psql -h /var/run/postgresql -d babelfish_db -tAc \
  "SELECT name, setting FROM pg_settings WHERE name LIKE 'babelfishpg_tsql.%' ORDER BY name;"

If you need single-db mode or a different collation, say so before you load data — the only way to change them is to create a new cluster.

Encryption in transit

The appliance generates its own TLS key pair on your VM at first boot; no two VMs from this image share a private key, and no key is baked in. The certificate is self-signed, which is why clients need "trust server certificate" (-C in sqlcmd).

sudo -u postgres /opt/babelfish/5.4.0/bin/psql -h /var/run/postgresql -tAc "SHOW ssl;"
sudo openssl x509 -noout -subject -dates \
  -in /var/lib/babelfish/5.4.0/data/cloudimg-babelfish.crt

To use a certificate from your own authority, replace cloudimg-babelfish.crt and cloudimg-babelfish.key in that directory, keep the key 0600 and owned by postgres, and reload the engine with sudo systemctl reload babelfish.service.

Operating the appliance

The engine runs as a single systemd service:

systemctl status babelfish.service --no-pager

Logs are collected by PostgreSQL itself, one file per day:

sudo ls -1 /var/log/babelfish/
sudo tail -20 /var/log/babelfish/*.log

A convenience wrapper is on PATH for psql against the local cluster:

babelfish-psql -d babelfish_db -c "SELECT current_database(), version();"

Back up exactly as you would any PostgreSQL database — pg_dump of babelfish_db captures every T-SQL database, because they are all schemas inside it.

The start-up guard

babelfish.service will not start unless the appliance is in a shippable state. Before every start, as root, it checks that the data directory belongs to the service account, that listen_addresses has not been reduced to something that would make the TDS listener unreachable, that babelfishpg_tds is still preloaded, that the TDS port is still 1433, that pg_hba.conf contains no trust rule and still carries its remote reject catch-all, and that first boot has completed and left a 0600 root:root credentials file. You can run it by hand:

sudo /usr/local/sbin/babelfish-preflight.sh

If you ever see the service refusing to start, run that and it will tell you exactly which invariant is broken.

What is inside, and under what licence

Babelfish is open source, and this image is built from source against exact upstream commits — upstream publishes no packages, no APT repository and no container image, so there is nothing else to install.

cat /usr/share/babelfish/provenance.txt

Component	Version	Licence
Babelfish extensions	5.4.0 (BABEL_5_4_0)	Apache License 2.0
PostgreSQL, patched for Babelfish	17.7 (BABEL_5_4_0__PG_17_7)	The PostgreSQL Licence
ANTLR 4 C++ runtime	4.13.2	BSD 3-Clause
T-SQL ANTLR grammar	vendored	MIT

The full texts ship on the image, and so does a machine-generated inventory of every operating system package:

cat /usr/share/babelfish/THIRD-PARTY-NOTICES
head -12 /usr/share/babelfish/dependency-licences.txt

That inventory is produced by an executable gate that runs at build time and again on a freshly launched VM. It reads structured licence metadata for every installed package, fails on any network-copyleft or source-available licence, reports GPL and LGPL components for review rather than blocking on them, and treats unresolved metadata as a failure. You can re-run it yourself:

sudo python3 /usr/share/babelfish/babelfish-licence-gate.py --selftest

Two things are deliberately not on this image: Microsoft's mssql-tools/sqlcmd, which ship under a proprietary licence, and Babelfish's optional linked-servers feature, which links FreeTDS under the LGPL. The FreeTDS tsql client is present as a separate program for local verification and is not linked into any Babelfish binary.

Support

This image is supported 24/7 by cloudimg. Raise an issue at cloudimg.co.uk with your subscription id and the VM name, and include the output of cat /usr/share/babelfish/provenance.txt — it identifies exactly which build you are running.