SQL Server 2019 : リモートホストから接続する2020/01/29

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

# インスタンス名確認
PS C:\Users\Administrator> Get-ItemProperty -Path "HKLM:\Software\Microsoft\Microsoft SQL Server" | Out-String -Stream | Select-String "InstalledInstances"

InstalledInstances : {MSSQLSERVER}

# 認証モードを混合モードに変更
# MSSQL15.(確認したインスタンス名)
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...

# SQL Server 再起動
PS C:\Users\Administrator> Restart-Service MSSQLSERVER

# Windows Firewall 稼働中の場合は データベースエンジン ポートを許可
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 

# ローカルホストから Windows 認証で接続して
# SQL Server の管理者ユーザーを有効にしてパスワードを設定
PS C:\Users\Administrator> sqlcmd -S localhost 

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

1> exit

# SQL Server の管理者ユーザーで SQL Server 認証でログイン
PS C:\Users\Administrator> sqlcmd -S localhost -U SA
Password:
1>     # ログインできた