Re: Authentication options with SS2005



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: Too many sa failed logins
    ... > The event log is showing a ton of failed sa logins. ... I don't have a real answer to your question, but exposing a database server ... The best option for monitoring attempted connections would be at the ... OS or network level - if a client doesn't authenticate with MSSQL, ...
    (comp.databases.ms-sqlserver)
  • Re: Trigger on Login
    ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) ... it seems that all logins to MS-SQL are recorded ... > If the trigger was lightweight enough it shouldn't hurt ... > password history). ...
    (microsoft.public.sqlserver.dts)
  • Re: ftp login retry lockout time
    ... | logins from a specific ip and then lock out that ip for a specified time ... Log the connections, write a script which you'll run periodically to ...
    (comp.unix.sco.misc)
  • Re: Monitoring Connections
    ... CGI app, then I do see another row appear in the sp_who2 results. ... 'connections' field incrementing. ... >> sp_monitor show the 'number of logins or attempted logins'. ...
    (microsoft.public.sqlserver.connect)
  • Trigger on Login
    ... it seems that all logins to MS-SQL are recorded ... If the trigger was lightweight enough it shouldn't hurt ... >a sql server message to the application that the password ... password history). ...
    (microsoft.public.sqlserver.dts)