Determining failed connections on SQL Server 2000

  • How can I determine how many and the details of failed connection to a SQL Server 2000 DB?

    Thanks.

  • 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,

  • 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

  • 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

  • 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.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply