June 26, 2008 at 6:20 am
How can I determine how many and the details of failed connection to a SQL Server 2000 DB?
Thanks.
June 26, 2008 at 6:49 am
You can get connected information from sysprocesses as follows:
select SPID,
DB_NAME(dbid) as dbname,
dbid,
login_time,
last_batch,
status,
hostname,
program_name,
cmd,
net_library,
loginame from master.dbo.sysprocesses
Order by login_time desc
Or sp_who2
For Failed attempts, you have to Query to ErrorLog. Get the ErrorLog into a Table and then Query on the table to find out Failed Login attempts. This will only Work if you have TURNED ON LOGGING AUDIT in SQL Server Settings for atleast Failed logins.
To Read Error Log either use xp_readerrorlog or check this link: http://www.novicksoftware.com/coding-in-sql/Vol3/cis-v3-N1-sql-errorlog.htm
Another Option will be to run a server trace to look for Failed Logins against the Database,
Maninder
www.dbanation.com
June 26, 2008 at 7:53 am
Like Mani said, you need to audit connections to SQL Server then check the error_log.
http://www.microsoft.com/sql/prodinfo/previousversions/securingsqlserver.mspx
June 26, 2008 at 8:07 am
In the short term you can also use the sql profiler to capture failed logon attempts (remember to add in the hostname field).
Also you can use the server side tracing to record this information
(see sp_trace_create and related sprocs in bol) more long term.
Steven
June 26, 2008 at 8:08 am
Check ErrorLog, if you have the LOGIN Auditing feature TURNED ON in the SQl server Properties and set to atleast Failed Logins.
Create #table and dum xp_readerrorlog into the Table and format the select statements for Failed logins.
Maninder
www.dbanation.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply