from my MSDN blog - July 24, 2014
SQL Server與SQL Server Agent 服務帳戶(啟動帳戶)與權限
可設定的種類
1.Domain User Account (使用一個網域使用者帳戶)
2.Local User Account (使用一個本機使用者帳戶)
3.Local Service Account (NT AUTHORITY\LOCAL SERVICE) 本機服務帳戶
4.Network Service Account (NT AUTHORITY\NETWORK SERVICE) 本機網路帳戶
5.Local System Account (NT AUTHORITY\SYSTEM) 本機系統帳戶
6.Virtual Accounts (start from Windows 7 and Windows Server 2008 R2)
NT SERVICE\ <SERVICENAME>
| Service | Virtual Account Name |
|---|---|
| Default instance of the Database Engine service | NT SERVICE\MSSQLSERVER |
| SQL Server Agent service on the default instance of SQL Server | NT SERVICE\SQLSERVERAGENT |
| SQL Server Analysis service on the default instance of SQL Server | NT SERVICE\MSSQLServerOLAPService |
| SQL Server Reporting service on the default instance of SQL Server | NT SERVICE\ReportServer |
7.Managed Service Accounts (start from Windows 7 and Windows Server 2008 R2)
Configure Windows Service Accounts and Permissions
https://msdn.microsoft.com/en-us/library/ms143504.aspx
Q & A
1.Does service account need sysadmin role?
[SQL Server(Database Engine)]
沒有文件說明一定需要sysadmin role
no document found saying No or Yes explicitly about if sql service account need to be a member of sysdamin role
(1)SQL 2005,service account is always granted with sysadmin by default (e.g. if we pick Local System as our service account, then we would have NT Authority/System having sysadmin in the SQL instance).
How to change the SQL Server or SQL Server Agent service account without using SQL Enterprise Manager in SQL Server 2000 or SQL Server Configuration Manager in SQL Server 2005
https://support.microsoft.com/kb/283811
Base on KB283811 for SQL 2005, When changing new SQL service Account, sysadmin role is a must
(2)SQL 2008,in Windows 2008 R2, Virtual Accounts(SQL Server Per-service SID)=NT SERVICE\<SERVICENAME>is always granted with sysadmin by default
Configure Windows Service Accounts and Permissions
https://msdn.microsoft.com/en-us/library/ms143504.aspx
基於以上文件，建議將SQL Server service accoount(startup account)加入sysadmin role
[SQL Server Agent]
雖然SQL Server Agent service的啟動帳戶不是sysadmin role也能啟動SQL Server Agent，但可能會限制某些功能。
(1)必須是sysadmin role
From Books Online and Training kit 70-462
The SQL Server Agent service startup account must be a member of the SQL Server sysadmin fixed server role, and if multiserver job processing is used, the account must also be a member of the msdb database role TargetServersRole on the master server.
Set the Service Startup Account for SQL Server Agent (SQL Server Configuration Manager)
https://msdn.microsoft.com/en-us/library/ms186264.aspx
From Training kit 70-462
(2)The account you assign for the SQL Server Agent service during installation is added automatically to the sysadmin fixed server role during installation.
PS.但是經測試SQL 2008 R2安裝過程時所指定一個local or domain user account也不會自動加入sysadmin role
(3)If you modify the account used by the SQL Server Agent service at a later point, SQL Server Configuration Manager does not automatically add the account to sysadmin role.
PS.透過SQL Server Configuration Manager修改啟動帳戶，只會給予需要的權限，不會自動加sysadmin role
2.Does service account need Windows Administrator Permission?(local Administrators Group member)
[SQL Server(Database Engine)]
(1)不需要是Administrators group member
(2)視需求選擇其中一種account
Domain User Account
Local User Account
Local Service Account(NT AUTHORITY\LOCAL SERVICE)
Network Service Account(NT AUTHORITY\NETWORK SERVICE)
Local System Account(NT AUTHORITY\SYSTEM)
Windows 7 and Windows Server 2008 R2 have two new types of service accounts called managed service accounts (MSA) and virtual accounts.
Virtual Accounts
Managed Service Accounts
Configure Windows Service Accounts and Permissions
https://msdn.microsoft.com/en-us/library/ms143504.aspx
[SQL Server Agent]
(1)Beginning with SQL 2005, 不需要是Administrators Group member
Beginning with SQL Server 2005, SQL Server Agent no longer requires that the service startup account be a member of the Microsoft Administrators group.
However, there are some restrictions associated with using a non-administrative account for the SQL Server Agent service. For more information, see Service Account Types Supported for SQL Server Agent.
https://msdn.microsoft.com/en-us/library/ms345380(v=sql.105).aspx
(2)如果是domain account可能需要以下權限
From Training kit 70-462
When choosing to use a domain-based security account, ensure that the account has the following permissions:
■ The logon as a service right. You assign this right by using Group Policy.
■ Membership of the Pre-Windows 2000 Compatible Access security group at the domain level. If you do not add the domain-based security account used for the SQL Server Agent service to this group, any jobs owned by domain users who are not members of the local Administrators group on the host computer will fail.
PS.
1.Default provisioning login after SQL Server setup finish
2.Default provisioning windows groups
沒有留言:
張貼留言