A SQL Server gotcha for today – following the installation of a SQL Server 2005 active/passive cluster I ran the Microsoft Baseline Security Analyser (sorry Analyzer) to check that nothing was missing or incorrectly configured. Halfway through the report I noticed the following warning regarding the SQL Server Agent Service:
SQL Server Service [CLIENTSQL02SQLSERVERAGENT] In Unrecommended Account On Host [CLIENTSQL02].
We recommend that the service [SQLSERVERAGENT] on host [CLIENT02] be run under Network Service Account. Currently it is designated to run under the account [DOMAINSqlSvrSvc].
This warning threw me as the installation wizard requires you to use a domain user as the account for the SQL Server Agent Service and explicitly does not allow you to select the Network Service Account.
So whats the deal here Microsoft? After a little Googling, it would appear that there is one of two explainations:
- The MBSA does not report correctly for a clustered environment – the SQL Server Books Online page ‘Service Account Types Supported for SQL Server Agent‘ reports that Network Service account (NT AUTHORITYNetworkService) is supported on a non-clustered server, but it is not supported on a clustered server. There is lots more gumpf on the page and I would recommend that you read it all before coming to a conclusion if you are experiencing the same problem; Or
- It appears the client had evicted one of the nodes to perform some motherboard firmware upgrade (damn you fancy new HP Blades!!) before running the MBSA and it is possible that it thought it was looking at a standalone rather than clustered environment.
I think the point to take home from here is not to take the MBSA report as gospel – before you go ahead and implement a change on live, test it first on your UAT or reference environment and check that it does produce the desired effect.
Update: Also just found this in the SQL Server Books Online page Selecting an Account for the SQL Server Agent Service:
Because multiple services can use the Network Service account, it is difficult to control which services have access to network resources, including SQL Server databases. We do not recommend using the Network Service account for the SQL Server Agent service.
I suppose that answers it then – don’t use the Network Service account for the SQL Server Agent service.