SQL Server 2019 : Connect from remote Hosts2020/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.
Change authentication mode.If Windows Firewall is running, allow [1433/TCP]. 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).
After installing SSMS, run it and select [SQL Server Authentication] and also specify [SA] for login user to login with remote connection.
⇒ https://docs.microsoft.com/ja-jp/sql/ssms/download-sql-server-management-studio-ssms |
|
| [10] | Just logined. |
|
| [11] |
If you'd like to connect with command, use [sqlcmd].
After installing [sqlcmd] command, it's possbile to connect to database on CUI operation.
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 |
|
Matched Content