Windows 2019
Sponsored Link

SQL Server 2019 : Connect from remote Hosts
2020/01/30
 
If you'd like to connect to SQL Server Database Engine Service from remote Hosts, configure like follows.
This is based on Windows 10 remote client.
[1]
If target SQL Server Host and source Windows client Host are in the same Active Directory domain, it's possbile to connect to Database with Windows authentication from source remote client Host if you logon with domain user. Then, there is no specific change and operation, possible to connect with SSMS or Sqlcmd like here.
[2]
If remote connection environment is not in Active Directory domain environment, it needs to change authentication mode on SQL Server Host 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
# MSSQL15.(confirmed instance name above)
PS C:\Users\Administrator> Set-ItemProperty -Path "HKLM:\Software\Microsoft\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQLSERVER" -Name "LoginMode" -Value 2

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

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

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

Running  MsDtsServer150     SQL Server Integration Services 15.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  SSISTELEMETRY150   SQL Server Integration Services CEI...

# restart SQL Server
PS C:\Users\Administrator> Restart-Service MSSQLSERVER

# if Windows Firewall is running, allow database engine 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 = 'password';
3> go

1> exit

# try to login with SQL Server admin with SQL Server authentication
PS C:\Users\Administrator> sqlcmd -S localhost -U SA
Password:
1>     # logined
[3]
On GUI change operation, Install and use SSMS on SQL Server Host.
If Windows Firewall is running, allow [1433/TCP].
Change authentication mode.
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 Ajuthentication mode] on the right pane.
[5] Right-click instance name and run [Restart].
[6] Next, enable SQL Server 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 enable [Login].
[9]
For remote Host on Windows 10 setting,
Download and Install SQL Server Management Studio (SSMS).
⇒ https://docs.microsoft.com/ja-jp/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.
[10] Just logined.
[11]
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://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-ver15
After installing [sqlcmd] command, it's possbile to connect to database on CUI operation.
Matched Content