October 3, 2011 at 10:36 am
Greetings.
I am trying to find if there is a way to allow only 1 sql login to the Instance and then deny other authentication requests to the same instance.
I will give a brief description to see if it clarifies what I am trying to accomplish.
We have a application that checks to see if users are valid users to the system. This is handle by an internal table in the DB. once they do exist in this table, the user is authenticated and given the correct roles to the different DB's.
The issue we are having is since the roles are controlled via the login, a user would login to my machine (for example) and then go to their machine and login again. Hence someone sitting and my desk and at the other desk can both perform the same tasks since the system has authenticated them on both machines.
I would want for the initial user to be disconnected if they login somewhere else so that I the user limited to 1 machine.
I have considered using Mix Mode authentication and making the Windows account the authenticating account in our environment, we are allowed to login multiple locations with our Windows account.
Can my scenario be achieve by a SQL control??
Thanks for your assistance.
Regards
Mo'
October 3, 2011 at 12:37 pm
books online 's first logon trigger example is limiting a login from more than three connections:
http://msdn.microsoft.com/en-us/library/bb326598.aspx
make sure you NEVER CLOSE the window you use to create your login trigger during testing...otherwise you may lock tourself out.
if your trigger is badly written, you need the ability to drop, alther or disable that trigger...and the original window is what you need to maintian just in case.
if you don't, i can point you to quite a few posts where folks have locked themselves out, and had to start the serve rin single user mode jsut to drop the trigger.
Lowell
October 13, 2011 at 5:09 pm
Thanks very much for your assistance.
I slightly altered the code to let it count distinct machines instead of sessions. Our application authenticates at different levels due to special activities that they need to perform. and once the person is logged into one machine it is not like they will get any benefit by logging in again as the same user..
I placed the code below but I have another question which I will ask after the code:
alter TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS 'TESTER'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN() = 'TESTER' -- in (@c_username)
AND
(SELECT COUNT(distinct host_process_id) FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND
original_login_name = 'TESTER') > 1
ROLLBACK;
END;
The above code works perfectly, but it only works perfectly for 1 user. I want it to apply to all users except 'sa', 'DBS', and about 4 more accounts.
I tried using a cursor with in the trigger to pull usernames that I wanted to compare..
tried:
as the cursors select statement
SELECT distinct original_login_name FROM sys.dm_exec_sessions
WHERE is_user_process = 1
AND original_login_name not in ('sa','DBS')
order by original_login_name
--the value returned was stored in @c_username and that was used in the check query.
and that made the login prompt start bringing an error.
I also tried using a basic select from a table which I populated with 2 names, TEST and TESTER.
That still produced the error. Is there any way you could suggest I apply this to about 200 names apply to all except 10??
Thanks again for your assistance.
Mo'
October 13, 2011 at 5:34 pm
this one is tougher for me to test...at home i don't have another machine to connect to to test.
i think you might have issues if you don't grant permissions to PUBLIC so the login can read sys.dm_exec_sessions;
the default is non sysadmins can only see their own sessions, i think.
as i stated before, this requires testing...if you lock yourself or your entire company out of your server, and it costs a million dollars an hour in payroll while you have to reboot the server in single user mode to drop the trigger, it's not my fault...
something like this SEEMS to be closer to what you want...note there is no cursor.
CREATE TRIGGER connection_limit_trigger
ON ALL SERVER
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN() NOT IN ('sa', 'DBS','4 more accounts')
-- in (@c_username)
AND
(SELECT COUNT(DISTINCT host_process_id) FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND
original_login_name = ORIGINAL_LOGIN()) > 1
ROLLBACK;
END;
GO
ENABLE TRIGGER connection_limit_trigger ON ALL SERVER
DISABLE TRIGGER connection_limit_trigger ON ALL SERVER
DROP TRIGGER connection_limit_trigger ON ALL SERVER
Lowell
October 14, 2011 at 12:32 am
Thanks dude...
I did not realize that ORIGINAL_LOGIN() was the user calling for authentication...
I understand all the code.. with the exception for the last 3 lines..
ENABLE TRIGGER connection_limit_trigger ON ALL SERVER
DISABLE TRIGGER connection_limit_trigger ON ALL SERVER
DROP TRIGGER connection_limit_trigger ON ALL SERVER
why are you enabling , then disabling, and then dropping the trigger???
what would have happened if I dont include those 3 commands??
I also noticed that you took off the "WITH EXECUTE AS 'TESTER'". I wanted to take that off during my testing but wasnt sure what it would cause?? does that portion dictate that the trigger only runs when it is the TESTER account logging in??
Respectfully,
Mo'
October 14, 2011 at 4:38 am
Mo' (10/14/2011)
Thanks dude...I did not realize that ORIGINAL_LOGIN() was the user calling for authentication...
I understand all the code.. with the exception for the last 3 lines..
ENABLE TRIGGER connection_limit_trigger ON ALL SERVER
DISABLE TRIGGER connection_limit_trigger ON ALL SERVER
DROP TRIGGER connection_limit_trigger ON ALL SERVER
why are you enabling , then disabling, and then dropping the trigger???
what would have happened if I dont include those 3 commands??
I also noticed that you took off the "WITH EXECUTE AS 'TESTER'". I wanted to take that off during my testing but wasnt sure what it would cause?? does that portion dictate that the trigger only runs when it is the TESTER account logging in??
Respectfully,
Mo'
after you create the trigger, it doesn't do anything until you explicitly enable it.
the other two lines are just there for reference....as you test it by connecting from another machine and other windows, you wan tto be able to disable or drop it...those two lines should be commended out, but easily accessible, that's all.
WITH EXECUTE AS TESTER would execute the trigger codeunder the security context of that login...works well if THAT user has permissions to say, the sys views, but normal users don't. i didn't think that's the case for your testing, so i removed it.
one I get to work, i'll be able to properly test the model trigger from other machines, as other users, but you'llwant to do the same on some developer or private instance, and don't test on production or anything like that .
here's a decent auditing snippet i use to review values in triggers and procs that might help a bit:
--the auditing snippet below works fine in a
--login trigger,
--database trigger
--or any stored procedure.
SELECT
getdate() AS EventDate,
DB_NAME() AS DBName,
HOST_NAME() AS HostName,
APP_NAME() AS ApplicationName,
OBJECT_NAME(@@PROCID) AS ProcedureName,
USER_ID() AS Userid,
USER_NAME() AS UserName,
SUSER_ID() AS sUserid,
SUSER_SNAME() AS sUserName,
IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin],
IS_MEMBER('db_owner') AS [Is_DB_owner],
IS_MEMBER('db_ddladmin') AS [Is_DDL_Admin],
IS_MEMBER('db_datareader') AS [Is_DB_Datareader],
ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],
client_net_address AS ipaddress,
auth_scheme AS AuthenticationType
FROM sys.dm_exec_connections where session_id = @@spid
Lowell
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply