目次
- はじめに
- ①SQLServer認証でログインできるように設定をする。
- ②CLIからユーザー作成をしてログインをする。
- 最後に
- 参考サイト
はじめに
現場のセキュリティ対応でSQLServerからlocal/AdministratorsのグループをLoginsのエントリーから外さないといけません。
何も迂回路を作らず、指示通りに二つ返事でAdministratorsグループをLoginsから削除したところ、見事にSQLServerに接続できなくなったぞ！
もちろん事前にsaも無効化しているので、万全にログインできません。
というトラブル対応のお手伝いをしました。
解決法として、CLIからSQLServer認証のログインアカウントを作成し、無事復旧させることができました。
という、1つ学んだトラブル対応方法のメモです。
現場での環境はSQLServer2016ですが、対応方法としてバージョンの大差はないと思います。
①SQLServer認証でログインできるように設定をする。
SQLServerのインストール時や、SQLServerManagementStudio(以下SSMS)のプロパティにて、ログイン時の認証を設定する項目があり、
Windows認証のみか、Windows認証とSQLServer認証の両方を使用できるようにするか選択する項目があります。
しかし、SQLServerにログインできない状態ではSSMSからプロパティを変更することができないので、レジストリエディタを使って該当する箇所の値を変更します。
レジストリエディタから、
Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL16.[インスタンス名]\MSSQLServer
に行くと[LoginMode]という項目があります。
こちらの値が1の場合、Windows認証のみでのログイン、2の場合はSQLServer認証も可能となります。
この値が2でないと後続の対応ができません。
②CLIからユーザー作成をしてログインをする。
SQLServerの構成マネージャーを開き、SQLServerを停止させます。
ServerAgentが起動していたらこの時に同時に止めてください。
SQL Serverを右クリック→プロパティ→スタートアップのタブにて
起動オプションに-mだけの項目を追加します。
-mをつけることでシングルユーザーモード(特権モード)で起動となるようです。
プロパティを閉じて、SQLServerのサービスを起動。
この時、Agentは立ち上げないように。奪われてしまいログインできなくなります。
次に、Powershellを実行し、SQLサーバーへ接続します。
SQLCMD -S [サーバーホスト名]
以下のコマンドを入力してログインIDを作成、
作成したIDにsysadmin権限を付与する
CREATE LOGIN [臨時のログインID] WITH PASSWORD='[パスワード]'
GO
EXEC sp_addsrvrolemember [臨時のログインID], sysadmin
GO
以下コマンド実行例
PS C:\Users\Administrator> SQLCMD -S localhost
1> CREATE LOGIN LOGINUSER WITH PASSWORD='Password123'
2> GO
1> EXEC sp_addsrvrolemember LOGINUSER, sysadmin
2> GO
1> quit
PS C:\Users\Administrator>
コマンドが正常に通ったら、構成マネージャーで先ほど操作した起動オプションから-mを外して、SQLSERVERのサービスを再起動します。
これで作成したアカウントからSQLServerにログインできるようになりました。
最後に
①と②は違うサーバーで起こりました。
1つ目のサーバーで行った対処は②のみで、
2つ目のサーバーで万全の対策をして臨んだところ、認証方法がWindowsのみになっており、作成したアカウントでログインできず…①の対応を行ったという結果となります。
トラブル対応からも色々と学ぶことが多いです。