SecureAnyCloud
Deployment guide

SQL Server 2022 Standard on Ubuntu Server 22.04 with Support by Sac

Overview

This guide covers the deployment and configuration of SQL Server 2022 Standard on Ubuntu Server 22.04 using SecureAnyCloud AMIs from the AWS Marketplace. SQL Server 2022 is the latest major release of Microsoft's relational database platform, running on Ubuntu Server 22.04 LTS for long term support, modern kernel features, and broad ecosystem compatibility.

What's included in this AMI:

  • SQL Server 2022 Standard edition installed at /opt/mssql
  • Ubuntu Server 22.04 LTS operating system
  • SQL Server database port 1433 preconfigured and listening on boot
  • SA (System Administrator) account with a randomly generated password
  • sqlcmd command line tool for database interaction
  • OS package update script for keeping the system current
  • AWS CLI v2 for AWS service integration
  • Systems Manager Agent (SSM) for remote management
  • CloudWatch Agent for monitoring
  • Latest security patches applied at build time
  • 24/7 SecureAnyCloud support with guaranteed 24 hour response SLA

Edition Notes: SQL Server 2022 Standard is designed for production workloads with moderate resource requirements. It supports up to 128 GB of buffer pool memory and the lesser of 4 sockets or 24 cores. SQL Server 2022 introduces enhancements over 2019 including improved query processing with parameter sensitive plan optimisation, enhanced Azure integration, ledger database support for tamper evident data, and improved disaster recovery with contained availability groups. Running on Ubuntu 22.04 LTS provides support through April 2027 with extended security maintenance available through 2032.

Prerequisites

You need:

  • An active AWS account
  • An active subscription to the SQL Server 2022 Standard on Ubuntu Server 22.04 listing on the AWS Marketplace
  • An EC2 key pair for SSH access
  • Familiarity with EC2 instance management and SSH

Recommended instance type: m5.xlarge (4 vCPU, 16 GB RAM) or larger. Production workloads benefit from extra memory for query caching and processing.

Minimum requirements: 1 vCPU, 2 GB RAM and 6 GB of disk space.

1

Launch the AMI

  • Open the AWS Marketplace and search for "SQL Server 2022 Standard Ubuntu SecureAnyCloud".
  • Select Continue to Subscribe, accept the terms, then select Continue to Configuration.
  • Choose your Region and Software Version.
  • Select Continue to Launch.
  • Choose Launch through EC2 for full control over instance configuration.
  • Select your instance type. m5.xlarge is recommended for production.
  • Configure storage. Provision gp3 volumes that suit your database workload, and consider separate EBS volumes for data and log files to improve performance.
  • Configure your Security Group with the following inbound rules:

Port Protocol Source Purpose

22 TCP Your IP SSH access

1433 TCP Your IP SQL Server database port

Important: Restrict port 1433 to trusted application servers or your own IP address. Never expose the SQL Server port to the public internet.

  • Select your EC2 key pair and launch the instance.
2

Connect via SSH

When your instance is running and has passed both status checks (2/2), connect over SSH:

ssh -i your-key.pem ec2-user@<public-ip-address>

To switch to the root user, run:

sudo su -

Important: Wait for the instance to reach 2/2 successful status checks before you connect. While it is still initialising, connection attempts can fail with "Permission denied" errors.

3

Retrieve the SA Password

The SA (System Administrator) password is randomly generated at build time and stored in a log file on the instance. Read it with:

cat /stage/scripts/mssql_admin_password.log

Store this password securely. You need it to connect to SQL Server.

4

Connect to SQL Server

Connect to the local SQL Server instance with the sqlcmd command-line tool:

sqlcmd -S localhost -U SA

When prompted, enter the SA password you retrieved in Step 3.

The prompt appears as follows:

1>

You are now connected and can run T-SQL queries.

5

Create a Database and Table

Create a new database:

CREATE DATABASE MyAppDB; GO

Switch to the new database:

USE MyAppDB; GO

Create a table:

CREATE TABLE Users ( UserID INT IDENTITY(1,1) PRIMARY KEY, Name NVARCHAR(100), Email NVARCHAR(255), CreatedAt DATETIME DEFAULT GETDATE() ); GO

Insert data:

INSERT INTO Users (Name, Email) VALUES ('John Doe', 'john@example.com'); GO

Query the data:

SELECT * FROM Users; GO

Type QUIT to exit the sqlcmd shell.

Server Components

Component Software Home

SQL Server 2022 Standard /opt/mssql

Note: On first boot, the OS package update script can update component versions when the system package repositories offer newer ones.

Filesystem Layout

Mount Point Size Description

/ 38 GB Root filesystem

/boot 2 GB Operating system kernel files

/opt/mssql 9.8 GB SQL Server installation directory

Managing the SQL Server Service

systemd manages SQL Server, and the service starts automatically on boot.

Check the service status:

systemctl status mssql-server

Stop SQL Server:

systemctl stop mssql-server

Start SQL Server:

systemctl start mssql-server

Restart SQL Server:

systemctl restart mssql-server

Scripts and Log Files

Script/Log Path Description

initial_boot_update.sh /stage/scripts Updates the OS with the latest packages on first boot

initial_boot_update.log /stage/scripts Output log for the boot update script

mssql_admin_password.log /stage/scripts Contains the randomly generated SA password

On Startup

An OS package update script runs on first boot to bring the image fully up to date. To disable it, remove the script and its crontab entry:

rm -f /stage/scripts/initial_boot_update.sh crontab -e
# Delete the following line, save and exit: @reboot /stage/scripts/initial_boot_update.sh

Troubleshooting

Cannot connect via SSH

  • Wait for the instance to reach 2/2 status checks.
  • Verify that your security group allows port 22 from your IP.
  • Confirm that you are using the correct key pair and connecting as ec2-user.
  • Check the instance system log in the AWS Console for boot errors.

SQL Server service not running

  • Check the service status: systemctl status mssql-server
  • Review the SQL Server error log: cat /var/opt/mssql/log/errorlog
  • Verify that there is enough disk space: df -h /opt/mssql
  • Make sure the instance has at least 2 GB RAM.

Cannot connect to SQL Server on port 1433

  • Verify that the service is running: systemctl status mssql-server
  • Check that the security group allows port 1433 from your IP.
  • Test local connectivity: sqlcmd -S localhost -U SA
  • Check that SQL Server is listening: ss -tlnp | grep 1433

SA password not working

  • Read the password again: cat /stage/scripts/mssql_admin_password.log
  • Copy the entire password, without extra whitespace.
  • If needed, reset the SA password with mssql-conf:
sudo systemctl stop mssql-server
sudo /opt/mssql/bin/mssql-conf set-sa-password
sudo systemctl start mssql-server

High memory usage

  • By design, SQL Server Standard can use up to 128 GB of buffer pool memory.
  • Configure memory limits with mssql-conf:
sudo /opt/mssql/bin/mssql-conf set memory.memorylimitmb 4096
sudo systemctl restart mssql-server
  • Choose an instance type with enough RAM for your workload.

Ubuntu package update issues

  • If the boot update script reports errors, check its log: cat /stage/scripts/initial_boot_update.log
  • Run a manual update: sudo apt update && sudo apt upgrade -y
  • Verify that the Microsoft SQL Server repository is configured: cat /etc/apt/sources.list.d/mssql-server*.list

Security Recommendations

  • Restrict port access. Allow SQL Server port 1433 only from trusted application servers or specific IP addresses.
  • Change the SA password. After your first login, replace the generated password with a strong, unique one.
  • Create application-specific logins. Do not use the SA account for application connections. Create dedicated logins with the minimum permissions they need.
  • Enable encrypted connections. Configure SQL Server to use TLS/SSL for client connections.
  • Enable ledger tables. SQL Server 2022 supports ledger databases for tamper-evident record keeping of sensitive data.
  • Keep SQL Server updated. Apply cumulative updates and security patches when they are available.
  • Keep Ubuntu updated. Run sudo apt update && sudo apt upgrade regularly to apply OS security patches.
  • Monitor access. Review the SQL Server error logs regularly for failed login attempts.
  • Configure backups. Set up regular database backups with SQL Server Agent or AWS Backup.
  • Use AWS security features. Use VPC security groups, NACLs and private subnets to restrict network access.
  • Secure the password file. After you retrieve the SA password, consider removing /stage/scripts/mssql_admin_password.log or restricting access to it.
  • Configure the UFW firewall. Ubuntu includes the Uncomplicated Firewall. For defence in depth, consider enabling it with rules that match your security group.

Support

If you encounter any issues with this product, contact SecureAnyCloud support:

  • Email: sales@secureanycloud.com
  • Website: secureanycloud.com
  • Support hours: 24/7 with guaranteed 24 hour response SLA

Deploy on AWS

Launch SQL Server 2022 Standard on Ubuntu Server 22.04 with Support by SecureAnyCloud with 24/7 support from SecureAnyCloud.

View on Marketplace

Need Help?

Our support team is available 24/7.

sales@secureanycloud.com