none
SQL Server 2008 R2 Maintenance Plan

    Question

  • I have a SQL Server 2008R2 server configured that hosts multiple databases.  The SQL Server (MSSQLSERVER) service uses a local system account.

    I attempted to setup a maintenance plan that including a full backup to a NAS but I get an error that says:  "The database engine service cold not resolve the specified folder location....."  So my thought was that the SQL Server service needs permissions to the target location.  My instinct tells me that I have to setup a domain account for the SQL service to use and grant that permission to the target (backup) location;  however, am I going to break anything by changing the SQL service account?  Is this the right course of action or is there a way to grant access to the target share for the local account?

    Wednesday, January 30, 2013 8:05 PM

Answers

  • Either use a domain account, or create  local account with mathing name/password (dirty solution). If you change the service account, make sure you use SQL Server Configuration Manager tool to change it.

    Tibor Karaszi, SQL Server MVP | web | blog

    • Marked as answer by Grandman Thursday, January 31, 2013 4:17 PM
    Wednesday, January 30, 2013 8:08 PM
  • Hi Grandman,

    If the service must interact with network services, access domain resources like file shares or if it uses linked server connections to other computers running SQL Server, you might use a minimally-privileged domain account. Many server-to-server activities can be performed only with a domain user account. This account should be pre-created by domain administration in your environment.

    To change the service accounts, password, service startup type, or other properties of any SQL Server–related service, use SQL Server Configuration Manager.

    And grant the user the access permission to target folder.

    Setting Up Windows Service Accounts (SQL Server 2008 R2): http://msdn.microsoft.com/en-us/library/ms143504(v=SQL.105).aspx.

    If you have any feedback on our support, please click here.

    Thanks.


    Maggie Luo
    TechNet Community Support


    Thursday, January 31, 2013 5:28 AM
  • You mean what tool you use to change the service account? You don't use Management Studio for this. You use the SQL Server Configuration Manager tool.

    Tibor Karaszi, SQL Server MVP | web | blog

    • Marked as answer by Grandman Thursday, January 31, 2013 4:17 PM
    Thursday, January 31, 2013 3:37 PM
  •  Can helps you-

    http://beyondrelational.com/modules/2/blogs/77/posts/11360/changing-sql-server-service-account-or-password-avoid-restarting-sql-server.aspx
    http://msdn.microsoft.com/en-US/library/ms345578.aspx


    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    • Marked as answer by Grandman Thursday, January 31, 2013 4:16 PM
    Thursday, January 31, 2013 3:42 PM

All replies

  • Either use a domain account, or create  local account with mathing name/password (dirty solution). If you change the service account, make sure you use SQL Server Configuration Manager tool to change it.

    Tibor Karaszi, SQL Server MVP | web | blog

    • Marked as answer by Grandman Thursday, January 31, 2013 4:17 PM
    Wednesday, January 30, 2013 8:08 PM
  • Hi Grandman,

    If the service must interact with network services, access domain resources like file shares or if it uses linked server connections to other computers running SQL Server, you might use a minimally-privileged domain account. Many server-to-server activities can be performed only with a domain user account. This account should be pre-created by domain administration in your environment.

    To change the service accounts, password, service startup type, or other properties of any SQL Server–related service, use SQL Server Configuration Manager.

    And grant the user the access permission to target folder.

    Setting Up Windows Service Accounts (SQL Server 2008 R2): http://msdn.microsoft.com/en-us/library/ms143504(v=SQL.105).aspx.

    If you have any feedback on our support, please click here.

    Thanks.


    Maggie Luo
    TechNet Community Support


    Thursday, January 31, 2013 5:28 AM
  • Thanks - this makes sense.  So let's say I create a domain account.  I'm a little unsure where exactly in the MS SQL server management studio I need to reflect this change.  Suggestions?

    Joe Grandjean

    Thursday, January 31, 2013 2:42 PM
  • You mean what tool you use to change the service account? You don't use Management Studio for this. You use the SQL Server Configuration Manager tool.

    Tibor Karaszi, SQL Server MVP | web | blog

    • Marked as answer by Grandman Thursday, January 31, 2013 4:17 PM
    Thursday, January 31, 2013 3:37 PM
  •  Can helps you-

    http://beyondrelational.com/modules/2/blogs/77/posts/11360/changing-sql-server-service-account-or-password-avoid-restarting-sql-server.aspx
    http://msdn.microsoft.com/en-US/library/ms345578.aspx


    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    • Marked as answer by Grandman Thursday, January 31, 2013 4:16 PM
    Thursday, January 31, 2013 3:42 PM
  • Thanks - this is exactly what I was looking for.  It changed sucesfully and my applications that are accessing the databases are working perfectly.

    Joe Grandjean

    Thursday, January 31, 2013 4:17 PM