none
Windows認証について RRS feed

  • 質問

  • お世話になります。V5です。

    SQL Server Management Studio Express(SSMSE) から Windows認証で接続できないようにする方法で確認です。

    ・SSMSEの左側のオブジェクトエクスプローラのセキュリティのログインの名前をsa以外は、すべて削除します。
    ・SSMSEを再起動し、Windows認証で、接続してもエラーでできない。

    この方法で行っていますが、何か問題はないでしょうか?
    (この方法で問題ないでしょうか)

    よろしく、お願いいたします。

    2009年4月22日 9:28

回答

  • ちなみに、未検証で申し訳ないのですが、以下の設定で同様の挙動は実現できるかもしれません。
    ・ sa は無効に設定。
    ・ sysadmin ロールの管理用アカウントを作成。
    ・ 上記アカウントのログオントリガで接続元がローカル以外は拒否。
       (sys.dm_exec_sessions.host_name が null またはサーバ自体のコンピューター名以外はロールバック)
    素晴らしいヒントをありがとうございます。とりあえず以下のようにして、うまく動いているようです。

    CREATE TRIGGER sa_limit_trigger
    ON ALL SERVER WITH EXECUTE AS 'sa'
    FOR LOGON
    AS
    BEGIN
    
    declare @hostname          nvarchar(128)
    declare @request_hostname  nvarchar(128)
    
    select @hostname = substring(@@servername, 1, charindex('\', @@servername) - 1)
    select @request_hostname = host_name from sys.dm_exec_sessions where session_id = @@spid
    
    IF @hostname != @request_hostname
        ROLLBACK;
    
    END
    
    

    #本当はcharindexはインスタンス名が無いことも考慮しないといけません。charindexは検索する文字列が見つからないと0を返します。


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://blogs.wankuma.com/trapemiya/
    • 編集済み trapemiya 2009年4月24日 6:51 編集するとコード部分が崩れる・・・
    • 回答としてマーク sk7474 2009年5月14日 7:31
    2009年4月24日 5:39
  • SQL Server認証は推薦されていませんから、SQL Server認証だけにするモードはありません。したがって、書かれている方法しか無いと思います。
    ただ気になったのは、BUILTIN\Administratorsが残っていれば、Administrator権限のあるユーザーだとWindows認証できちゃいます。あと、sa以外すべて削除ということは、ログインはsaで行うのでしょうか? それはそれで危険だと思います。


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://blogs.wankuma.com/trapemiya/
    • 回答としてマーク sk7474 2009年5月14日 7:32
    2009年4月23日 5:40
  • 簡単に言えば、saは管理者用のアカウントですので何でもできてしまうからです。saは広く知らていますので、簡単なパスワードではアタックを受けた場合に突破される可能性があります。saはSQL Server認証で用いられますので、アタックに対してロックアウトすることができません。(NetValidatePasswordPolicy APIを使用すれば可能になるかも?)
    また、対策をしっかりしておかないと、SQLインジェクションで突破されることも考えられます。
    突破されればデータベースの削除など、何でもできてしまいます。従って通常は、アクセスできるテーブルなどを制限したユーザーでログインを行うのが普通です。セキュリティの考え方の基本として、必要最低限の権限を与えます。
    saはそもそも管理者用ですので、もしアプリケーションからの接続用に使用されるのでしたら、使用すべきではありません。

    #saはローカル接続のみ可っていうオプションがあればいいように思うのですが、たぶん無いんですよね?
    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://blogs.wankuma.com/trapemiya/
    • 回答としてマーク sk7474 2009年5月14日 7:32
    2009年4月23日 8:25
  • こんにちは、nagino です。
    茶々を入れたいわけではないのですが、技術的に気になる点がありましたので、横から失礼いたします。

    > #saはローカル接続のみ可っていうオプションがあればいいように思うのですが、たぶん無いんですよね?
    オプションとしては存在しなかったと思います。
    ちなみに、未検証で申し訳ないのですが、以下の設定で同様の挙動は実現できるかもしれません。
    ・ sa は無効に設定。
    ・ sysadmin ロールの管理用アカウントを作成。
    ・ 上記アカウントのログオントリガで接続元がローカル以外は拒否。
       (sys.dm_exec_sessions.host_name が null またはサーバ自体のコンピューター名以外はロールバック)

    また、SQL Server 認証のログインであっても、SQL Server 2005 以降でかつ Windows Server 2003 以降の上で動作している場合はパスワードポリシーが適用できますので、ロックアウトできます。
    http://msdn.microsoft.com/ja-jp/library/ms161959.aspx
    http://msdn.microsoft.com/ja-jp/library/ms189828.aspx
    ・・・が、不具合が存在するようです。
    http://support.microsoft.com/default.aspx/kb/818078
    (msdn では Windows 2003、サポート情報では Windows XP となっているので、要検証ですが・・・)

    ただし、SQL Server 2000 までの経緯などもありますし、アカウントの一括管理のメリットもありますので、trapemiya 様が既に記載されているとおり Windows 認証を利用されることをお勧めします。
    また、sa を使用すべきでないという点も変わりません。

    なお、Windows のローカル Administrators から SQL Server を本質的に保護することはできません。
    Windows 認証のログインを全て削除しても、例えば以下のように sysadmin 権限で接続する手段などがあります。
    http://msdn.microsoft.com/ja-jp/library/dd207004.aspx

    ご参考になれば幸いです。

    MCITP(Database Developer/Database Administrator)
    • 編集済み nagino - 引退エンジニア 2009年4月23日 23:36 回答の文言が不適切・不明瞭な箇所について、記述の追加と修正
    • 回答としてマーク sk7474 2009年5月14日 7:31
    2009年4月23日 23:33
  • 私も1点 気になった点がございます。
    SQL Server 2005 をインストールした際に、自動的に作成されるユーザー (SQLServer2005MSSQLUser$インスタンス名) を削除してしまうと、サポートされない構成となってしまいます。

    • 回答としてマーク sk7474 2009年5月14日 7:31
    2009年5月1日 3:53

すべての返信

  • SQL Server認証は推薦されていませんから、SQL Server認証だけにするモードはありません。したがって、書かれている方法しか無いと思います。
    ただ気になったのは、BUILTIN\Administratorsが残っていれば、Administrator権限のあるユーザーだとWindows認証できちゃいます。あと、sa以外すべて削除ということは、ログインはsaで行うのでしょうか? それはそれで危険だと思います。


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://blogs.wankuma.com/trapemiya/
    • 回答としてマーク sk7474 2009年5月14日 7:32
    2009年4月23日 5:40
  • はい、ログインはsaで行います。危険ということですが、具体的にどのようなことが危険なのか教えて下さい。
    よろしく、お願いいたします。
    2009年4月23日 6:53
  • 簡単に言えば、saは管理者用のアカウントですので何でもできてしまうからです。saは広く知らていますので、簡単なパスワードではアタックを受けた場合に突破される可能性があります。saはSQL Server認証で用いられますので、アタックに対してロックアウトすることができません。(NetValidatePasswordPolicy APIを使用すれば可能になるかも?)
    また、対策をしっかりしておかないと、SQLインジェクションで突破されることも考えられます。
    突破されればデータベースの削除など、何でもできてしまいます。従って通常は、アクセスできるテーブルなどを制限したユーザーでログインを行うのが普通です。セキュリティの考え方の基本として、必要最低限の権限を与えます。
    saはそもそも管理者用ですので、もしアプリケーションからの接続用に使用されるのでしたら、使用すべきではありません。

    #saはローカル接続のみ可っていうオプションがあればいいように思うのですが、たぶん無いんですよね?
    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://blogs.wankuma.com/trapemiya/
    • 回答としてマーク sk7474 2009年5月14日 7:32
    2009年4月23日 8:25
  • 了解しました。参考にさせていただきます。
    ありがとう、ございました。

    2009年4月23日 8:41
  • こんにちは、nagino です。
    茶々を入れたいわけではないのですが、技術的に気になる点がありましたので、横から失礼いたします。

    > #saはローカル接続のみ可っていうオプションがあればいいように思うのですが、たぶん無いんですよね?
    オプションとしては存在しなかったと思います。
    ちなみに、未検証で申し訳ないのですが、以下の設定で同様の挙動は実現できるかもしれません。
    ・ sa は無効に設定。
    ・ sysadmin ロールの管理用アカウントを作成。
    ・ 上記アカウントのログオントリガで接続元がローカル以外は拒否。
       (sys.dm_exec_sessions.host_name が null またはサーバ自体のコンピューター名以外はロールバック)

    また、SQL Server 認証のログインであっても、SQL Server 2005 以降でかつ Windows Server 2003 以降の上で動作している場合はパスワードポリシーが適用できますので、ロックアウトできます。
    http://msdn.microsoft.com/ja-jp/library/ms161959.aspx
    http://msdn.microsoft.com/ja-jp/library/ms189828.aspx
    ・・・が、不具合が存在するようです。
    http://support.microsoft.com/default.aspx/kb/818078
    (msdn では Windows 2003、サポート情報では Windows XP となっているので、要検証ですが・・・)

    ただし、SQL Server 2000 までの経緯などもありますし、アカウントの一括管理のメリットもありますので、trapemiya 様が既に記載されているとおり Windows 認証を利用されることをお勧めします。
    また、sa を使用すべきでないという点も変わりません。

    なお、Windows のローカル Administrators から SQL Server を本質的に保護することはできません。
    Windows 認証のログインを全て削除しても、例えば以下のように sysadmin 権限で接続する手段などがあります。
    http://msdn.microsoft.com/ja-jp/library/dd207004.aspx

    ご参考になれば幸いです。

    MCITP(Database Developer/Database Administrator)
    • 編集済み nagino - 引退エンジニア 2009年4月23日 23:36 回答の文言が不適切・不明瞭な箇所について、記述の追加と修正
    • 回答としてマーク sk7474 2009年5月14日 7:31
    2009年4月23日 23:33
  • ちなみに、未検証で申し訳ないのですが、以下の設定で同様の挙動は実現できるかもしれません。
    ・ sa は無効に設定。
    ・ sysadmin ロールの管理用アカウントを作成。
    ・ 上記アカウントのログオントリガで接続元がローカル以外は拒否。
       (sys.dm_exec_sessions.host_name が null またはサーバ自体のコンピューター名以外はロールバック)
    素晴らしいヒントをありがとうございます。とりあえず以下のようにして、うまく動いているようです。

    CREATE TRIGGER sa_limit_trigger
    ON ALL SERVER WITH EXECUTE AS 'sa'
    FOR LOGON
    AS
    BEGIN
    
    declare @hostname          nvarchar(128)
    declare @request_hostname  nvarchar(128)
    
    select @hostname = substring(@@servername, 1, charindex('\', @@servername) - 1)
    select @request_hostname = host_name from sys.dm_exec_sessions where session_id = @@spid
    
    IF @hostname != @request_hostname
        ROLLBACK;
    
    END
    
    

    #本当はcharindexはインスタンス名が無いことも考慮しないといけません。charindexは検索する文字列が見つからないと0を返します。


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://blogs.wankuma.com/trapemiya/
    • 編集済み trapemiya 2009年4月24日 6:51 編集するとコード部分が崩れる・・・
    • 回答としてマーク sk7474 2009年5月14日 7:31
    2009年4月24日 5:39
  • 私も1点 気になった点がございます。
    SQL Server 2005 をインストールした際に、自動的に作成されるユーザー (SQLServer2005MSSQLUser$インスタンス名) を削除してしまうと、サポートされない構成となってしまいます。

    • 回答としてマーク sk7474 2009年5月14日 7:31
    2009年5月1日 3:53
  • こんにちは。中川俊輔です。

    皆様、とても参考になる詳しい回答ありがとうございます。

    V5さん、フォーラムのご利用ありがとうございます。
    勝手ながら、有用な情報と思われる回答へ回答マークをつけさせていただきました。

    今後ともフォーラムをよろしくお願いします。
    それでは!
    マイクロソフト株式会社 フォーラム オペレータ 中川 俊輔
    2009年5月14日 7:37