Problem

SQL Server Express is a free edition of Microsoft's SQL Server, which is a relational database management system (RDBMS). It is designed for small-scale applications and for users who are getting started with learning about databases and SQL Server. However, remote access is not enabled by default on this SKU. To access a SQL Server Express instance from the network, we have to do a few steps. There's plenty of guide on the internet telling you how to enable remote access via GUI, but I would like to do it from PowerShell, so that I can integrate those steps in automated environment.

Solution

Steps

To enable remote access for SQL Server Express, the general steps are

Check "Allow remote connections to this server" checkbox (Already checked in a new SQL Server 2022 Express installation)

  1. Enable TCP/IP Protocol
  2. Set TCP port to 1433
  3. Set Windows Firewall to allow TCP 1433
  4. Restart related services

You can refer to a GUI guide here: https://www.apesoftware.com/calibration-control/help/sql-remote-connections

PowerShell

Thanks to Azure Open AI, I don't have to work 996 this time to make the PowerShell script. The script generally is automating the steps above.

# Run this script as an Administrator

# Step 1: Set SQL Server Browser service to start automatically and start the service
Write-Host "Setting SQL Server Browser service to Automatic and starting the service..."
Set-Service 'SQLBrowser' -StartupType Automatic
Start-Service 'SQLBrowser'

# Step 2: Enable TCP/IP protocol for SQL Server
# Note: This step requires SQL Server Configuration Manager to be installed.
Write-Host "Enabling TCP/IP protocol for SQL Server..."
$sqlServerConfigManager = Get-WmiObject -Namespace "root\Microsoft\SqlServer\ComputerManagement16" -Class "ServerNetworkProtocol"
$tcpIp = $sqlServerConfigManager | Where-Object { $_.InstanceName -eq 'SQLEXPRESS' -and $_.ProtocolName -eq 'Tcp' }
$tcpIp.SetEnable()

# Step 3: Set the TCP Port for IPAll to 1433
Write-Host "Setting the TCP Port for IPAll to 1433..."
$regPath = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL16.SQLEXPRESS\MSSQLServer\SuperSocketNetLib\Tcp\IPAll"
Set-ItemProperty -Path $regPath -Name TcpPort -Value "1433"
Set-ItemProperty -Path $regPath -Name TcpDynamicPorts -Value ""

# Step 4: Create a firewall rule to allow TCP port 1433
Write-Host "Creating a firewall rule for TCP port 1433..."
New-NetFirewallRule -DisplayName "SQL Server Remote Access" -Direction Inbound -Protocol TCP -LocalPort 1433 -Action Allow

Write-Host "SQL Server Express should now accept remote connections."

Restart-Service 'MSSQL$SQLEXPRESS'
Restart-Service 'SQLBrowser'

Please note, this script is for SQL Server 2022 Express, with default instance name "SQLExpress". If you are targeting other version of SQL Server, you should change these items in the script.

  • ComputerManagement16
  • MSSQL16.SQLEXPRESS
  • SQLEXPRESS

To connect to your SQL Server Express instance via network. There are also a few things to keep in mind.

  • Add port number in server name, that is "IP\SQLExpress,1433", NOT "IP\SQLExpress".
  • Trust server certificate if SSMS throw error about certificate.