The minimum Windows privilege level required to run the Diagnostic Server is Power User. Spotlight on SQL Server requires that the Diagnostic Server have the following permissions to successfully connect to a SQL Server instance.
SQL Server permissions required on the Diagnostic Server
Each SQL Server connection from the Diagnostic Server must have access to a SQL Server account that is a member of the sysadmin server role. This can be a SQL Server login (such as 'sa'), or the Diagnostic Server can be installed to run under a Windows account that is trusted by SQL Server.
The data must be retrievable over the network so Spotlight on SQL Server requires the use of port 1433 TCP/IP to SQL Server to do this. The port on the Diagnostic Server must also be open. The default port is 3843. The data collection subsystem of the Diagnostic Server listens on port 3166. No external connections are required on this port.
Windows permissions required on the Diagnostic Server
To retrieve performance counter information, the Diagnostic Server executes WMI queries against the Windows server on which SQL Server is currently running. To do this, the Diagnostic Server needs to have access to a Windows account that can retrieve this information. This account will normally be a member of the Administrators group on the server. If you do not have access to an Administrator account, have your Windows administrator assign you the required permissions on the server.
By default, only the local Administrators group has remote permissions to WMI. Instructions on how to configure WMI "Remote Enable" permissions are shown below:
1. Add the user(s) in question to the “Performance Monitor Users” group;
2. Under Services and Applications, bring up the properties dialog of WMI Control. In the Security tab, highlight Root/CIMV2, click Security; add Performance Monitor Users and enable the options : Enable Account and Remote Enable
3. Run dcomcnfg. At Component Services | Computers | My Computer, in the COM security tab of the Properties dialog click "Edit Limits" for both Access Permissions and Launch and Activation Permissions. Add Performance Monitor Users and allow remote access, remote launch, and remote activation
4. Select Windows Management Instrumentation under Component Services | Computers | My Computer | DCOM Config, and give Remote Launch and Remote Activation privileges to Performance Users Group.
The following data won’t be displayed with the restricted user account:
- Processes | “Services” and “System Drivers”
- Disks | “Disk Summary” and “File Sizes”
- Network | “Sessions”