SQL Server 2022 : Remote Connection : SQL Server Auth2023/01/13

If you'd like to connect to SQL Server Database Engine Service from remote Hosts with SQL Server authentication method, configure like follows.

It does not need that target SQL Server Host and source Windows client Host are in the same Active Directory domain on SQL Server authentication method.

[1]	To use SQL Server authentication on remote client Hosts, it needs to change authentication mode to mixed mode which are Windows authentication and SQL Server authentication. On CUI setting, Run PowerShell with Admin Privilege and change mode.

Windows PowerShell
Copyright (C) Microsoft Corporation. All rights reserved.

# confirm instance name
PS C:\Users\Administrator> Get-ItemProperty -Path "HKLM:\Software\Microsoft\Microsoft SQL Server" | Out-String -Stream | Select-String "InstalledInstances"

InstalledInstances : {MSSQLSERVER}

# change authentication mode to mixed
# MSSQL16.(confirmed instance name above)
PS C:\Users\Administrator> Set-ItemProperty -Path "HKLM:\Software\Microsoft\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQLSERVER" -Name "LoginMode" -Value 2

PS C:\Users\Administrator> Get-ItemProperty -Path "HKLM:\Software\Microsoft\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQLSERVER" -Name "LoginMode"

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
PS C:\Users\Administrator> Set-ItemProperty -Path "HKLM:\Software\Microsoft\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\Tcp" -Name "Enabled" -Value 1

PS C:\Users\Administrator> Get-ItemProperty -Path "HKLM:\Software\Microsoft\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\Tcp" -Name "Enabled"

Enabled      : 1
PSPath       : Microsoft.PowerShell.Core\Registry::HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL
               Server\MSSQL16.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\Tcp
PSParentPath : Microsoft.PowerShell.Core\Registry::HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL
               Server\MSSQL16.MSSQLSERVER\MSSQLServer\SuperSocketNetLib
PSChildName  : Tcp
PSDrive      : HKLM
PSProvider   : Microsoft.PowerShell.Core\Registry

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...

# restart database instance service
PS C:\Users\Administrator> Restart-Service MSSQLSERVER

# if Windows Firewall is running, allow service port
PS C:\Users\Administrator> 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 database on localhost with Windows authentication
# and enable SQL Server admin and set its password
PS C:\Users\Administrator> sqlcmd -S localhost 

1> alter login sa enable;
2> alter login sa with password = 'P@ssw0rd01';
3> go

1> exit

# verify to login with SA
PS C:\Users\Administrator> sqlcmd -S localhost -U SA
Password:
1>     # logined

SQL Server 2022 : Remote Connection : SQL Server Auth (GUI)

[2]	On GUI change operation, Install and use SSMS on SQL Server Host first. Start [SQL Server 2022 Configuration Manager] from start menu, then select [SQL Server Network Configuration] on the left pane, next, enable [TCP/IP] like follows.

[3]	Run SSMS and connect to database, then right-click the instance name and open [Properties].

[4]	Select [Security] on the left pane and check a box [SQL Server and Windows Authentication mode] on the right pane.

[5]	Right-click instance name and run [Restart].

[6]	Next, enable database instance admin user. Open [Security] - [Logins] on the left pane and right-click [sa] to open [Properties].

[7]	Select [General] on the left pane and set SQL Server admin password.

[8]	Select [Status] on the left pane and check a box [Enabled] on [Login] section.

[9]	If Windows Firewall is running, allow database engine service port (1433 by default).

SQL Server 2022 : Remote Connection : SQL Server Auth (Client)

[10]

Connect to SQL Server Databace service from remote client computer. This is based on Windows 11 client.

Download and Install SQL Server Management Studio (SSMS).
⇒ https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

After installing SSMS, run it and select [SQL Server Authentication] and also specify [SA] for login user to login with remote connection.

[11]	That's OK if successfully connected like follows.

[12]

If you'd like to connect with command, use [sqlcmd].
However, [sqlcmd] is not included in SSMS 18 or later version, so install it manually.
⇒ https://learn.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-ver16

After installing [sqlcmd] command, it's possbile to connect to database on CUI operation.

Matched Content