SQL Server Service accounts

Most entry level DBAs still have the confusion on what is a service account and which is the best to be used and when. This article will take you through that.

Depending on the selection that you made while installing SQL Server, you will have the services installed in your server.

SQL Server Database Services:

SQL Server Agent

Analysis Services

Reporting Services

Integration Services

SQL Server Browser

Full-text search

SQL Server Active Directory Helper

SQL Writer

You can configure you SQL Server related services either during the setup or after the installation using the SQL Server configuration Manager.

Types of startup accounts:

Local User Account: This user account is created in your server where SQL Server is installed, this account does not have access to network resources.

Local Service Account: This is a builtin windows account that is available for configuring services in windows. This account has permissions as same as accounts that are in the users group, thus it has limited access to the resources in the server. This account is not supported for SQL SERVER and AGENT services.

Local System Account:
This is a builtin windows account that is available for configuring services in windows. This is a highly privileged account that has access to all resources in the server with administrator rights.

Network Service Account: This is a builtin windows account that is available for configuring services in windows. This has permissions to access resources in the network under the computer account.

Domain Account: This account is a part of your domain that has access to network resources for which it is intended to have permission for. It is always advised to run SQL Server and related services under a domain account with minimum privilege need to run SQL Server and its related services.

Where can you see the Local System, Local Service and Network Service accounts? These are windows in-built accounts that are part of the operating system and assigned to the users, you cannot use the account to login to the system, these accounts are meant to be used for securing and authentication mechanism.

Changing Service Accounts:

As mentioned above, SQL Server service accounts can be configured either during installation or using SQL Server configuration Manager. The first one is part of the installation and can be configured during the step Instance Configuration. I would walk you through changing a service account using SQL Server Configuration Manager.

1. Start -> Programs Microsoft SQL Server 2008 -> Configuration Tools -> SQL Configuration Manager

2. Highlight a service in the right pane, right click for properties.

You can change the built-in account here, else if you would like to change it to a Local User account or a domain user account, choose option This Account to Ungray it and enter the credentials of a local or a domain user account.

Remember that you will need to restart the SQL Server and related services for the new Service account to take effect.

Tip: If you have no domain accounts or running under multiple domains and Your SQL Server needs access to other resources in the network, you can create an user account with same username and password across all Servers and use that as service account in your servers.


Posted

in

by

Comments

13 responses to “SQL Server Service accounts”

  1. ajay avatar

    very good topic.Thank you so much.

  2. Megha Chandak avatar
    Megha Chandak

    thanks for sharing this article ………………

  3. vic avatar
    vic

    Nice article!!!

    1. vic avatar
      vic

      I’m new DBA, I would like to know how to identify the servive accounts for all SQL server in my company by scripts or SSMS?

      Thank you.

      1. Sugesh avatar
        Sugesh

        You will need to identify the list of SQL Server first and then run a query to read service accounts. You can find similar in scripts section wiht topic “Server Details”

      2. VidhyaSagar avatar
        VidhyaSagar

        Vic — Can you check the article by sugesh, he will pull the necessary information from registry.. It will give you an idea how to get the details
        http://sql-articles.com/scripts/server-property/

  4. Naveed A avatar

    Nice article. I have a question. How I can check that my user account has previlieages to use Full Text Services on hosting server. My hosting server is SQL 2005
    If I can, then plz let me know the STEPS to follow for Enabling FTS on my database.
    Thanks

    1. VidhyaSagar avatar
      VidhyaSagar

      HI Naveed,
      Could you please check the article wrote by deepak on fulltext? http://sql-articles.com/articles/dba/sql-server-2005-full-text-search/

  5. Mohamed Sheik avatar

    Very good article.

  6. Sandeep Awale avatar
    Sandeep Awale

    I have created a Windows Authentication SQL Login for service account. But unable to login via SSMS using this service account. Could you please guide on this?

    1. VidhyaSagar avatar

      Thanks for reading the article. This article is for old version of SQL Server but it should similar for the new versions as well.

      Is SQL Server service is running?
      What error are you getting when you try to connect with the account?

Leave a Reply to VidhyaSagar Cancel reply

Your email address will not be published. Required fields are marked *