観察と傍観のはて

アプリ、プログラム、本、映画、メンタル的なこと、日記などを書いています。

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が原因かな。

以上です。