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: Logon triggers
    ... it's not that if you mess up the login trigger that no one can ... You can still connect on the DAC (Dedicated Administrator ... Connection) to drop the trigger. ... Event Notifications since in the future I want to log DDL & SQL Config ...
    (microsoft.public.sqlserver.security)
  • 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: OT: PayPal fake website?
    ... Your account access has been limited. ... Due to the number of incorrect login attempts,your Paypal account has ... "If you did not trigger this limited,follow this link: To access the form, please click on the link below:" ... The first one leaves you hanging, then the second sentence leads to the link, it's STUPID. ...
    (sci.electronics.design)
  • 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)
  • Re: Disable or prevent Trusted Connection (-E) logins [DDL triggers]
    ... You could create a login trigger like this one: ... CREATE TRIGGER LimitLogins ... DECLARE @TrustedConnection INT ... Be sure and test a login trigger very carefully, since a mistake can lock you out of the server. ...
    (microsoft.public.sqlserver.server)