April 3, 2009 at 12:22 am
Hi All,
I want to achieve below objectives in SQL Server 2000 ( Not in SQL Server 2005)
After 5 invalid logins SQL Server / Windows Login will automatically updated by Deny Access.
Password complexity and password expiration policy.
Any help friends.
Regards,
Raj
April 3, 2009 at 3:44 am
I believe you can implement by Implementing Windows Services.
? After 5 invalid logins SQL Server / Windows Login will automatically updated by Deny Access.
Windows Login: I believe you can implement this in Windows itself.
SQL Server Login:
1. In SQL Server ERRORLOG file Enable log on and success failure audit.
2. Write a Windows Service to read the ERRORLOG Log file and Find the continuous login failure for particular account then disable that SQL Server Login.
? Password complexity and password expiration policy.
May be you can implement password expiration using programming but
It may not be possible for Password complexity
Rajesh Kasturi
April 3, 2009 at 4:36 am
You can also do this by reading / parsing the error log within SQL Server and keeping track of the number of failed attempts, and after you're threshold is crossed, disable the windows and sql login.
Of course for this you will need to log failed events as well.
April 3, 2009 at 11:58 am
There are certain loopholes in your requirement - There is no direct way to deny login for pure sql logins, yes its ok for windows login.
Anyways, I have some interesting things for you.
Below is a sample code which will read from error log all login failure and then take appropriate action. For sql logins i have just put a password reset, you can change it to something more relevant to your environment.
Use this code directly or create a SP based on the code.
-- create a table to store error log
create table #log
(msg varchar(255), cr int)
-- populate error log tabele
insert into #log
exec master..xp_readerrorlog
-- delete unwanted msgs
delete from #log
where msg not like '% Login failed for user %'
-- truncate last 2 chars from table
update #log
set msg=left(msg,len(msg)-2)
--now we want just the login name to be in table
update #log
set msg=right(msg,charindex(char(39),reverse(msg))-1)
-- now extract only logins which failed for more than 5 times
select msg
into #failed
from #log a
JOIN master..syslogins b on a.msg = b.name
where b.denylogin <> 1
group by msg
having count(msg) > 5
DECLARE @login_name sysname
DECLARE failed_login CURSOR FOR
SELECT msg
FROM #failed
OPEN failed_login
FETCH NEXT FROM failed_login
INTO @login_name
WHILE @@FETCH_STATUS = 0
BEGIN
-- for windows login it will deny
IF EXISTS(select * from master..syslogins where name = @login_name and isntname=1)
BEGIN
exec('exec sp_denylogin '''+@login_name+'''')
print 'Login '+@login_name+' has been denied.'
END
-- for sql login, it will reset password
IF EXISTS(select * from master..syslogins where name = @login_name and isntname=0)
BEGIN
exec('exec sp_password NULL,''xxx123xxx'', '''+@login_name+'''')
print 'Password for login '+@login_name+' has been reset'
END
FETCH NEXT FROM failed_login INTO @login_name
END
CLOSE failed_login
DEALLOCATE failed_login
-- cleanup
drop table #log
drop table #failed
But there are drawbacks of above code.
1. For sql logins it will again and again password, as much times it will be executed. As we are not storing already processed logins. For windows logins its ok, as we can filter dined logins
2. It will work only with current error log
Now the question is how to get login failure event - one way is to setup alert as mentioned HERE , but when i tested it was not working for me, maybe i missed something. Another way is to run a job periodically.
Please note that this code i have tested just for few times, so cant guarantee, there might be something overlooked and code can be further tuned.
2.
April 3, 2009 at 12:15 pm
But there are drawbacks of above code.
1. For sql logins it will again and again password, as much times it will be executed. As we are not storing already processed logins. For windows logins its ok, as we can filter dined logins
You can maintain a list of sql logins in a permenant table with a flag and the reason the password was reset and other information date etc. This will allow you to keep an audit trail on the execution of this process. Moreover, this will allow you to exclude service / functional accounts from locking out (even though you want to track break-in attempts).
2. It will work only with current error log
Now the question is how to get login failure event - one way is to setup alert as mentioned HERE , but when i tested it was not working for me, maybe i missed something. Another way is to run a job periodically.
Please note that this code i have tested just for few times, so cant guarantee, there might be something overlooked and code can be further tuned.
2.
You can maintain the log date from the error logs in a table and then read the log post that date / time and update as necessary. All of this could be setup as part of a job.
April 3, 2009 at 12:18 pm
i agree, thanks.
April 15, 2009 at 12:04 am
Thanks a lot Boolean_z,Rajesh kasturi,SA.
I just need little update how to read error log after a date or time specified.
sp_readerrorlog ?
April 15, 2009 at 6:54 am
You will have to take the output from readerrorlog in to a temporary table and then perform the date time comparison.
April 20, 2009 at 9:30 am
Hi,
I have a problem, my server is not recycled in a frequent basis and we have audit enabled so the Error Log Size is too big.
We also don’t follow any DBCC ERRORLOG mechanism, so every time taking the error log to temp table and scanning for events will not work.
The mechanism should be so solid that if somebody tries to do brute force then it should be locked in an instant.
Please Advice.
Regards,
Raj
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply