Re: Authentication options with SS2005

Thanks for the info.

If I follow this trigger correctly, it keeps a login from having multiple
simultaneous sessions, right?

What I was looking for is a way to watch how many times a sql server login
(using sql authentication, not windows) was attempted with a bad password,
and after a set number of consecutive failed login attempts….lock out the
user.


"Uri Dimant" wrote:

Jason
SP2 should be installed on the system
This trigger prevents from 'AuditLogin' login having more that one
connections.
Modufy this script for your needs

create login AuditLogin with password = ‘AuditLoginPswd’
go
/*Create a very simple login trigger */
create trigger AuditLogin_Demo
/* server means instance level*/
on all server
with execute as self
/* We specify the logon event at this stage
– If there are more than one connections,
– Issue a rollback*/
for logon
as begin
IF ORIGINAL_LOGIN()= ‘AuditLogin’ AND
(SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND
original_login_name = ‘AuditLogin’) > 1
ROLLBACK;

end
go
"Jason" <Jason@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:77F199C5-DD90-42B1-9619-23A25FCDE01A@xxxxxxxxxxxxxxxx
thanks

we have an issue were we need an instance of ss to lockout and account
after
more logins than is set by our domain policy.

as far as the hacker scenario, that could happen now using windows
account.

"Charles Wang[MSFT]" wrote:

Hi Jason,
SQL Server does not provide such function. You may give Microsoft
feedback
via https://connect.microsoft.com/sql so that your suggestion will be
heard
by the product team and you may get their responses via email.

Personally I do not think that this is a secure way since your valid
logins
will face the risk of locking if some hackers just want to make your
logins
locked. If it is required for you, you may consider implementing this
function in your application level by yourself. I think that your meaning
of locking logins here is that you temporarily DENY all the permissions
to
the login.

If you have any other questions or concerns, please feel free to let me
know. Have a good day!

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no
rights.
======================================================





.

Relevant Pages

  • Re: Creating a Trigger on Access
    ... I don't think a 'SELECT' trigger would be a good idea since data could be ... access for reasons other than login. ... a stored procedure would be the ... DECLARE @ReturnCode int ...
    (microsoft.public.sqlserver.security)
  • Re: are triggers atomic?
    ... If I write a trigger for a file, ... fact that it will conclude before another login on say port ... Maybe I should have been using a readu lock in my ...
    (comp.databases.pick)
  • Trigger**
    ... I've a following trigger which is fired in update status and make all ... destination table) ... ,but when I change the emp_status (by another login which has not access to ... table,an error occurred that you have no permission to update the second ...
    (microsoft.public.sqlserver.programming)
  • Re: login auditing
    ... trigger on that table. ... 2003 you can use account policies and limit users to login on certain hosts ... "Nishu" wrote in message ...
    (microsoft.public.sqlserver.security)
  • Re: SQL7 Column-level constraints/filters possible?
    ... based on the login. ... While the SQL server can determine the User logged ... on, a trigger can only be applied to Inserts, Updates and Deletes; ... when retrieving data (e.g., SELECT blah, blah FROM MyTable WHERE ...
    (microsoft.public.sqlserver.security)