Enable Remote Access to Microsoft SQL Server 2022

After installation of Microsoft SQL Server, there is always a need to access it and manage from a GUI tool, this can be locally or remotely. For local installation, it is much easier to connect to the database with windows OS user credentials. This article is targeting remote desktop users who want to connect to a SQL Server instance or database, and communicate using Transact-SQL (T-SQL). For this option we shall use SQL Server IP address and allow connections if Firewall is active (which is correct state by default).

For local users within Active Directory domain, you can identify instance name if connection using the name is desired. I prefer running the commands from PowerShell, to do this start PowerShell as Administrator and execute the following commands.

Get-ItemProperty -Path "HKLM:\Software\Microsoft\Microsoft SQL Server" | Out-String -Stream | Select-String "InstalledInstances"

Sample output:

InstalledInstances : {MSSQLSERVER}

For a connection from remote host devices to the SQL Server, we need to update the authentication mode to mixed. This basically means we have both the SQL Server authentication and Windows authentication methods enabled.

Change the mode from PowerShell with the commands below.

Set-ItemProperty -Path "HKLM:\Software\Microsoft\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQLSERVER" -Name "LoginMode" -Value 2

Current set value can then be confirmed by using the commands below.

Get-ItemProperty -Path "HKLM:\Software\Microsoft\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQLSERVER" -Name "LoginMode"

Here is an output from my command execution.

LoginMode    : 2
PSPath       : Microsoft.PowerShell.Core\Registry::HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL
               Server\MSSQL16.MSSQLSERVER\MSSQLSERVER
PSParentPath : Microsoft.PowerShell.Core\Registry::HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL
               Server\MSSQL16.MSSQLSERVER
PSChildName  : MSSQLSERVER
PSDrive      : HKLM
PSProvider   : Microsoft.PowerShell.Core\Registry

Enable TCP/IP setting on SQL server to unlock remote IP based connections into the server instance.

Set-ItemProperty -Path "HKLM:\Software\Microsoft\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\Tcp" -Name "Enabled" -Value 1
Get-ItemProperty -Path "HKLM:\Software\Microsoft\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\Tcp" -Name "Enabled"

Confirm the current settings by running below commands.

PS C:\Users\Administrator> Get-Service | Out-String -Stream | Select-String "SQL"

Running  MsDtsServer160     SQL Server Integration Services 16.0
Running  MSSQLFDLauncher    SQL Full-text Filter Daemon Launche...
Running  MSSQLSERVER        SQL Server (MSSQLSERVER)
Running  MSSQLServerOLAP... SQL Server Analysis Services (MSSQL...
Stopped  SQLBrowser         SQL Server Browser
Stopped  SQLSERVERAGENT     SQL Server Agent (MSSQLSERVER)
Running  SQLTELEMETRY       SQL Server CEIP service (MSSQLSERVER)
Running  SQLWriter          SQL Server VSS Writer
Running  SSASTELEMETRY      SQL Server Analysis Services CEIP (...
Running  SSISTELEMETRY160   SQL Server Integration Services CEI...

Once validated restart SQL Server instance.

PS C:\Users\Administrator> Restart-Service MSSQLSERVER

We can then allow SQL server port (1433) on the firewall.

New-NetFirewallRule -Name "MSSQL" `
-DisplayName "SQL Server" `
-Description "Allow Database Engine" `
-Profile Any `
-Direction Inbound `
-Action Allow `
-Protocol TCP `
-Program Any `
-LocalAddress Any `
-RemoteAddress Any `
-LocalPort 1433 `
-RemotePort Any 

Connect to SQL Server on localhost using Windows authentication.

PS C:\Users\Administrator> sqlcmd -S localhost 

Enable SQL Server admin user (SA) and set a password for the user.

1> alter login sa enable;
2> alter login sa with password = 'STrongP#w0rdW';
3> go

1> exit

Remember to replace STrongP#w0rdW under single quotes with your desired password for sa database admin user.

Validate connection using the set password.

PS C:\Users\Administrator> sqlcmd -S localhost -U SA
Password:
1>

If successful then proceed to test SSMS remote connection in the next section.

Configure SQL Server Management Studio (SSMS)

SQL Server Management Studio (SSMS) is a client utility for managing Microsoft SQL server locally or remotely. It gives you the tools for configuring, monitoring, and administering instances of SQL Server and databases.

Download and Install SQL Server Management Studio (SSMS) from the official Microsoft website.

Create new connection from Connect –> Database Engine. Input the following details

• Select Database Engine for Server type
• IP address of the server for Server name
• SQL Server Authentication for Authentication method
• SA as Login username
• Provide password is configured earlier for the sa user.

When done hit the “Connect” button to initiate the connection. You should see database explorer loaded on the left.

Enjoy administering your SQL Server from you local or remote hosts with secure username and password authentication. Remember this method should only be used where local access to the database is not available. For instances running in the cloud with a public IP address, you can enable the access through a private subnet where applicable.

Thank you for using our article to configure remote connections to Microsoft SQL Server 2022. We hope this was helpful to you. Until next time, cheers!.