Windows 2022
Sponsored Link

SQL Server 2022 : Remote Connection : Windows Auth
2023/01/13
 
If you'd like to connect to SQL Server Database Engine Service from remote Hosts with Windows authentication method, configure like follows.
It needs that target SQL Server Host and source Windows client Host are in the same Active Directory domain to use Windows authentication method.
[1] To use Windows authentication on remote client Hosts, enable TCP/IP setting on SQL Server.
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}

# enable TCP/IP setting
# MSSQL16.(confirmed instance name above)
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...

# confirm logon account of database instance service
PS C:\Users\Administrator>  Get-WmiObject Win32_Service -filter "name='MSSQLSERVER'" | Select-Object -Property "StartName" 

StartName
---------
NT Service\MSSQLSERVER

# if the logon account of database instance service is local account,
# change it to a domain user account
# * the domain user also needs to have admin privilege to the local Windows system
PS C:\Users\Administrator> (Get-WmiObject Win32_Service -filter "name='MSSQLSERVER'").Change($null,$null,$null,$null,$null,$null,"FD3S01\SQLAdmin","P@ssw0rd01") 

__GENUS          : 2
__CLASS          : __PARAMETERS
__SUPERCLASS     :
__DYNASTY        : __PARAMETERS
__RELPATH        :
__PROPERTY_COUNT : 1
__DERIVATION     : {}
__SERVER         :
__NAMESPACE      :
__PATH           :
ReturnValue      : 0
PSComputerName   :

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

# instance service starts to listen on port 1433 (default)
PS C:\Users\Administrator> netstat -a | Select-String "1433" 

  TCP    0.0.0.0:1433           rx-8:0                 LISTENING
  TCP    [::]:1433              rx-8:0                 LISTENING

# 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 

# add domain account to logins of database instance
PS C:\Users\Administrator> sqlcmd -S localhost 

# show current logins
1> select sp.name as login from sys.server_principals sp 
2> go 
login 
------------------------------------------
sa                                        
public                                    
sysadmin                                  
securityadmin                             
serveradmin                               
setupadmin                                
processadmin                              

.....
.....

RX-8\Administrator                        
NT SERVICE\SQLWriter                      
NT SERVICE\Winmgmt                        
NT Service\MSSQLSERVER                    
NT AUTHORITY\SYSTEM                       
NT SERVICE\SQLSERVERAGENT                 
NT SERVICE\SQLTELEMETRY                   

# add a domain account to logins
1> create login [FD3S01\SQLAdmin] from windows 

# if you'd like to set the user an admin account, add [sysadmin] role
2> exec sp_addsrvrolemember 'FD3S01\SQLAdmin', 'sysadmin' 

3> go 

1> exit 
SQL Server 2022 : Remote Connection : Windows 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] Select [SQL Server Services] on the left pane and open [Properties] on the right-clicking menu of [SQL Server (instance name)] on the right pane.
[4] Move to [Log on] tab and change logon account setting to a domain account if current account is a local account.
The domain account also needs to have admin privilege to the local Windows system.
[5] Right click the [SQL Server (instance name)] and run [Restart] to apply changes.
[6] If Windows Firewall is running, allow database engine service port (1433 by default).
[7] Run SSMS (SQL Server Management Studio) and connect to database instance, then add domain accounts to Logins like follows. If you'd like to set the user an admin account, add [sysadmin] role, too.
SQL Server 2022 : Remote Connection : Windows Auth (Clients)
[8]
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) first.
⇒ https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms
After installing SSMS, logon to Windows as a domain account and run SSMS to connect to Databace service with Windows Authentication method.
[9] That's OK if successfully connected like follows.
[10]
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