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

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