Sql-server – How to provision the SQL Server Agent service after SQL Server Edition upgrade

sql serversql-server-2008sql-server-2008-r2sql-server-agentupgrade

I have upgraded SQL Server 2008 R2 Express to SQL Server Standard Edition. After I have upgraded it, the SQL Server Agent was stopped.

I have tried to start but it fails.

Only when I have changed his built in account from "Network Service" to "Local System" it started.

Looking at MSDN How to: Upgrade to a Different Edition of SQL Server 2008 R2 Procedure 13 it says:

Provision the SQL Server Agent service account by using SQL Server Configuration Manager

  1. What does it mean provision it?
  2. What built in account should the SQL Server Agent run with?

Thank you

Best Answer

Let me answer your question point wise

1.SQL Server express has SQL server agent code built in but disabled. When you go to sp_configure you can see Agent XP enable/disable option this is so because if any point of time you are planning to upgrade SQL Server Express Microsoft upgrade process does not have to do much change but just a minimum to enable SQL server agent functionality. So before upgrading to Standard it wants you to use proper service account as a start up for SQL server agent so that after upgrade when database engine tries to bring agent services up it can do it eaisly. By default NT Authority/System account has admin privileges so it was able to bring Agent services up. Also please make sure that you always change SQL Server/Agent service account using SQL Server configuration manager it preserves the ACL's

2.As a good security practice SQL server should not run with Built in account instead a domain account should be created and granted least privileges to run it. Below Microsoft web resource will tell you what rights are required. The article is bit lengthy but beautifully written

http://msdn.microsoft.com/en-gb/library/ms143504.aspx

Related Question