Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Server based Authentication MSSQL Expand / Collapse
Author
Message
Posted Sunday, October 19, 2008 12:12 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, May 19, 2013 9:07 PM
Points: 5, Visits: 25
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
Post #588297
Posted Sunday, October 19, 2008 1:19 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 12:40 AM
Points: 7,001, Visits: 8,439
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

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


- 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 but most of the time this is me
Post #588304
Posted Sunday, October 19, 2008 3:42 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, May 19, 2013 9:07 PM
Points: 5, Visits: 25
Do we need to create the table for every DB. Please advise
Post #588322
Posted Sunday, October 19, 2008 10:26 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Thursday, July 10, 2014 1:34 PM
Points: 6,623, Visits: 1,855
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #588363
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse