Results 1 to 6 of 6

Thread: logon audit

  1. #1
    Join Date
    Aug 2010
    Posts
    3

    Unanswered: logon audit

    Hi All,

    Looking for help with the following problem.

    Scenario:

    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
    begin
    insert into login_history (system_users_id, result_code, date_login, ip_lh) values (null, 3, GETDATE(), @ip)
    set @ret = 3
    end
    else

    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

    Regrds,
    Bartek

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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
    Posts
    3
    Hi,

    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.

    Regards,
    Bartek

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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!!!

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

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

  6. #6
    Join Date
    Aug 2010
    Posts
    1
    Great forum...love 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
  •