Results 1 to 6 of 6

Thread: logon audit

  1. #1
    Join Date
    Aug 2010

    Unanswered: logon audit

    Hi All,

    Looking for help with the following problem.


    I have to log user login attempts(also the failed ones so so trigger ON ALL SERVER FOR LOGON can't be used)
    First, application log to db on user account (audit_user)dedicated for audit purpose
    audit_user has right only to log and execute one procedure to check login data

    part of the code related to password check

    select @pass = password_hash from sys.sql_logins where upper(name) like upper(@login)
    if PWDCOMPARE(@password, @pass) = 0
    insert into login_history (system_users_id, result_code, date_login, ip_lh) values (null, 3, GETDATE(), @ip)
    set @ret = 3

    result of select statement shows only two users: logged and SA and null values
    with execute as self/owner.. also don't work correctly

    How can I correctly complete the task?
    Thanks in advance


  2. #2
    Join Date
    Jan 2003
    Provided Answers: 17
    I do not get what you are trying to do with the PWDCOMPARE function. But a large part of what you are trying to capture would be covered by Profiler. Specifically the Login Successful and Login Failure events. Make the trace as you like in Profiler, then generate a script. Create a SQL Agent job with that script, and schedule it to start when SQL Server starts.

  3. #3
    Join Date
    Aug 2010

    Thanks for reply.

    PWDCOMPARE check if password provided by user matches the stored one in db.
    Sorry, I haven't mentioned that I'm using 2008 express R2 version. There are software limitations(lack of profiler/job agent). I've checked open source profiler (AnjLab Sql Profiler), but I have to store audit data in db and present it application layer of my system

    Any suggestions?
    Thanks in advance.


  4. #4
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    I'd suggest upgrading to the Developer Edition of SQL Server 2008 R2. If you pay full retail, it will cost you roughly $50 but save you so many hours that I can't think of another choice that makes any sense. Having every tool that SQL Server supports available to you for that price makes the question a non-starter to me!

    Once you upgrade to the Developer Edition, you'll have the SQL Profiler, all of SSIS, SSAS, SSRS, etc. For the cost of one (relatively cheap) night on the town you can save enough late night hours to have a dozen nights on the town!!!

    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Aug 2010
    hah ahaa.. sweeeeeet
    already tried to convince management, but in MSP sector I feel like Don Kichot :]

  6. #6
    Join Date
    Aug 2010
    Great it. Thanks for all those information. I have learned a lot.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts