Server based Authentication MSSQL

  • joythis

    SSC Journeyman

    Points: 95

    Hi,

    Is there a way in MSSQL to authenticate a particular user and server to access a DB.

    Like "ABC" user from "10.x.x.x" IP allowed to access "ABCDB"

    Please guide me is there any other way to achieve this kind of restriction.

    Thanks

    Joythi S

  • ALZDBA

    SSC Guru

    Points: 134254

    If you want a user only to be used from a certain address, you should first create your UserId_to_IPAddress table and then use a logon trigger (you need at least SP2 !)

    Be very careful with this kind of restrictions !

    could be something like:

    CREATE TRIGGER S_tr_DBA_ConnectionTracker

    ON ALL SERVER

    FOR LOGON

    execute as owner -- check bol for these options !

    AS

    BEGIN

    set nocount on

    If NOT exists (

    Select 1

    from master.dbo.yourtable T

    inner join sys.dm_exec_sessions ES

    on ES.[session_id] = @@spid

    -- and ES.[host_name] = T.[host_name]

    -- and ES.[program_name] = T.[program_name]

    -- and ES.[nt_domain] = T.[nt_domain]

    -- and ES.[nt_user_name] = T.[nt_user_name]

    and ES.[login_name] = T.[login_name]

    -- and ES.[original_login_name] = T.[original_login_name]

    inner join sys.dm_exec_connections EC

    on EC.[session_id] = @@spid

    and EC.[client_net_address] = T.[client_net_address] )

    begin

    Raiserror('SYSADMIN MESSAGE: Connection Not allowed (s) ',1,1,suser_sname()) with log;

    rollback tran

    end

    END;

    Johan


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt ?

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] :alien: but most of the time this is me :hehe:

  • joythis

    SSC Journeyman

    Points: 95

    Do we need to create the table for every DB. Please advise

  • K. Brian Kelley

    SSC Guru

    Points: 114445

    No. And keep in mind that this logon trigger is only going to fire when they first connect. Once they are in SQL Server, you can't prevent them from changing to a database they have access to.

    K. Brian Kelley
    @kbriankelley

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply