locked
Logon Failure - Authz EventID 537 RRS feed

  • Question

  •  

    Hello Experts,

     

    I have a database setup with a mirror on a different database server. 

     

    When I setup the mirror I recieve logon failure audits as noted below every minute on both the principal and mirror database servers.

     

    The logon failures are associated with the account running the SQL Server service.  They are local accounts.

     

    The mirror functions properly but the logged events are annoying.

     

    I have narrowed this issue down to occuring only when I setup a mirror.

     

    How can I prevent these failure audits and continue to use the local user account that runs the SQL service?

     

    Event Type: Failure Audit
    Event Source: Security
    Event Category: Logon/Logoff
    Event ID: 537
    Date:  2/22/2008
    Time:  11:39:00 AM
    User:  NT AUTHORITY\SYSTEM
    Computer: <servername>
    Description:
    Logon Failure:
      Reason:  An error occurred during logon
      User Name: 
      Domain:  
      Logon Type: 3
      Logon Process: Authz  
      Authentication Package: Kerberos
      Workstation Name: ISIDEVAPP08
      Status code: 0xC000005E
      Substatus code: 0x0
      Caller User Name: SQLService
      Caller Domain: <servername>
      Caller Logon ID: (0x0,0x6361C9)
      Caller Process ID: 5084
      Transited Services: -
      Source Network Address: -
      Source Port: -


    For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

    Friday, February 22, 2008 4:52 PM

All replies

  • If you want to maintain it running as Local System, which is not a recommended configuration, you will need to use certificates in mirroring.  You can get more information on this here:

     

    http://technet.microsoft.com/en-us/library/ms366346.aspx

     

    For using certificates read here:

     

    http://technet.microsoft.com/en-us/library/ms191477.aspx

     

    It would be best in my opinion if you created a local user account on both servers that was identical with the same passwords, and configured this account to run the SQL Services.  Then the servers can communicate to each other securely with this account.  This is how my DMZ mirroring is configured where the servers are not on our domain.

    Saturday, February 23, 2008 3:54 AM
  • Thanks Jonathan.  I actually have my SQL Servers configured as your opinion outlines.  The services are set to use local user accounts.  The mirroring of the databases works without issue. 

     

    The problem is the annoying logon failure audits that only occur once I setup the mirror.  I have rebuilt the servers three times and have proven that the only time I get the EventID 537 Logon Failure is once I setup the mirror.  The events occur every minute.  Since the accounts are local to the server (the servers are on a domain), I would not expect Authz being used.

     

    So my question remains, how can I prevent these failure audits from occurring.

     

     

    Saturday, February 23, 2008 6:06 PM
  • Perhaps you can explain how exactly are the server configured.

    Do you have a witness?
    Do you use Windows authentication or certificate authentication for mirroring endpoints?
      If Windows auth, what accounts are involved (principal, mirror, witness)?
      If certificate, what logins own the certificates involved (including exchanged certificates)?

    Have you validated that all the SQL Server services involved (principal, mirror, witness) have the proper rights to connect to the domain Active Directory?
    Monday, February 25, 2008 5:57 PM
  • Here is how I have my endpoints configured: I have WebDB1 and WebDB2 respectively for this example.

     

    On WebDB1:
    I have a local account called sqlserviceaccount with password abc123

     

    Code Snippet

    CREATE ENDPOINT [Mirroring]

    AUTHORIZATION [webdb1\sqlserviceaccount]

    STATE=STARTED

    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)

    FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE

    , ENCRYPTION = REQUIRED ALGORITHM RC4)

     

     

    On WebDB2:

    I have a local account called sqlserviceaccount also with password abc123.  User is webdb2\sqlserviceaccount.

     

    Code Snippet

    CREATE ENDPOINT [Mirroring]

    AUTHORIZATION [webdb2\sqlserviceaccount]

    STATE=STARTED

    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)

    FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE

    , ENCRYPTION = REQUIRED ALGORITHM RC4)

     

     
    These Service Accounts have the same exact permissions on both servers, and I keep my folder structures identical as well.  This allows the integrated Authentication to work between the servers.  Once I had created the Endpoints, I set my database to full recovery.
     

    Code Snippet

    ALTER DATABASE Web_Ecom_DB SET RECOVERY FULL;

     
    Then I did a full backup/restore from WebDB1 to WebDB2.
     
    Then on the Mirror I execute the following followed by the same script on the Primary.
     

    Code Snippet

    ALTER DATABASE Web_Ecom_DB SET PARTNER = 'TCP://WebDB1:5022';

     

     

    Just for the record, this is a sample and not my actual servernames, database names, usernames or passwords.  You want to be a bit more complex/creative for your real environment than this, but this is identical to how I have my non-domain mirror working.

    Monday, February 25, 2008 6:16 PM
  • Thanks for the review.

     

    This is exactly how I have mine setup.  The one difference is that both machines are on the same domain but I am using local accounts to run the services. 

     

    Both machines, I am not using a witness, were recently rebuilt with a fresh OS install and MS SQL Server install.  I created three identical accounts on each server to run the SQL Server, Agent and Broswer services (I am not running the Browser services). 

     

    When installing MS SQL Server, I set each service to use the accounts I created and let the install assign the necessary rights etc. 

     

    During the setup of the mirror, using the GUI, there is a step that asks you to specify the service accounts of the server instances.  Here is a quote from the GUI..  "If the server instances use different accounts in the same or trusted domain as their service accounts for SQL Server, enter the accounts below.  Leave the textboxes empty if all instances use the same account, the accounts are non-domain accounts, or the accounts are in untrusted domains." 

     

    I left the textboxes empty because I felt they met the last condition, the accounts are identical (name and password), and they are non-domain accounts.

     

    It is immediately following the mirror setup the the failure audits start appearing every minute.  The mirror functions properly.  I have failed over from both servers without issue or data loss. 

     

    The failed logon attempts are always associated with the SQL Service account and it is for Authz Kerberos which I thought was only used in domain authentication, not local account authentication. 

     

    I am stumped and my log files continue to fill up with failed log on attempts.

    Monday, February 25, 2008 8:10 PM
  • Remus -

     

    I do not have a witness.

     

    I am using Windows authentication and left the textboxes blank during the mirror setup when asked to specify the service accounts of the server instances.  I did so because I felt the conditions were met for leaving them blank, noted in my other post.

     

    The servers can communicate with the Active Directory.  They are members of the domain and authenticate domain accounts properly.  The sql server instances on both servers run under local accounts that use the same username and password.

     

    Thanks. 

    Monday, February 25, 2008 8:14 PM
  • By servers communicating with the domain AD I mean the SQL Server instances, not the machines. If they are installed as local users they may not be able to communicate with AD. Accessing the AD is required in many scenarios, including authentication, authorization and EXECUTE AS scenarios.

    Using local accounts with matching passwords is hardly encouraged, and I'd rather say that is strongly discouraged. The recommended configuration is to use domain accounts. With other technologies the so called 'mirrored accounts' technique should only be deployed if there is no common Kerberos KDC (ie. a common trusted domain). However this last resort is not required, nor recommended, for mirroring, even when no common domain exists, because the far supperior technique of using certificates for mirroring endpoints is available as an alternative.

    In your situation you should use domain accounts for the two SQL Server instances, or use certificates authentication.

    Tuesday, February 26, 2008 12:36 AM
  • Remus,

     

    I agree with you that certificates are a better answer to this.  I didn't know about them back when I setup my mirroring, and I should fix it so that I think about certificates only when I answer posts.  I learned about them while working on exam 070-443 a few months back.  However, since it works perfectly as configured, right or wrong, I have to provide a strong case for changing it, to be able to change it.  I wish it were as easy as saying it isn't a best practice.  Any ideas?

     

     

    Tuesday, February 26, 2008 1:05 AM
  •  Jonathan Kehayias wrote:


    However, since it works perfectly as configured, right or wrong, I have to provide a strong case for changing it, to be able to change it.  I wish it were as easy as saying it isn't a best practice.  Any ideas?

     


    First they don't scale. Today there are only two servers involved, but in future your deployment may involve more and more servers. Replicating the user and password compromises the password as it becomes more promiscuous and orchestrating a password change across more and more servers becomes more and more difficult.

    Second the two accounts have different SID and there are constructs in SQL Server that rely on the SID, like the mapping between a database principal (user) and server principal (login). Database ownership (the mapping of dbo to a the login that created the database) is the major SID mapping problem and I had to answer on these forums again and again troubleshooting problems derived from this SID mismatch. Also auditing across enterprises often rely on SID for tracking (eg. generated profiler events).

    And last but not least local accounts in general (not only when deployed as mirrored accounts) break the EXECUTE AS functionality (because AD cannot be contacted) and this in turn breaks every functionality in the server that relies on EXECUTE AS (Service Broker, event notifications, db mail, queue activation etc)

    The problem is that even if it works today, a new application may be installed tomorrow and won't work and will probably waste many hours investigating it only to find out was a SID issue or an AD connectivity issue.
    Tuesday, February 26, 2008 7:20 AM
  • Thanks Remus.

     

    The SQL Server instances are able to communicate with the AD.  The databases successfully authenticate AD users for access.

     

    One reason that I have the SQL Server instances running under local accounts is because I read in MS SQL Server Best Practices that the account running the services should be the least priviledged accounts possible and to use local accounts if accessing domain resources and/or linked servers are not required.  Since these accounts do not have context outside their local systems I feel more protected from other resources being touched.

     

    I will research the certificates approach.  If this is the recommended method for setting up the mirror then I will likely transition to that.

     

    However, the fact remains my mirror setup is functioning properly with respect to synchronization and failover.  The failure audits are still unexplained and may be eliminated by going to certificates, we'll see.

     

    Perhaps we agree to move on if the failure audits cannot be explained.

     

     

    Tuesday, February 26, 2008 2:14 PM
  • Try to change your endpoint definition from AUTHENTICATION = WINDOWS NEGOTIATE to AUTHENTICATION = WINDOWS NTLM
    Tuesday, February 26, 2008 4:37 PM
  • Remus,

     

    Thanks for the added information.  I began looking into changing this to certificates today, already, with our server team.  They are building a mirrored set of these servers in a separate VLAN on VMWare to validate the change process works as anticipated.  Once this is complete, I will be making the change over.

    Tuesday, February 26, 2008 9:30 PM
  •  

    Remus

     

    Unfortunately this had no impact.  I ran the following SQL on both servers and still receive the error message noted below.  I even rebooted both servers.

     


    ALTER ENDPOINT MirrorTest
    FOR DATABASE_MIRRORING
    (AUTHENTICATION = Windows NTLM)

     

     

    Event Type: Failure Audit
    Event Source: Security
    Event Category: Logon/Logoff
    Event ID: 537
    Date:  2/26/2008
    Time:  4:26:00 PM
    User:  NT AUTHORITY\SYSTEM
    Computer: <servername>
    Description:
    Logon Failure:
      Reason:  An error occurred during logon
      User Name: 
      Domain:  
      Logon Type: 3
      Logon Process: Authz  
      Authentication Package: Kerberos
      Workstation Name: <servername>
      Status code: 0xC000005E
      Substatus code: 0x0
      Caller User Name: <SQLServiceUsername>
      Caller Domain: <servername>
      Caller Logon ID: (0x0,0x13336)
      Caller Process ID: 1568
      Transited Services: -
      Source Network Address: -
      Source Port: -


    For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

     

    Tuesday, February 26, 2008 9:30 PM
  • I don't know what causes this and I don't have an environment to try to repro it myself. One option you have is to follow up with the advice on Troubleshooting Kerberos Errors to at least positively identify the actual cause of the error (what authz operation is being performed? why is NT AUTHORITY\SYSTEM involved?). Ultimately I recommend you contact MS support if you can't find the cause.
    Thursday, February 28, 2008 7:41 AM
  •  

    Thank you Remus for you time and attention to this issue.

     

    I don't know how much more time I will put to this.  I will likely reconfigure the mirror to use certificates and hopefully not experience this error message.

     

    If I find the cause I will post the information that I collect.

     

     

    Thursday, February 28, 2008 12:23 PM