SQL Server Account Types and Security

John

A quick reference to SQL Server Account Types and System Security

 

Ports

Clients connect to SQL Server through UDP 1434

SQL server responds with a TCP Port number to continue their connection

Default TCP port is 1433

 

 

Server and System Security

SQL Service runs under a service account, can be:

  • Windows account
  • Builtin account
    • Passwords managed by SCM (Service Control Manager)
    • Account Types
      • Local System Account (may have full permissions to local machine)
      • Network Service Account
      • Local Service Account
  • User account
    • Local account
    • Domain account
      • Domain account is important when using failovers, as the recovery servers would run under the same domain account as

 

SQL Service Accounts need to be able to do OS level tasks:

  • Access data and log files
  • Write / read backup files
  • No general permissions needed, can be specific

If the SQL Service service require access to network resources (like backup on LAN) then it should be using a domain account (or Network Service account but then it will be accessing the network  as the server name, not the service name).

Example of the account used in SQL Service Configuration Manager:

TBD

 

Some more information from: https://msdn.microsoft.com/en-us/library/ms143504.aspx#Network_Service

 

 

Description of Service Accounts

The service account is the account used to start a Windows service, such as the SQL Server Database Engine.

Accounts Available With Any Operating System

In addition to the new MSA and virtual accounts described earlier, the following accounts can be used.

 

Domain User Account

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.
System_CAPS_ICON_note.jpg

Note:  If you configure the application to use a domain account, you can isolate the privileges for the application, but must manually manage passwords or create a custom solution for managing these passwords. Many server applications use this strategy to enhance security, but this strategy requires additional administration and complexity. In these deployments, service administrators
spend a considerable amount of time on maintenance tasks such as managing service passwords and service principal names (SPNs), which are required for Kerberos authentication. In addition, these maintenance tasks can disrupt service.

 

Local User Accounts

If the computer is not part of a domain, a local user account without Windows administrator permissions is recommended.

Local Service Account

The Local Service account is a built-in account that has the same level of access to resources and objects as members of the Users group. This limited access helps safeguard the system if individual services or processes are  compromised. Services that run as the Local Service account access network resources as a null session without credentials. Be aware that the Local Service account is not supported for the SQL Server or SQL Server Agent services. Local Service is not supported as the account running those services because it is a shared service and any other services running under local service would have system administrator access to SQL Server. The actual name of the account is NT AUTHORITY\LOCAL SERVICE.

Network Service Account

The Network Service account is a built-in account that has more access to resources and objects than members of the Users group. Services that run as the Network Service account access network resources by using the credentials of the computer account in the format <domain_name>\<computer_name>$. The actual name of the account is NT AUTHORITY\NETWORK SERVICE.

Local System Account

Local System is a very high-privileged built-in account. It has extensive privileges on the local system and acts as the computer on the network. The actual name of the account is NT AUTHORITY\SYSTEM.

 

Identifying Instance-Aware and Instance-Unaware Services

Instance-aware services are associated with a specific instance of SQL Server, and have their own registry hives. You can install multiple copies of instance-aware services by running SQL Server Setup for each component or service. Instance-unaware services are shared among all installed SQL Server instances. They are not associated with a specific instance, are installed only once, and cannot be installed side-by-side. Instance-aware
services in SQL Server include the following:

  • SQL Server
  • SQL Server Agent
    • Be aware that the SQL Server Agent service is disabled on instances of SQL Server Express and SQL Server Express with Advanced Services.
  • Analysis Services*
  • Reporting Services
  • Full-text search

Instance-unaware services in SQL Server include the following:

  • Integration Services
  • SQL Server Browser
  • SQL Writer

*Analysis Services in SharePoint integrated mode runs as ‘Power Pivot’ as a single, named instance. The instance name is fixed. You cannot specify a different name. You can install only one instance of Analysis Services running as ‘Power Pivot’ on each physical server.

 

Account Types

User Accounts

  • Local Account
    • Administrator (default in Win7)
    • Guest (default in Win7)
    • Personal user
    • Local accounts are specific to the local computer and are not in Access Control List (ACL). Thus they get no access over the domain or across the network.
  • Domain Account
    • Krbtgt (default Kerberos service account)
    • Domain user
    • Domain users are part of a Windows Active Directory domain and can have the following:
      • Domain group membership
      • Reside in an ACL
      • Access resources in its same domain
      • Receive centralized Group Policy settings for security, profile, desktop, etc information.

 

System and Service Accounts

  • Local System Account
    • NT AUTHORITY\SYSTEM
    • Most highest privilege account can do anything on the system
    • SID S-1-5-18
    • Dangerous, should not use
  • Network System Account
    • NT AUTHORITY\NetworkService
    • Has more access to resources and objects
    • SID S-1-5-20
    • Access to network but uses the computer account credentials
  • Local Service Account
    • NT AUTHORITY \LocalService
    • Account has no password
    • HKCU represents the LocalService user account
    • Has minimal privileges on local computer
    • Presented as anonymous on the network
    • SID: S-1-5-19
    • Registry profile is under HKEY_USERS\S-1-5-19

 

http://stackoverflow.com/questions/510170/the-difference-between-the-local-system-account-and-the-network-service-acco