Databases Azure

SQL Server 2022 Web on Ubuntu 22.04 on Azure User Guide

| Product: SQL Server 2022 Web on Ubuntu 22.04 LTS on Azure

Overview

This guide covers the deployment and configuration of SQL Server 2022 Web on Ubuntu 22.04 on Azure using cloudimg Azure Marketplace images. SQL Server 2022 is Microsoft's current flagship relational database, delivering Azure Arc enabled services out of the box, a query store that is on by default, intelligent query processing, contained availability groups, ledger tables for tamper evident audit, and accelerated database recovery on every edition. Web Edition is purpose built for public facing web workloads, providing a cost effective relational database backend for internet facing applications at scale.

The image ships SQL Server 2022 Web Edition installed from the official Microsoft APT repository at packages.microsoft.com pinned to Ubuntu 22.04 jammy. The Microsoft GPG key is verified at build time so the chain of trust is unambiguous. On every fresh customer virtual machine, mssql-firstboot.service rotates the SA password from the build time placeholder to a strong random value unique to that virtual machine, creates the default cloudimg database, provisions a cloudimg SQL login with db_owner on that database, removes the build time memory cap, and writes the credentials to /stage/scripts/mssql-credentials.log. This pattern means no two virtual machines ever share an SA password and no password is baked into the image at any point.

SPLA Licensing. SQL Server Web Edition is licensed under the Microsoft Services Provider License Agreement (SPLA). Customers must hold a valid SPLA agreement with Microsoft. Web Edition is not available for purchase through retail or volume licensing channels and is not intended for internal line of business applications. Contact your Microsoft licensing representative to confirm eligibility before deploying this image.

What is included:

  • Microsoft SQL Server 2022 Web Edition from packages.microsoft.com

  • mssql-tools18 including sqlcmd and bcp for database administration

  • Listener on TCP port 1433 on every interface by default

  • Per VM SA and cloudimg passwords generated on first boot, written to /stage/scripts/mssql-credentials.log

  • Default cloudimg database created on first boot

  • cloudimg SQL login with db_owner role on the cloudimg database

  • Systemd service mssql-server.service for automatic startup on boot

  • Systemd oneshot mssql-firstboot.service for idempotent per VM credential rotation

  • Ubuntu 22.04 LTS base with latest security patches applied at build time

  • Azure Linux Agent for seamless cloud integration and SSH key injection

  • 24/7 cloudimg support with guaranteed 24 hour response SLA

Prerequisites

Before deploying this image, ensure you have:

  • An active Azure subscription

  • A subscription to the SQL Server 2022 Web on Ubuntu 22.04 listing on Azure Marketplace

  • A valid Microsoft SPLA agreement authorising use of SQL Server Web Edition

  • An SSH public key for virtual machine authentication

  • Familiarity with Azure virtual machine management and SSH

  • A virtual network and subnet in the target region

Recommended virtual machine size: Standard_D2s_v5 (2 vCPU, 8 GB RAM) or larger. Web Edition is optimised for high throughput web workloads. Scale vCPU and memory to match peak concurrent connection requirements.

Step 1: Deploy the Virtual Machine from the Azure Portal

Navigate to Marketplace in the Azure Portal, search for SQL Server 2022 Web, and select the cloudimg publisher entry. Click Create to begin the wizard.

On the Basics tab choose your subscription, target resource group, and region. Set the virtual machine name. Choose SSH public key as the authentication type, set the username to a name of your choice (the examples below use azureuser), and paste your SSH public key. Standard_D2s_v5 is the recommended starting size.

On the Disks tab set the OS disk type to Standard SSD or Premium SSD. SQL Server data lives at /var/opt/mssql/data on the root disk by default. For production workloads attach a separate Premium SSD data disk and bind mount it over /var/opt/mssql/data before starting the service.

On the Networking tab select your existing virtual network and subnet. Attach a network security group that allows inbound TCP 22 from your management IP range and inbound TCP 1433 only from application server subnets or the virtual network CIDR. Do not expose port 1433 to the public internet. The SA password is written in plain text on the virtual machine by design so you can read it without a second trip; an internet exposed SQL Server port would make brute force attacks trivial.

On the Management, Monitoring, and Advanced tabs the defaults are appropriate. Click Review + create, wait for validation to pass, then click Create. Deployment takes around two minutes.

Step 2: Deploy the Virtual Machine from the Azure CLI

If you prefer the command line, use the gallery image resource identifier as the source. The exact resource identifier is published on your Partner Center plan. A representative invocation:

RG="mssql-web-prod"
LOCATION="eastus"
VM_NAME="mssql-web-01"
ADMIN_USER="azureuser"
GALLERY_IMAGE_ID="/subscriptions/<sub-id>/resourceGroups/azure-cloudimg/providers/Microsoft.Compute/galleries/cloudimgGallery/images/mssql-2022-web-ubuntu-22-04/versions/<version>"
SSH_KEY="$(cat ~/.ssh/id_rsa.pub)"

az group create --name "$RG" --location "$LOCATION"

az network vnet create \
  --resource-group "$RG" \
  --name mssql-web-vnet \
  --address-prefix 10.81.0.0/16 \
  --subnet-name mssql-web-subnet \
  --subnet-prefix 10.81.1.0/24

az network nsg create --resource-group "$RG" --name mssql-web-nsg

az network nsg rule create \
  --resource-group "$RG" --nsg-name mssql-web-nsg \
  --name allow-ssh-mgmt --priority 100 \
  --source-address-prefixes "<your-mgmt-cidr>" \
  --destination-port-ranges 22 --access Allow --protocol Tcp

az network nsg rule create \
  --resource-group "$RG" --nsg-name mssql-web-nsg \
  --name allow-mssql-vnet --priority 110 \
  --source-address-prefixes 10.81.0.0/16 \
  --destination-port-ranges 1433 --access Allow --protocol Tcp

az vm create \
  --resource-group "$RG" \
  --name "$VM_NAME" \
  --image "$GALLERY_IMAGE_ID" \
  --size Standard_D2s_v5 \
  --storage-sku StandardSSD_LRS \
  --admin-username "$ADMIN_USER" \
  --ssh-key-values "$SSH_KEY" \
  --vnet-name mssql-web-vnet --subnet mssql-web-subnet \
  --nsg mssql-web-nsg \
  --public-ip-address "" \
  --os-disk-size-gb 64

The --public-ip-address "" flag keeps the database off the public internet. Use a bastion host or your existing private connectivity to reach it.

Step 3: Connect via SSH

After deployment, find the private IP of the new virtual machine. From a host inside the same virtual network:

ssh azureuser@<private-ip>

The first login may take a few seconds while cloud init finalises. Once you have a shell, mssql-server.service will have already started and mssql-firstboot.service will have already run, generating the per VM SA password, creating the cloudimg database, and provisioning the cloudimg SQL login. Both services are one time operations gated by a sentinel file so they do not repeat on subsequent reboots.

Step 4: Verify the SQL Server Service

Confirm the SQL Server service is active:

sudo systemctl status mssql-server.service --no-pager

You should see active (running). Confirm the firstboot sentinel exists:

sudo test -f /var/opt/mssql/.firstboot-done && echo FIRSTBOOT_DONE

Confirm the TCP listener is bound on port 1433:

sudo ss -tln | grep 1433

mssql-server.service active (running) with TCP 1433 bound on all interfaces

Step 5: Retrieve the SA and cloudimg Passwords

The SA and cloudimg passwords have been randomly generated on this specific virtual machine and written to a root only file. Read them with:

sudo cat /stage/scripts/mssql-credentials.log

You will see lines similar to:

SA_USER=sa
SA_PASSWORD=<SA_PASSWORD>
CLOUDIMG_USER=cloudimg
CLOUDIMG_PASSWORD=<CLOUDIMG_PASSWORD>
CLOUDIMG_DATABASE=cloudimg

Store the values in your secret store. You can shred the credentials file once the passwords are saved, but keeping it root protected at mode 0600 is a reasonable default for the life of the virtual machine.

Step 6: Connect Locally with sqlcmd

The mssql-tools18 package installs sqlcmd at /opt/mssql-tools18/bin/sqlcmd. Verify the binary is on PATH:

sudo ls -l /opt/mssql-tools18/bin/sqlcmd

Connect to the server as SA. The command reads the SA password directly from the credentials file:

/opt/mssql-tools18/bin/sqlcmd -S localhost -U sa \
  -P "$(sudo awk -F= '/^SA_PASSWORD=/{print $2}' /stage/scripts/mssql-credentials.log)" \
  -C -Q "SELECT @@VERSION;"

You should see the server reports Microsoft SQL Server 2022.

Step 7: Confirm the Web Edition

Confirm the running edition is Web:

/opt/mssql-tools18/bin/sqlcmd -S localhost -U sa \
  -P "$(sudo awk -F= '/^SA_PASSWORD=/{print $2}' /stage/scripts/mssql-credentials.log)" \
  -C -Q "SELECT SERVERPROPERTY('Edition') AS Edition;"

You should see Web Edition (64-bit).

Per-VM SA + cloudimg credentials at /stage/scripts/mssql-credentials.log; SERVERPROPERTY('Edition') confirms Web Edition (64-bit)

Step 8: Round-trip Test with sqlcmd

The cloudimg database and cloudimg login are ready to use from first boot. Run a round-trip query as the cloudimg user:

/opt/mssql-tools18/bin/sqlcmd -S localhost -U cloudimg \
  -P "$(sudo awk -F= '/^CLOUDIMG_PASSWORD=/{print $2}' /stage/scripts/mssql-credentials.log)" \
  -C -d cloudimg -h -1 -W -Q "CREATE TABLE ##probe (id INT, val NVARCHAR(64)); INSERT INTO ##probe VALUES (1, N'cloudimg-ok'); SELECT val FROM ##probe WHERE id=1; DROP TABLE IF EXISTS ##probe;"

The output should include cloudimg-ok, confirming the cloudimg user has the expected permissions on the cloudimg database.

@@VERSION reports Microsoft SQL Server 2022 Web on Linux (Ubuntu 22.04); cloudimg user CREATE/INSERT/SELECT round-trip on cloudimg database returns cloudimg-ok

SQL Server 2022 Web Edition is sold ONLY through Microsoft SPLA (Service Provider License Agreement) for web-hosting providers — apply your SPLA product key with sudo /opt/mssql/bin/mssql-conf setpid

Step 9: Connect from a Remote Client

From any host inside the same virtual network that has sqlcmd or SQL Server Management Studio installed, connect on port 1433 with the SA or cloudimg credentials. A representative sqlcmd invocation from a remote client:

sqlcmd -S <private-ip>,1433 -U sa -P '<SA_PASSWORD>' -C -d cloudimg

SQL Server Management Studio and Azure Data Studio connect the same way, using the virtual machine's private IP and the SA or cloudimg credentials. If you are connecting from a management workstation outside the virtual network, use Azure Bastion or an SSH tunnel. Do not open port 1433 to the public internet.

Step 10: Server Components

Component Path

SQL Server daemon /opt/mssql/bin/sqlservr

sqlcmd client /opt/mssql-tools18/bin/sqlcmd

bcp bulk copy utility /opt/mssql-tools18/bin/bcp

mssql-conf configuration tool /opt/mssql/bin/mssql-conf

SQL Server data directory /var/opt/mssql/data/

SQL Server log directory /var/opt/mssql/log/

Systemd unit /lib/systemd/system/mssql-server.service

Firstboot systemd unit /etc/systemd/system/mssql-firstboot.service

Firstboot script /usr/local/sbin/mssql-firstboot.sh

Credentials file /stage/scripts/mssql-credentials.log

Firstboot sentinel /var/opt/mssql/.firstboot-done

Inspect the running mssql-server package version:

dpkg-query -W -f='${Package} ${Version}\n' mssql-server mssql-tools18

Step 11: Filesystem Layout

Mount point Size Description

/ 32 GB Root filesystem (includes /var/opt/mssql)

/boot 1 GB Operating system kernel files

/var/opt/mssql/data (on root) SQL Server data directory

/var/opt/mssql/log (on root) SQL Server transaction logs and error logs

For production workloads attach a separate Premium SSD data disk, format it with xfs, and bind mount it over /var/opt/mssql/data before starting the service. Place the transaction log directory on a separate disk for IO isolation. This keeps database IO off the OS disk and allows independent resize and snapshot of the data and log volumes.

Step 12: Managing the SQL Server Service

SQL Server is started and stopped by systemd via the mssql-server.service unit.

Check service status:

sudo systemctl status mssql-server.service --no-pager

Stop SQL Server:

sudo systemctl stop mssql-server.service

Start SQL Server:

sudo systemctl start mssql-server.service

Restart SQL Server:

sudo systemctl restart mssql-server.service

View the SQL Server error log:

sudo tail -n 50 /var/opt/mssql/log/errorlog

Step 13: Troubleshooting

Cannot connect to SQL Server on port 1433

  • Verify the service is running: sudo systemctl status mssql-server.service

  • Verify the listener is bound: sudo ss -tln | grep 1433

  • Check the error log: sudo tail -n 50 /var/opt/mssql/log/errorlog

  • Confirm the network security group allows TCP 1433 from your client source IP

  • Confirm the virtual machine NIC is in the expected subnet with the NSG attached

Authentication errors after connecting

  • Re read the SA password: sudo awk -F= '/^SA_PASSWORD=/{print $2}' /stage/scripts/mssql-credentials.log

  • Verify the firstboot sentinel exists: ls -la /var/opt/mssql/.firstboot-done. If it is absent, mssql-firstboot.service did not complete; check its journal

  • Check the firstboot journal: sudo journalctl -u mssql-firstboot.service --no-pager

Firstboot service did not run

  • Check the firstboot service status: sudo systemctl status mssql-firstboot.service --no-pager

  • Review the journal: sudo journalctl -u mssql-firstboot.service --no-pager -n 100

  • Verify SQL Server started: sudo systemctl status mssql-server.service

  • If SQL Server failed to start, check the error log: sudo tail -n 100 /var/opt/mssql/log/errorlog

Service fails to start

  • Check systemd journal: sudo journalctl -u mssql-server.service --no-pager -n 100

  • Check the SQL Server error log: sudo tail -n 100 /var/opt/mssql/log/errorlog

  • Check filesystem space: df -h /var/opt/mssql

  • Verify data directory ownership: sudo ls -la /var/opt/mssql/data | head; files should be owned mssql:mssql

Step 14: Security Recommendations

  • Rotate the SA password on a schedule using ALTER LOGIN [sa] WITH PASSWORD=N'<new-password>';

  • Create application specific logins with minimal privileges, never connect application code as SA

  • Restrict port 1433 to trusted application server subnets only in your NSG

  • Disable the SA login once named logins are in place: ALTER LOGIN [sa] DISABLE;

  • Enable Transparent Data Encryption for sensitive databases

  • Enable the error log monitoring and alerting pipeline; /var/opt/mssql/log/errorlog rolls automatically

  • Back up regularly with native BACKUP DATABASE statements or SQL Agent jobs

  • Keep SQL Server updated by running sudo apt-get update && sudo apt-get upgrade mssql-server periodically; the official packages.microsoft.com APT repository is already configured on this image

  • Shred the credentials file once the passwords are stored in your secret store: sudo shred -u /stage/scripts/mssql-credentials.log

Step 15: Support and Licensing

SQL Server 2022 Web Edition is Microsoft proprietary software licensed under the Services Provider License Agreement (SPLA). Deployment requires a valid SPLA agreement. Web Edition is restricted to use in web hosted scenarios and is not licensed for internal enterprise applications. Microsoft Server and SQL Server are registered trademarks of Microsoft Corporation.

cloudimg provides commercial support for this image separately from the upstream Microsoft product. Contact us at any time for support questions, architectural advice, capacity planning, or migration help.

  • Email: support@cloudimg.co.uk

  • Website: www.cloudimg.co.uk

  • Support hours: 24/7 with guaranteed 24 hour response SLA

Deploy on Azure

Launch SQL Server 2022 Web on Ubuntu 22.04 with 24/7 support from cloudimg.

View on Marketplace

Need Help?

Our support team is available 24/7.

support@cloudimg.co.uk