はじめに

現場のセキュリティ対応で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のみになっており、作成したアカウントでログインできず…①の対応を行ったという結果となります。
トラブル対応からも色々と学ぶことが多いです。

参考サイト

SQL Server - 完全にロックアウトされてログインできない時の対処方法

目次

はじめに

①SQLServer認証でログインできるように設定をする。

②CLIからユーザー作成をしてログインをする。

最後に

参考サイト