SQL Server 2022 Developer on Ubuntu 22.04

In the AWS Marketplace listing, choose Continue to Subscribe, then Continue to Configuration. Pick SQL Server 2022 Developer on Ubuntu 22.04 as the software version and select your Region, then choose Launch through EC2.

Choose m5.large or larger, your key pair and a security group as described in Prerequisites. SQL Server needs at least 2 GB of memory.

With the AWS CLI (replace the values in angle brackets):

aws ec2 run-instances --region <region> --image-id <ami-id-from-the-listing> \
  --instance-type m5.large --key-name <your-key-pair> \
  --security-group-ids <your-security-group> --subnet-id <your-subnet> \
  --metadata-options HttpTokens=required,HttpEndpoint=enabled \
  --tag-specifications 'ResourceType=instance,Tags=[{Key=Name,Value=sql-server-developer}]'

On a new instance, first boot takes about five minutes. Amazon EBS loads the AMI's blocks from its snapshot the first time they are read, and SQL Server's setup reads 1.4 GB of program files. Until first boot finishes, systemctl is-active mssql-firstboot reports activating and /root/mssql-credentials.txt holds a temporary value instead of the password. Later boots start SQL Server in seconds.

Connect over SSH as the ubuntu user on SQL Server 2022 Developer on Ubuntu 22.04.

ssh -i <your-key.pem> ubuntu@<public-ip>

The first-boot service and SQL Server both report active:

systemctl is-active mssql-firstboot mssql-server

active active

Check what is listening. SQL Server listens on the loopback address only, and only SSH listens on the network.

1433 is the database port, and 1431 is an internal SQL Server port. 1434 is the local dedicated administrator connection (DAC), the emergency connection a sysadmin can use from the instance itself when SQL Server stops responding. Remote DAC is off.

sudo ss -Htlnp | awk '{print $4, $6}' | sed 's/,pid=[0-9]*,fd=[0-9]*//g' | sort -u

0.0.0.0:22 users:(("sshd")) 127.0.0.1:1431 users:(("sqlservr")) 127.0.0.1:1433 users:(("sqlservr")) 127.0.0.1:1434 users:(("sqlservr")) 127.0.0.53%lo:53 users:(("systemd-resolve")) [::1]:1431 users:(("sqlservr")) [::1]:1434 users:(("sqlservr")) [::]:22 users:(("sshd"))

The credentials file is readable only by root. List the setting names it holds:

sudo ls -l /root/mssql-credentials.txt
sudo grep -oE '^[A-Z_0-9]+=' /root/mssql-credentials.txt

-rw------- 1 root root 688 Oct 24 18:29 /root/mssql-credentials.txt SA_USER= SA_PASSWORD= <set-at-first-boot>= TLS_CERT= TLS_CERT_SHA256=

Read the whole file with sudo cat /root/mssql-credentials.txt and store the password in your password manager. It is unique to this instance.

Never pass the password to sqlcmd with -P, because it would land in your shell history and the process list. sqlcmd reads it from the SQLCMDPASSWORD environment variable instead. Load it once per shell:

export SQLCMDPASSWORD="$(sudo awk -F= '/^SA_PASSWORD=/{print $2}' /root/mssql-credentials.txt)"

The blocks below repeat that line so that each one works on its own. sqlcmd is on the PATH of every login shell (/opt/mssql-tools18/bin), but the blocks use the full path.

-C tells sqlcmd to trust the instance's self-signed certificate (Step 6 shows how to pin it instead).

export SQLCMDPASSWORD="$(sudo awk -F= '/^SA_PASSWORD=/{print $2}' /root/mssql-credentials.txt)" /opt/mssql-tools18/bin/sqlcmd -S 127.0.0.1 -U sa -C -Q "SET NOCOUNT ON; SELECT CAST(SERVERPROPERTY('Edition') AS nvarchar(40)) AS edition, CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(20)) AS version, CAST(SERVERPROPERTY('ProductUpdateLevel') AS nvarchar(10)) AS cu, CAST(SERVERPROPERTY('EngineEdition') AS int) AS engine_edition"

edition version cu engine_edition ---------------------------------------- -------------------- ---------- -------------- Developer Edition (64-bit) 16.0.4305.5 CU27 3

engine_edition 3 is the Enterprise engine, which Developer edition runs.

SQL Server forces encryption, so even a client that asks only for optional encryption (-No) gets an encrypted session:

export SQLCMDPASSWORD="$(sudo awk -F= '/^SA_PASSWORD=/{print $2}' /root/mssql-credentials.txt)" /opt/mssql-tools18/bin/sqlcmd -S 127.0.0.1 -U sa -C -No -Q "SET NOCOUNT ON; SELECT encrypt_option, protocol_type, auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@SPID"

encrypt_option protocol_type auth_scheme ---------------------------------------- ---------------------------------------- ---------------------------------------- TRUE TSQL SQL

The certificate is this instance's own, named after its hostname:

openssl x509 -in /etc/ssl/mssql/mssql.pem -noout -subject -enddate -ext subjectAltName -fingerprint -sha256

subject=CN = ip-172-31-89-102 notAfter=Oct 21 18:28:28 2036 GMT X509v3 Subject Alternative Name: DNS:ip-172-31-89-102, DNS:localhost, IP Address:127.0.0.1 sha256 Fingerprint=1B:13:1A:B3:27:A8:CA:F9:ED:C0:1C:9B:F4:96:EB:83:6A:48:58:19:05:C0:93:0A:F6:C4:DD:99:67:8C:84:3E

The same fingerprint is in /root/mssql-credentials.txt as TLS_CERT_SHA256. To verify the server instead of trusting it blindly, copy /etc/ssl/mssql/mssql.pem (the public certificate, never mssql.key) to your client and add it to the client's trusted certificates, or pin the fingerprint in tools that support it. Because the certificate names localhost and 127.0.0.1, verification also passes through the SSH tunnel in Step 8.

Create a database, write a row, read it back and drop the database:

export SQLCMDPASSWORD="$(sudo awk -F= '/^SA_PASSWORD=/{print $2}' /root/mssql-credentials.txt)" /opt/mssql-tools18/bin/sqlcmd -S 127.0.0.1 -U sa -C -W -Q "SET NOCOUNT ON; CREATE DATABASE demo; EXEC('USE demo; CREATE TABLE dbo.notes (id INT IDENTITY PRIMARY KEY, body NVARCHAR(200) NOT NULL); INSERT INTO dbo.notes (body) VALUES (N''hello from SQL Server 2022 Developer'');'); SELECT id, body FROM demo.dbo.notes; DROP DATABASE demo;"

id body -- ---- 1 hello from SQL Server 2022 Developer

For an application, create its own login and database instead of using sa. Run this in an interactive sqlcmd session (/opt/mssql-tools18/bin/sqlcmd -S 127.0.0.1 -U sa -C), choosing your own strong password:

CREATE DATABASE appdb; GO CREATE LOGIN appuser WITH PASSWORD = N'<a-strong-password>', CHECK_POLICY = ON; GO USE appdb; CREATE USER appuser FOR LOGIN appuser; ALTER ROLE db_owner ADD MEMBER appuser; GO

The safest way to use SQL Server Management Studio, Azure Data Studio, VS Code or an application on your workstation is an SSH tunnel. Port 1433 stays closed to the network:

ssh -i <your-key.pem> -N -L 1433:127.0.0.1:1433 ubuntu@<public-ip>

Then connect your client to 127.0.0.1,1433 with SQL Server authentication as sa, encryption Mandatory (or Strict), and either trust the server certificate or install mssql.pem as a trusted certificate (Step 6).

If applications on other hosts in your VPC must connect directly, open the listen address and restart SQL Server, then allow TCP 1433 in the security group from those hosts' CIDRs only, never from 0.0.0.0/0:

sudo /opt/mssql/bin/mssql-conf unset network.ipaddress
sudo systemctl restart mssql-server

Encryption stays forced. The certificate names the instance's hostname, localhost and 127.0.0.1. Clients connecting by another name need to trust the certificate or use a certificate you issue for that name (mssql-conf set network.tlscert and network.tlskey, then restart).