【SQLServer】クライアントPCからSMSS、ODBC等の外部接続する設定(ID設定・TCP設定・ファイアウォール)

最近導入した「さくらのVPS」のWindowsServer。
SQLServer設定後にクライアントPCから外部接続する設定方法などのメモを残します。

スポンサーリンク

SMSSでSQLServer用の認証IDとPW設定

Windows認証モードをID&パスワードの認証形式に変更します。


左上のコンピュータ名を右クリック⇒プロパティ⇒セキュリティ⇒「SQL Server認証モードとWindows 認証モード」の方に変更する⇒OKボタン⇒「再起動するまで有効になりません」もOKボタン(上の画像とは違い、実際にはもっと中央にでる)


左上のコンピュータ名を右クリック⇒「再起動」を行います。
2度ほど確認画面がでますがOKを押します。
※ここの再起動はサーバー自体ではなく、SQLサーバー再起動のことです。数十秒程度で終わります。


再起動後、セキュリティ>ログイン で右クリック ⇒ 新しいログイン を選ぶ。



全般(デフォルト)で上部画像の赤丸部分を入力。
①ログイン名・・・ログインIDを決めて入力します。半角英数にしましょう。
②SQLServer認証を選び、パスワードを決める。下のポリシーを適用するのチェックを外せば緩めのPW設定にできます。
③パスワードポリシーや期限を決める。ここは各状況や環境に応じて選択してください。

最後にOKボタンを押します。

権限を変更(ほぼ管理者権限)にする場合

データベース作れない、表示されないので、とりあえずセキュリティとか気にせずに権限を広げたい場合は、以下のような設定にするのが手っ取り早いです。


セキュリティ>ログイン>ユーザ名をダブルクリック
サーバーロールを選択し、 sysadmin にチェックをする。

エラー18456がでてしまう場合


いくらやってもエラー18456がでてしまう!
それはSQLserver元がWindows認証しかログインできない設定になっているからです。
設定変更しましょう。(SQLServer2019はこれがデフォルトになっているっぽい)

①サーバ側でSMMSを起動し、windows認証で入る


②上図のようにツリーの一番上を右クリック⇒プロパティ⇒セキュリティと進む
Windows認証モードが選ばれている場合は「SQL Server認証モードとwindows認証モード」の方を選びOKボタン


③最後に左上のコンピュータ名を右クリック⇒「再起動」も行う必要があります

以上

SQLServer2017構成マネージャーでTCPポートの設定

図の通りにスタートメニューからSQLServer2017構成マネージャーを起動します。


SQL Serverネットワーク構成>SQLEXPRESSのプロトコルをクリック
右側のTCP/IPをダブルクリック。⇒「プロトコル」タブの有効を「はい」を選ぶ。
(まだ続きます)


次に「IPアドレス」タブを選び、下の方にスクロールして
「IPAII」TCPポート・・・空欄⇒1433
TCP動的ポート・・・0 ⇒ 空欄
に修正し、OKボタンを押す。

SQL Server Browserも有効にした方が良い場合も(名前付きインスタンスじゃなければ不要)

データベースが名前付きインスタンスの場合など
SQL Server Browserも有効にしておくと良い場面もあるようです(うちでは設定しなくても問題はありませんでしたが)


SQL Serverのサービス>「サービス」タブ>開始モードを無効⇒自動に変更する。⇒OKボタン

SQL Server Browserの上で右クリック⇒「開始」

Windowsファイアウォールの設定


スタート ⇒ Windows管理ツール ⇒ 「セキュリティが強化されたWindowsファイアウォール」を選んで起動する



左側の「受信の規則」⇒右側の「新しい規則」の順にクリックすると「新規の受信の規則ウィザード」が表示します。


2番目の「ポート」を選択し「次へ」



TCP を 選び、特定のローカルポートに「1433」と入力し、「次へ」



そのまま「次へ」



そのまま「次へ」



名前は任意の名称を入力。(自分で作った設定であると見分けが付くような名称の方を推奨します。)最後に完了をクリック


受信の規則に追加されました。必ず有効化になっているか確認しましょう。

余談

ファイアウォールの設定が何かの拍子で無効化になると、エラー40、53がでます。
エラーが出て接続できない場合は改めて、追加した設定が緑のチェックが有効になっているか確認しましょう。
私もインストールや設定してるうちの、あるタイミングで突然接続エラーがでて接続できないようになりました。半日以上あれやこれや苦戦し、やっと設定が無効に気づいた・・・。

さいごに

うちの環境では以上の3つの設定で、普段使用しているPCから、「さくらのVPS」のSQLServerへの接続は成功しました。同じくODBCもこの設定をしておけば接続できるようになっています。

これからVPSなどでWindowsServer × SQL Server 使い始める方の何かの参考になればと思います。今回は以上です。

コメント