SQL Server Agent – Failure to start – NTFS Permission issues

Background

Playing around with Replication and as I completed configuring the publication, I opted to actually create the publication.  But, got an error message right away:

 

Error Message

Here are the individual error messages:

SQL Server Could Not Configure SQL Server Agent To Start Automatically

Textual:

Title :-
SQL Server could not configure SQL Server Agent to start automatically

Text:
SQL Server blocked access to procedure ‘dbo.sp_set_sqlagent_properties’ of componet ‘Agent XPs’ because this component is turned off as part of the security configuration for this server.

A system administrator can enable the use of ‘Agent XPs’ by using sp_configure.

(Microsoft SQL Server, Error 15281

Image:

CreatingPublication-Errors-CouldNotConfigureToStartAutomatically

 

SQL Server blocked access  to procedure ‘dbo.sp_set_sqlagent_properties’ of component ‘Agent XPs’

Textual:

Title :-
SQL Server could not configure SQL Server Agent to start automatically

Text:-
SQL Server blocked access to procedure ‘dbo.sp_set_sqlagent_properties’ of component ‘Agent XPs’  because the component is turned off as part of the security configuration for this server.

A system can enable the enable the use of ‘Agent XPs’ by using sp_configure.

 

Image:

CreatingPublication-Errors-SQLServerCouldNotStartTheSnapshpotAgent

 

Causation

The error is sourced to the Replication Wizard inability to submit jobs to SQL Server Agent.

Unfortunately, SQL Server Agent is not responding to requests as it is having problems starting.

 

Resolution – sp_configure?

Using SQL Server Management Studio, Review Settings

Via SQL Server Management Studio, review SQL Server Agent Settings:

SQLServerAgent-AgentXPsDisabled
We can clearly see the red mark by the Agent’s icon.

The red mark states that “Agent XPs disabled”.

 

Issued sp_configure

What is Agent XPs?

Here is what MSFT https://msdn.microsoft.com/en-US/Library/ms178127.aspx about Agent XPs

  • Use the Agent XPs option to enable the SQL Server Agent extended stored procedures on this server. When this option is not enabled, the SQL Server Agent node is not available in SQL Server Management Studio Object Explorer.
  • When you use the SQL Server Management Studio tool to start the SQL Server Agent service, these extended stored procedures are enabled automatically.
  • Management Studio Object Explorer does not display the contents of the SQL ServerAgent node unless these extended stored procedures are enabled regardless of the SQL Server Agent service state.

 

Via sp_configure, Enable “Agent XP”


exec sp_configure 'show advanced options', 1
go

reconfigure with override
go

exec sp_configure 'Agent XPs', 1
go

reconfigure with override
go

 

But, no help.

 

Resolution – SQL Server Management Studio

Inside SQL Server Management Studio, Start SQL Server Agent

Using SQL Server Management Studio, Start SQL Server Agent

 

StartedAndStopped

 

No help, as system indicates that agent was started and consequently stopped.

 

Diagnosis

MS Windows Event Log

Here is what the Event Log has to say:

Textual:

  • Event ID: 324
  • Text: Failed to initialize SQL Agent log (reason: Access is denied).

 

Image:

WindowsLogs

 

Folder & File Access Auditing – Using OS Tools

Configuration

Let us go figure out which folders and files we are unable to access.  Furthermore, which account is having problems accessing that object.

Here is the guide that we will follow:

How to audit user access of files, folders, and printers in Windows XP
https://support.microsoft.com/en-us/kb/310399

Local Security Policy – Object Level Access

  1. Click Start, click Control Panel, click Performance and Maintenance, and then click Administrative Tools.
  2. Double-click Local Security Policy.
  3. In the left pane, double-click Local Policies to expand it.
  4. In the left pane, click Audit Policy to display the individual policy settings in the right pane.
  5. Double-click Audit object access.
  6. To audit unsuccessful access to these objects, select the Failure check box.
  7. Click OK.

 

NTFS Auditing

  • Launched MS Windows Explorer
  • Transversed to C:\Program Files\Microsoft SQL Server\[Instance-name]\MSSQL  ( C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS_V2014\MSSQL )
  • Enabled Auditing on the Log Sub Folder
    • Right-click on the Log folder
    • From the drop-down menu, choose Properties
    • Click the Security tab, and then click Advanced.
    • Click the Auditing tab, and then click Add.
    • In the Enter the object name to select box, type the name of the user or group whose access you want to audit. You can browse the computer for names by clicking Advanced, and then clicking Find Now in the Select User or Group dialog box.
    • Click OK.
    • Select the Failed check box for the actions you want to audit, and then click OK.
    • Click OK, and then click OK.

 

Review

Let us review the captured logs.

As our error is security related, we will focus in on the Security Logs.

Filter Current Log

We will filter the “Security” Event Log, as detailed below:

  • Event logs: Security
  • Event ID: 4656
  • Keywords: Audit Failure

 

filterCurrentLog

 

 

Review Log Entries

Image:

WindowLogs-Security

 

 

Tabulate:

Item Value Sample
Security ID Account  Network Service
 Account Name  [computername$]  [computername$]
 Logon ID  0x3e4  0x3e4
 Object Server: Security  Security  Security
 Object Type  File  File
 Object Name  C:\Program Files\Microsoft SQL Server\[Instance-Name]\MSSQL\Log\SQLAGENT.OUT  C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS_V2014\MSSQL\Log\SQLAGENT.OUT
 Handle ID  0x0
 Log Name  Security
 Source  Microsoft Windows security  Microsoft Windows security
 Event ID  4565
 Task Category  File System  File System
 Level Information
 Keywords Audit Failure
 User
 Computer
 OpCode  Info

 

 

There are a few things that stick out:

  • Security ID
    • The Security ID is clearly stated as Network Service
  • Account Name
    • The Account name is the computer-name suffixed by the dollar sign ($)
  • Logon ID
    • It is likely that Microsoft assigned its built-in account (Network Service) the ID 0x3e4
    • But, I am unable to find official documentation
  • Object name
    • SQLAGENT.OUT
  •  Level
    • There are a few defined labels :- Critical, Error, Warning, Information, Verbose
    • Why are security failures qualified as Information?
  • Keywords
    • Thank goodness, we can use Keyword equal to “Audit Failure”
  • Event ID
    • 4656
      • 4656: A handle to an object was requested
        https://www.ultimatewindowssecurity.com/securitylog/encyclopedia/event.aspx?eventID=4656 

        • When you enable auditing on an object (e.g. file or folder), this is the first event recorded when an application attempts to access the object in such a way that matches the audit policy defined for that object in terms of who is requesting the access and what type of access is being requested. (it appears that two subcategories must be enabled, Handle Manipulation and one other such as File System or Registry depending on what type of object you are auditing.)
        • This event will be Audit Success or Audit Failure depending on whether the user account under which the account is running has the requested permissions or not.

Conclusion:

It seems we need to grant our SQL Server Agent Service Account (Network Service) permissions to SQL Server Log folder.

 

Folder & File Access Auditing – Using SysInternals

Let us gather collaborative data using SysInternals \ Process Monitor:

Filter

Add our SQL Server base folder:

Path-ErrLog-defined

 

 

Add Result = Access Denied:

Filter-Result-AccessDenied

 

Captured Event:

SQLAgent-CreateFile

 

 

Explanation:

  • We can see the first request is for SQLAgent.1
  • And, the next denied requests are for SQLAgent.Out

 

NTFS – Folder & File Access – Grant Permission

  1. Launch Windows Explorer
  2. Transverse to the base SQL Server Instance Folder;  in our case it is C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS_V2014\MSSQL
  3. Right-click the folder
  4. From the drop-down, select Properties
  5. The Properties dialog box appears.
    • Click the Security tab.
    • Under Group or user names, select or add a group or user; in our case “
    • At the bottom, choose to effect the available permissions.

 

Image:

permissionsForLog(Folder)-InBetween

 

Error Message:

On a running SQL Server Instance, you will likely see errors that are safe to ignore.

AnErrorOccurredWhileApplyingSecurityInformation

 

The errors we experienced touched to our inability to augment opened files such as errorlog, fd, fdlauncher.

ErrorLog is being accessed by the SQL Server Engine and fdlauncher is being accessed by the Full Text Engine.

Summary

Things seem to be working now.

I am not quite sure how much of my problems is due to the fact the SQL instance is running on my laptop.

It was originally installed as SQL Express.

But, because of some of the limitations of that Edition, I upgraded it to an Evaluation.

I think I missed checking to see if I had supplanted the fact that SQL Express does not support SQL Agent, but the Enterprise does.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s