SQL Serverで外部接続(management studio使用しない方法)
他のサーバから、外部接続をしてselect文の結果を取得したかったのですが
少しだけハマったので備忘録メモ。
参考:SQL Server Express にリモート接続
https://creativeweb.jp/fc/remote/
前提としては、別のPCから接続するためには以下の作業が必要なんですが
sa アカウントを使いたくない理由があったため、ユーザを新規作成する設定にしました。
・SQL Server 構成マネージャを使ってプロトコルの設定
・使用するDBインスタンスのTCP/IPの有効化
・固定ポートか動的ポートか決めた上で設定
→SQL-Server を再起動すると設定が有効
・リモート接続の許可
・「 Windows 認証モード」から「SQL Server 認証モードと Windows 認証モード」に変更
・データベースユーザのアカウントを有効
→ sa アカウントでなくても良い
上記の流れ今回は、そもそも外部接続には、
SQL Server 認証モードじゃないとできないということ。
ログインユーザとデータベースユーザ
それぞれを紐付ける必要があること。
作成したデータベースユーザにデータベースのロールを
付与する必要があるということを知りました。
で、ながなが書きましたが設定手順は以下の通り。
①SQL Server 構成マネージャを使用してTCP/IPの有効化と
固定ポートを使用する場合は、その設定をしてSQL-Server を再起動
②SQL Server 認証モードを有効にするため、レジストリエディタを開きデータベースの
MSSQLServerの以下を設定。
キー値:LoginMode
型:REG_DWORD
値:2
リンク先の場所とは私の環境は違ってました。
大事なのは設定するDBのインスタンスのパスの中にある
LoginModeの値を1から2にかえる必要があります。
③コマンドプロンプトを起動して以下を実行。
sqlcmd -S <サーバー名>\<インスタンス名> EXEC sp_configure 'remote access', 1 ; GO RECONFIGURE ; GO CREATE LOGIN <新規ユーザー名> WITH PASSWORD = '<パスワード>'; GO ALTER LOGIN <新規ユーザー名> ENABLE ; GO USE <データベース名>; GO CREATE USER <上でつくったユーザ> FOR LOGIN <上でつくったユーザ>; GO EXEC sp_addrolemember 'db_datareader', '<上でつくったユーザ>' GO
④設定したユーザでSQL Server 認証モードでログインできるか確認
sqlcmd -S <サーバー名>\<インスタンス名> -U <ユーザー名> -P <パスワード> -d <データベース名>
で、ログインに成功したら、select文とgoを打ち結果が返ってこれば設定は完了。
これで外のサーバからアクセスできなければ、ネットワークやFWが原因かな。
以上です。