SQL Server 2022 Developer on Windows Server 2022 on AWS User Guide
Overview
This image is Microsoft SQL Server 2022 Developer edition (16.0.4325.4, cumulative update 28, KB5125472) on Windows Server 2022 Datacenter, the cloudimg SQL Server 2022 Developer on Windows Server 2022 listing on AWS Marketplace.
For development, test and demonstration only, never production. SQL Server 2022 Developer edition has every Enterprise edition feature, and Microsoft licenses it for development, test and demonstration only, never production. It needs no SQL Server licence. Do not run production workloads on it. The Windows Server licence is included by AWS in the EC2 instance price. For production, move your databases to a licensed edition (see Licensing).
Price: the listing costs $0.04 per vCPU-hour for what cloudimg adds (below), plus AWS infrastructure charges (the EC2 Windows instance price includes the Windows Server licence). An m5.large has 2 vCPUs, so the cloudimg charge on it is $0.08 per hour. The listing charges the same rate per vCPU on every instance type it offers.
SQL Server is completed on your instance at first boot. Allow about 15 minutes after launch before SQL Server is ready. The progress log is
C:\ProgramData\cloudimg\mssql-firstboot.log.
What cloudimg does to the image:
- SQL Server 2022 from Microsoft's own media. The build downloads the Microsoft SQL Server 2022 installer from microsoft.com and installs the Developer edition Database Engine (default instance
MSSQLSERVER) and thesqlcmdcommand-line tools, with the latest cumulative update at build time (16.0.4325.4, cumulative update 28, KB5125472). The setup media stays inC:\SQLServerSetupso you can add features or repair the installation. - No SQL Server instance and no password in the image. The AMI carries the SQL Server binaries only (Microsoft's "prepare image" step of SQL Server sysprep). A one-time task,
cloudimg-mssql-firstboot, completes the instance on your instance's first boot. The server name, the service master key, the system databases and thesapassword are all created on your instance and are unique to it. - A unique sa password that only Administrators can read. Authentication is mixed mode. First boot generates a random
sapassword on the instance and writes it toC:\ProgramData\cloudimg\mssql\mssql-credentials.txt, which only the Administrators group and SYSTEM can open. It is never put on a command line or in a log. Before the file is written, first boot proves that the new password logs in and that a wrong one is refused. - Windows authentication for the Administrator. The local Administrators group (
BUILTIN\Administrators) is a member of thesysadminrole, so the Administrator signed in over Remote Desktop can usesqlcmd -Ewithout a password. - Port 1433 closed from outside by default. TCP/IP is enabled and SQL Server listens on TCP 1433, but the Windows Firewall has no inbound rule for 1433 and the listing's security group opens only Remote Desktop (3389). You open 1433 deliberately, to your own network only (see Allow remote client connections). Named Pipes is off and the SQL Server Browser service is disabled.
- SQL Server telemetry off. The SQL Server CEIP telemetry service (
SQLTELEMETRY) is disabled. - Microsoft Security Baseline for Windows Server 2022 (Member Server), applied with Microsoft's LGPO tool and measured with HardeningKitty v.0.9.4 on a fresh instance: 319 of 320 scored rules pass (99.7%), with 8 rules waived for EC2 (listed below). The one rule that differs is by design: "Perform volume maintenance tasks" (10320) also lists the SQL Server service account, because first boot turns on instant file initialization, which Microsoft recommends for SQL Server data files. The report ships in
C:\ProgramData\cloudimg\baseline\. This is a measurement made with open-source tooling against Microsoft's published baseline, not a certification by Microsoft or any other body. - Fully patched Windows at build time (OS build 20348.5870).
- A unique Administrator password on every instance (Sysprep through EC2Launch v2; retrieve it with your key pair). Remote Desktop (3389), with Network Level Authentication, is the only inbound port open.
- IMDSv2 only, and no build residue: build tools, downloaded installers other than the SQL Server setup media, the build's logs and history are removed and free space is wiped before capture.
Connecting to your instance
Connect with Remote Desktop as the Administrator:
| Operating system variant | Remote Desktop login user |
|---|---|
| Windows Server 2022 Datacenter | Administrator |
Launch from AWS Marketplace
- Open the listing on AWS Marketplace and choose View purchase options, then Subscribe.
- Once the subscription is active, choose Launch your software.
- The listing has one software version. Choose your Region and an instance type.
m5.large(2 vCPU, 8 GiB) is the recommended starting size; use a larger instance for heavier test workloads. Use an instance with at least 4 GiB of memory (t3.medium or larger). The listing charges $0.04 per vCPU-hour, the same rate on every instance type it offers, plus AWS infrastructure charges. - Choose a key pair. You need its private key to get the Administrator password.
- Use a security group that allows RDP (TCP 3389) from your own address only. The listing's default security group opens only 3389; restrict its source to your address range. Do not open 1433 yet.
- Optional but recommended: attach an IAM instance profile with the
AmazonSSMManagedInstanceCorepolicy, so you can reach SQL Server from your workstation with Session Manager port forwarding (see Connect from your workstation) without opening any port. - Launch. The root volume is 75 GiB; you can choose a larger volume at launch, or add a data volume later (see Filesystem layout).
Wait for first boot
After Windows finishes its first boot, the scheduled task cloudimg-mssql-firstboot (running as SYSTEM) completes the SQL Server instance. Allow about 15 minutes after launch. The Administrator password is ready after about four minutes, so you can sign in and watch. Check progress in PowerShell:
Get-Content C:\ProgramData\cloudimg\mssql-firstboot.log -Tail 5
Test-Path C:\ProgramData\cloudimg\mssql-firstboot.done
SQL Server is ready when the done marker C:\ProgramData\cloudimg\mssql-firstboot.done exists (Test-Path prints True). To follow the log live in a Remote Desktop session, run Get-Content C:\ProgramData\cloudimg\mssql-firstboot.log -Wait.
If first boot fails, the file C:\ProgramData\cloudimg\mssql-firstboot.failed says why and no credentials are written. The next boot tries again (up to three attempts), so restart the instance to retry, or contact cloudimg support.
Get the Administrator password
In the EC2 console select the instance, choose Actions, Security, Get Windows password, upload the private key of your key pair and choose Decrypt password. Or with the AWS CLI on your workstation:
aws ec2 get-password-data --region <region> --instance-id <instance-id> \
--priv-launch-key ~/.ssh/my-key-pair.pem --query PasswordData --output text
An empty result means Windows has not finished setting the password yet; try again a minute later. Every instance has a different password.
Sign in with Remote Desktop
Connect to the instance's public DNS name or IP address on port 3389 with a Remote Desktop client that supports Network Level Authentication (Microsoft Remote Desktop on Windows and macOS, FreeRDP on Linux), as Administrator with the password from the previous step. Each instance generates its own Remote Desktop certificate, so your client asks you to trust it on first connection.
Read the sa password
Open an elevated PowerShell (Run as administrator) in the Remote Desktop session and read the credentials file:
Get-Content C:\ProgramData\cloudimg\mssql\mssql-credentials.txt
| Key | What it is |
|---|---|
SQL_INSTANCE |
MSSQLSERVER, the default instance |
SQL_PORT |
1433 |
SA_USER |
sa |
SA_PASSWORD |
The password generated on this instance |
Store it in your password manager. If you copy it anywhere else, change it (see Change or disable sa).
Check the services
The Database Engine runs as the MSSQLSERVER service and SQL Server Agent as SQLSERVERAGENT. The SQL Server Browser (SQLBrowser) and telemetry (SQLTELEMETRY) services are disabled:
Get-Service MSSQLSERVER, SQLSERVERAGENT, SQLBrowser, SQLTELEMETRY | Select-Object Name, Status, StartType
MSSQLSERVER must show Running.
Connect with sqlcmd
As the Administrator you can use Windows authentication (-E), with no password. Check the instance, its edition and its version:
sqlcmd -S localhost -E -b -W -Q "SELECT @@SERVERNAME AS server_name, SERVERPROPERTY('Edition') AS edition, SERVERPROPERTY('ProductVersion') AS version, SERVERPROPERTY('ProductUpdateLevel') AS cu"
server_name is your instance's own name, set at first boot, and edition reads:
Developer Edition (64-bit)
Confirm mixed-mode authentication (0 means SQL Server and Windows authentication are both allowed) and the default data and log folders:
sqlcmd -S localhost -E -b -W -Q "SELECT SERVERPROPERTY('IsIntegratedSecurityOnly') AS windows_auth_only, SERVERPROPERTY('InstanceDefaultDataPath') AS data_path, SERVERPROPERTY('InstanceDefaultLogPath') AS log_path"
To sign in as sa, leave out the password so sqlcmd prompts for it and it never appears on the command line or in your PowerShell history:
sqlcmd -S localhost -U sa
Paste the SA_PASSWORD value at the Password: prompt, then type T-SQL followed by GO, and EXIT to leave.
Create your first database:
sqlcmd -S localhost -E -b -Q "CREATE DATABASE appdb;"
Change or disable sa
If you only use Windows authentication, disable the sa login:
sqlcmd -S localhost -E -b -Q "ALTER LOGIN sa DISABLE;"
To keep sa but change its password, sign in with sqlcmd -S localhost -E (or SQL Server Management Studio) and run the statement below with a new long random password, so the password is typed into the session and not into your shell history:
ALTER LOGIN sa WITH PASSWORD = '<new password>';
GO
After a change, update or delete mssql-credentials.txt. For your applications, create their own logins with only the permissions they need rather than using sa.
Allow remote client connections
SQL Server listens on TCP 1433:
Get-NetTCPConnection -LocalPort 1433 -State Listen | Select-Object LocalAddress, LocalPort, OwningProcess
but nothing outside the instance can reach it. The Windows Firewall lets in only Remote Desktop; this lists the TCP ports it allows inbound, and 1433 is not among them:
Get-NetFirewallRule -Direction Inbound -Enabled True -Action Allow | Get-NetFirewallPortFilter | Where-Object Protocol -eq 'TCP' | Select-Object -ExpandProperty LocalPort -Unique
TCP/IP is enabled (1) and Named Pipes is off (0):
$net = 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQLServer\SuperSocketNetLib'
Get-ItemProperty "$net\Tcp", "$net\Np" -Name Enabled | Select-Object PSChildName, Enabled
To let a client network connect, open 1433 deliberately, in both places, from your own address range only:
- In the Windows Firewall, allow 1433 from your client network (replace
203.0.113.0/24with your own CIDR):
powershell
New-NetFirewallRule -DisplayName 'SQL Server 1433 from my network' -Direction Inbound -Protocol TCP -LocalPort 1433 -RemoteAddress 203.0.113.0/24 -Action Allow
- Add a matching inbound rule for TCP 1433 to the instance's security group, from the same CIDR. In the EC2 console: Security groups, the instance's group, Edit inbound rules, Add rule, type MS SQL, source your CIDR. Or with the AWS CLI on your workstation:
bash
aws ec2 authorize-security-group-ingress --region <region> --group-id <sg-id> \
--protocol tcp --port 1433 --cidr 203.0.113.0/24
Never open 1433 to 0.0.0.0/0. SQL Server on the internet is scanned and attacked for sa constantly. Opening the port in the security group alone is not enough: the Windows Firewall still blocks it until you add the rule above.
Clients connect to <public-ip-or-dns>,1433. SQL Server encrypts the login with a self-signed certificate unless you install your own, so recent clients (SQL Server Management Studio 20 and later, ODBC Driver 18) need Trust server certificate ticked, or a certificate from your own certificate authority configured as Microsoft's Configure SQL Server Database Engine for encryption describes.
Connect from your workstation
If you would rather not open any port, use AWS Systems Manager Session Manager port forwarding. With the instance profile from the launch step and the Session Manager plugin installed on your workstation:
aws ssm start-session --region <region> --target <instance-id> \
--document-name AWS-StartPortForwardingSession \
--parameters portNumber=1433,localPortNumber=1433
Leave it running and connect your client to localhost,1433 as sa (or another SQL login you create).
Optional: SQL Server Management Studio
SQL Server Management Studio (SSMS) is not in the image. It is a download from Microsoft: Download SQL Server Management Studio. Install it on your workstation and connect over Session Manager port forwarding or through the port you opened, or install it on the instance and connect to localhost with Windows authentication.
Server components
| Component | Version | Notes |
|---|---|---|
| Microsoft SQL Server 2022 Developer edition | 16.0.4325.4, cumulative update 28, KB5125472 | Database Engine, default instance MSSQLSERVER |
| sqlcmd | Installed by SQL Server setup | Command-line tools, on the PATH |
| SQL Server setup media | Same release | C:\SQLServerSetup |
| Windows Server 2022 Datacenter | 20348.5870 | Fully patched at build time |
| HardeningKitty | v.0.9.4 | C:\ProgramData\cloudimg\tools\HardeningKitty-0.9.4 |
| EC2Launch v2, AWS Systems Manager agent | Current at build time | Part of the Windows base |
Filesystem layout
| Drive | Size | Purpose |
|---|---|---|
C: |
75 GiB | The only volume: Windows, SQL Server and, by default, your databases |
Key paths:
| Path | What it is |
|---|---|
C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA |
Default data and log folder (system and user databases) |
C:\SQLServerSetup |
SQL Server 2022 setup media |
C:\ProgramData\cloudimg\mssql\mssql-credentials.txt |
The sa password (Administrators and SYSTEM only) |
C:\ProgramData\cloudimg\mssql-firstboot.log |
First boot progress log |
C:\ProgramData\cloudimg\baseline\ |
Security baseline report |
For larger test databases, keep them on a separate EBS volume (gp3), so you can size, snapshot and tune it independently of Windows. Create and attach the volume in the EC2 console (Volumes, Create volume in the instance's Availability Zone, then Attach volume), then in an elevated PowerShell on the instance bring it online, format it with the 64 KB allocation unit Microsoft recommends for SQL Server data files, give the SQL Server service access and make it the default location for new databases:
$disk = Get-Disk | Where-Object PartitionStyle -eq 'RAW' | Select-Object -First 1
if ($disk.IsOffline) { $disk | Set-Disk -IsOffline $false }
$disk | Initialize-Disk -PartitionStyle GPT -PassThru |
New-Partition -DriveLetter D -UseMaximumSize |
Format-Volume -FileSystem NTFS -AllocationUnitSize 65536 -NewFileSystemLabel SQLData -Confirm:$false
New-Item -ItemType Directory D:\SQLData, D:\SQLLog | Out-Null
icacls D:\SQLData /grant 'NT SERVICE\MSSQLSERVER:(OI)(CI)F'
icacls D:\SQLLog /grant 'NT SERVICE\MSSQLSERVER:(OI)(CI)F'
sqlcmd -S localhost -E -b -Q "EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'D:\SQLData'; EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, N'D:\SQLLog';"
Restart-Service MSSQLSERVER -Force
New databases then go to D:. If you use SQL Server Agent, check it is running again afterwards (Get-Service SQLSERVERAGENT). Move existing user databases with detach and attach, or backup and restore.
Security baseline
Everything the build measured ships in C:\ProgramData\cloudimg\baseline\:
| File | What it is |
|---|---|
baseline-report.html |
Every rule audited, its result and the recommended value; waived rules marked |
hardeningkitty-report.csv |
The raw HardeningKitty audit |
baseline-summary.json |
Rules audited, waived, scored and passed, the pass rate and threshold |
waivers.txt / waivers.json |
The waived rules and why |
Get-Content C:\ProgramData\cloudimg\baseline\baseline-summary.json | ConvertFrom-Json |
Select-Object profile, audited, waived, scored, pass, fail, ratePct, thresholdPct, evaluatedAt
Re-run the audit yourself
HardeningKitty v.0.9.4 stays in the image. Run it in an elevated PowerShell; it only reads settings and takes a minute or two:
$hk = 'C:\ProgramData\cloudimg\tools\HardeningKitty-0.9.4'
Import-Module "$hk\HardeningKitty.psm1" -Force
$list = "$hk\lists\finding_list_msft_security_baseline_windows_server_2022_21h2_member_machine.csv"
$csv = Join-Path $env:TEMP 'hardeningkitty-now.csv'
Remove-Item $csv -ErrorAction SilentlyContinue
$null = Invoke-HardeningKitty -Mode Audit -FileFindingList $list -Report -ReportFile $csv -SkipMachineInformation *>&1
$waived = (Get-Content -Raw C:\ProgramData\cloudimg\baseline\waivers.json | ConvertFrom-Json).waivers.id
$scored = @(Import-Csv $csv | Where-Object { $waived -notcontains $_.ID })
$pass = @($scored | Where-Object { $_.Severity -eq 'Passed' }).Count
"{0} of {1} scored rules pass ({2:N1}%)" -f $pass, $scored.Count, (100 * $pass / $scored.Count)
$scored | Where-Object { $_.Severity -ne 'Passed' } | Select-Object ID, Name, Result, Recommended
Waived rules
These rules are set differently on purpose. Each one is in waivers.txt with its reason:
| ID | Rule | Set to | Why |
|---|---|---|---|
| 10311 | Deny access to this computer from the network | Guests | The baseline denies network logon to local administrators. Remote Desktop with Network Level Authentication checks the Administrator's password with a network logon first, so the EC2 Administrator could not sign in. |
| 10312 | Deny log on through Remote Desktop Services | Guests | The baseline denies Remote Desktop to every local account. On a stand-alone EC2 instance the local Administrator is the only way in. |
| 10672-10677 | Device Guard: Virtualization Based Security and Credential Guard (6 rules) | Not configured | Credential Guard needs UEFI Secure Boot. This image boots in legacy BIOS mode on EC2, so the policy could not run and a "pass" would be misleading. |
Two image settings you can undo
Edge's mDNS firewall rule stays off. Microsoft Edge Update creates and enables a Windows Firewall rule, Microsoft Edge (mDNS-In) (UDP 5353), whenever it installs a new Edge, which can happen minutes after launch. A scheduled task, cloudimg-edge-mdns-off, runs as SYSTEM at every startup and whenever a firewall rule is added or changed, and turns off that one rule; it never touches a rule you add. See the task and the rule's state:
Get-ScheduledTask -TaskName 'cloudimg-edge-mdns-off' | Select-Object TaskName, State
Get-NetFirewallRule -Direction Inbound | Where-Object DisplayName -like 'Microsoft Edge (mDNS-In)*' | Select-Object DisplayName, Enabled
If you want Edge's rule back, remove the task first, then enable the rule:
Unregister-ScheduledTask -TaskName 'cloudimg-edge-mdns-off' -Confirm:$false
Get-NetFirewallRule -Direction Inbound | Where-Object DisplayName -like 'Microsoft Edge (mDNS-In)*' | Enable-NetFirewallRule
The page file is cleared at shutdown. ClearPageFileAtShutdown is 1, so Windows overwrites pagefile.sys every time the instance shuts down or restarts and no memory contents stay on the disk. On an instance with a large page file this makes shutdowns and restarts slower. Check it:
Get-ItemProperty 'HKLM:\SYSTEM\CurrentControlSet\Control\Session Manager\Memory Management' -Name ClearPageFileAtShutdown | Select-Object ClearPageFileAtShutdown
To turn it off (it takes effect from the next restart):
Set-ItemProperty 'HKLM:\SYSTEM\CurrentControlSet\Control\Session Manager\Memory Management' -Name ClearPageFileAtShutdown -Value 0 -Type DWord
SQL Server telemetry
The SQL Server CEIP telemetry service (SQLTELEMETRY) is disabled in the image. If you want to send SQL Server usage data to Microsoft, turn the service back on:
Set-Service SQLTELEMETRY -StartupType Automatic
Start-Service SQLTELEMETRY
Patching
Windows is fully patched when the image is built. SQL Server has the latest cumulative update at build time (16.0.4325.4, cumulative update 28, KB5125472). Windows Update installs Windows updates from Settings, Windows Update (or sconfig option 6). To have it offer SQL Server cumulative and security updates too, turn on Receive updates for other Microsoft products under Settings, Windows Update, Advanced options (it is off in the image). Restart when Windows asks; plan the restart, because it stops SQL Server. You can also download a cumulative update yourself from Microsoft's Latest updates for Microsoft SQL Server. Check the patch level with the version query in Connect with sqlcmd.
Backups
Take SQL Server backups (BACKUP DATABASE) to a separate volume and copy them off the instance, for example to Amazon S3, or back up directly to S3 with BACKUP TO URL. EBS snapshots of a running database volume are crash-consistent only unless you use a VSS-aware snapshot (AWS's VSS application-consistent snapshots for Windows).
Licensing
SQL Server 2022 Developer edition is licensed by Microsoft under its own licence terms for development, test and demonstration only, and needs no SQL Server licence; this listing sells none. It must never be used in production: not for live applications, live data or anything your business or customers depend on. It has every Enterprise edition feature, so code you develop and test on it runs on any licensed edition that supports the features you use.
The Windows Server licence is included by AWS in the EC2 instance price. The $0.04 per vCPU-hour charge covers what cloudimg adds to SQL Server and Windows Server (the first-boot completion with per-instance secrets, the closed-by-default network, the security baseline and the patched build); AWS infrastructure charges are extra.
For production, move your databases (backup and restore) to a licensed SQL Server edition: an AWS licence-included SQL Server instance, or your own SQL Server 2022 licences, which on AWS need active Software Assurance (License Mobility through Software Assurance), verified with Microsoft for AWS. Licences without Software Assurance cannot be brought to AWS for SQL Server 2022.
Support
cloudimg's support team is on hand 24 hours a day by email, with a guaranteed response within 24 hours: support@cloudimg.co.uk or https://www.cloudimg.co.uk/support. SQL Server licensing questions go to Microsoft or your Microsoft licensing partner; Windows Server licensing questions go to AWS and Microsoft.
Microsoft, SQL Server and Windows Server are trademarks of the Microsoft group of companies. cloudimg is not affiliated with or endorsed by Microsoft.