SQL Server Service Account All you Need to Know

Introduction

A sql service account is an explicitly created user account designed to establish a security framework for services operating on Windows Server operating systems. The security context dictates the service's ability to access local and network resources.Windows operating systems depend on services to operate various functions. You can configure these services through applications, the Services snap-in, Task Manager, or Windows PowerShell.Want to excel in this subject? Opt for SQL Server Online Training & Certification Course. It will make you understand the real-time working of SQL language and how it is applied in different industries and roles, including Data Analysis and reporting environments.

What is SQL Server Service Account

The SQL Server Service runs under the account of the computer. The SQL Server Service has access to network resources, but under the context of the computer account, not under its account. The SQL Server Service runs under the service-specific account called NT Service\MSSQLSERVER. Many entry-level Database Administrators (DBAs) often grapple with the concept of service accounts and which one is optimal for specific situations. 

This blog clarifies this topic for you. Depending on your choices during the SQL Server installation, various services will be installed on your server.

• 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 your SQL Server related services either during the setup or after the installation using the SQL Server Configuration Manager.

SQL Server Service Account

Following are the types of Service accounts available in SQL Server

• 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 built-in Windows account is available for configuring services in Windows. This account has permissions the same as accounts in the user group; thus, it has limited access to the resources on the server. This account is not supported for SQL SERVER and AGENT services.
• Local System Account: This built-in Windows account 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 built-in Windows account is available for configuring services in Windows. This has permission to access resources in the network under the computer account.
• Domain Account: This account is a part of your domain with access to network resources for which it is intended to have permission. 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 Windows in-built accounts are part of the operating system and assigned to the users; you cannot use the account to log in to the system. These accounts are meant to be used for securing and authentication mechanisms.

How to Add Service Account to Sql Server?

If you have a combination of SQL Server 2008 or above and Windows Server 2008 or above, then SQL Server will be installed using an operating system feature called Service Isolation. In terms of user accounts, all user accounts have a unique identifier we call a SID (Security Identifier). With Windows Server 2008, services also have SIDs. This was implemented for situations where multiple services are running under the same account (for instance, Network Service or System). Still, these services shouldn't have access to resources belonging to other services that share the same service account. As a result, each service now has a SID. Any sql developer knows how to add service account to sql sever.

SQL Server 2008 and later versions offer a means to ensure that the service itself, rather than just the account, possesses the necessary permissions for database resources. For example, in the illustration below, you'll observe that logins are attributed to NT Service\MSSQL$SQL2008R2 (representing a SQL Server 2008R2 named instance named SQL2008R2) and its corresponding SQL Agent service, NT Service\SQLAgent$SQL2008R2. This association arises from service isolation.In this scenario, the sole path to obtaining sysadmin-level access is by accessing SQL Server through these services. Even if the service account were to be compromised, unless one of these services established a connection, the compromised account would lack any privileges within SQL Server.

Use of SQL Server Service Account:

SQL Server service accounts allow SQL Server to run with the rights and privileges assigned to the service account. 

Changing Service Accounts:

As mentioned above, SQL Server service accounts can be configured 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 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 to restart the SQL Server and related services for the new Service account to take effect.

SQL Training For Administrators & Developers

• No cost for a Demo Class
• Industry Expert as your Trainer
• Available as per your schedule
• Customer Support Available

Enroll for Demo Class

Conclusion

The blog details the different aspects of SQL Server Service accounts. It describes SQL Server Service Accounts, sql server service account best practices, different types of SQL Server Service accounts, and how to create sql service accounts. It also discusses its use and how to switch between different service accounts. This will be a good read for those who aspire to become a DBA.But suppose you are still unsure of what is a service account in sql server or how to create service account for sql server. In that case, you can always opt for a self-learning sql online certification or SQL Server DBA Training & Certification. Learn from scratch, from creating a service account to using different SQL accounts. 

If you are a beginner, watch this video to learn What is Microsoft(MS) SQL Server.