locked
SQL trace set to halt on audit failure, now SQL server won't start RRS feed

  • Question

  • Running SQL Server 2012 Standard.  I'm new to SQL, so bear with me.  To comply with a security guideline, I created an event trace to log events, and was required to configure it so that the database would shut down if it failed to audit.  Of course, now that I've rebooted the server the SQL Server (MSSQLSERVER) service won't start, and I'm pretty sure it's because the event trace can't audit.  The error I get when I try to start the service manually is rather generic:

    Windows could not start the SQL Server (MSSQLSERVER) service on <servername>.  Error 1067: The process terminated unexpectedly.

    1. Is there a way to debug the SQL Server service to determine why it won't start?

    2. Presuming my suspicion is correct, that it won't start due to a failure to audit, is there a way to stop the stored procedure that I created (which I set to start automatically) from starting, or reconfigure it so that it won't kill the SQL Server on audit failure?  If there is a way to modify that stored procedure without going through Management Studio, I know the line that needs to be edited.

    Wednesday, August 6, 2014 9:37 PM

Answers

  • All - Thanks for the assistance with this issue.  As I mentioned above, I have restored the server from a  backup that was taken before the changes were made and my SQL Server is back in operation. 

    I wasn't sure which section of the SQL Server forum would be appropriate to ask this question, so I posted it under SQL Server Security since the problem occurred as a result of attempting to secure the server.  I'm going to post new threads to get help with properly configuring the Event Trace and Logon Triggers, and will post those to the SQL Server Security section as well unless anyone has suggestions for a more appropriate section.

    • Marked as answer by tracycai Monday, August 11, 2014 7:49 AM
    Thursday, August 7, 2014 8:21 PM

All replies

  • 1. Is there a way to debug the SQL Server service to determine why it won't start?

    Check the SQL Server error log for related messages.  The error log text file is located in the Log subfolder of the instance directory (e.g. "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\errorlog").

    2. Presuming my suspicion is correct, that it won't start due to a failure to audit, is there a way to stop the stored procedure that I created (which I set to start automatically) from starting, or reconfigure it so that it won't kill the SQL Server on audit failure?  If there is a way to modify that stored procedure without going through Management Studio, I know the line that needs to be edited.

    You can alter the trace so that it does not automatically start with the steps below.  There is more than one method to do these tasks but you might find this the easiest.

    1) Start SQL Server in single-user mode with minimal configuration.  This can be done with the following command from the command-prompt:

    NET START MSSQLSERVER /m /f
    

    2) Run the query below from an SSMS query widow or the SQLCMD command-prompt utility, specifying the desired trace name.  Note that SSMS may show an erroneous connection failed error due to InteliSense background thread.

    ALTER EVENT SESSION your_audit_trace_name ON SERVER WITH (STARTUP_STATE = OFF);

    3) Stop SQL Server with the following command from the command prompt

    NET STOP MSSQLSERVER

    4) Start SQL Server normally from the command prompt

    NET START MSSQLSERVER


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Thursday, August 7, 2014 12:00 PM
  • Check the SQL Server error log for related messages.  The error log text file is located in the Log subfolder of the instance directory (e.g. "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\errorlog").

    You can alter the trace so that it does not automatically start with the steps below.  There is more than one method to do these tasks but you might find this the easiest.

    1) Start SQL Server in single-user mode with minimal configuration.  This can be done with the following command from the command-prompt:

    NET START MSSQLSERVER /m /f


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    

    It doesn't appear to be getting to the point where it can log anything when the service tries to start.  The latest log ERRORLOG entry was just prior to rebooting the server.  Coincidentally, one of the hardening steps I took was to restrict permissions on the Log directory.  This morning I relaxed the permissions, granting Everyone - Full Control, to see if that was the cause but it still won't start.

    Using net start with the /m /f switches won't start the service either. I still get the "System Error 1067 - process terminated unexpectedly" error.

    I should also mention one other hardening step I took that may also be the cause.  I created a logon trigger to limit the number of logins for a particular service account using the following script:

    CREATE TRIGGER Conn_Limit
    ON ALL SERVER WITH EXECUTE AS 'xyzuser'
    FOR LOGON
    AS
    BEGIN
    IF     ORIGINAL_LOGIN() = 'xyzuser'
           AND
           (      SELECT COUNT(*)
                  FROM   sys.dm_exec_sessions
                  WHERE  Is_User_Process = 1 AND
    Original_Login_Name = ORIGINAL_LOGIN()
           ) > 1
        ROLLBACK
    END 

    xyzuser is a SQL login that 1) is the owner of the XYZ database that I created and 2) is used by the XYZ application (running on another server) to connect to the database.  As you can see, the trigger is configured to limit xyzuser to only one connection to the database, which was required to prevent the account from being used for malicious purposes.


    • Edited by nobody_u_know Thursday, August 7, 2014 5:53 PM Changed <> to = in logon trigger code
    Thursday, August 7, 2014 3:49 PM
  • Terminated unexpectedly isn't very helpful. You can sometimes get better errors by attempting to start SQL Server from the Windows services application services.exe.

    If that doesn't get you a better error, try starting SQL Server from the command line, see http://msdn.microsoft.com/en-us/library/ms162819.aspx

    I don't expect these methods to start SQL Server, but I'm hoping for better error messages.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Thursday, August 7, 2014 4:18 PM
  • I agree that the "terminated unexpectedly" error isn't helpful.  Unfortunately, trying to start it from Services returns the same error.  As Dan suggested, I tried starting it from the command line using NET START MSSQLSERVER /m /f with the same result, and I also tried NET START MSSQLSERVER /t 1, hoping that it would only start the default trace and not the trace I created which is trace ID 2.
    Thursday, August 7, 2014 5:40 PM
  • It sounds like you did more than that.  Did you change the rights on the MS SQL Server directory?

    I would suggest you undo everything you did and then research what exactly you are trying to accomplish.

    By default, only the SQL Server service and local administrators have access to the Program Files\MS SQL Server directory. 

    Thursday, August 7, 2014 6:01 PM
  • Rather than starting it from NET START, Services.msc, start it from the command line. Get the current parameters from SQL Server Configuration Manager and add /f /m. Hopefully it produces an error message before it dies.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, August 7, 2014 6:47 PM
  • It sounds like you did more than that.  Did you change the rights on the MS SQL Server directory?

    I would suggest you undo everything you did and then research what exactly you are trying to accomplish.

    By default, only the SQL Server service and local administrators have access to the Program Files\MS SQL Server directory. 

    I didn't change the rights on the MSSQL directory - only the Log directory. I questioned myself, wondering if there were any other changes I made.  But I double checked the guideline and didn't find any other changes that I made.  Nevertheless, I did exactly as you suggested and undid everything by restoring from backup.
    Thursday, August 7, 2014 8:14 PM
  • All - Thanks for the assistance with this issue.  As I mentioned above, I have restored the server from a  backup that was taken before the changes were made and my SQL Server is back in operation. 

    I wasn't sure which section of the SQL Server forum would be appropriate to ask this question, so I posted it under SQL Server Security since the problem occurred as a result of attempting to secure the server.  I'm going to post new threads to get help with properly configuring the Event Trace and Logon Triggers, and will post those to the SQL Server Security section as well unless anyone has suggestions for a more appropriate section.

    • Marked as answer by tracycai Monday, August 11, 2014 7:49 AM
    Thursday, August 7, 2014 8:21 PM