Configure File System Permissions for Database Engine Access

Applies to: SQL Server (all supported versions)

This topic describes how to grant the SQL Server Database Engine file system access to the location where database files are stored. The Database Engine service must have permission of the Windows file system to access the file folder where database files are stored. Permission to the default location is configured during setup. If you place your database files in a different location, you might need to follow these steps to grant the Database Engine the full control permission to that location.

Beginning with SQL Server 2012 (11.x) permissions are assigned to the per-service SID for each of its services. This system helps provide service isolation and defense in depth. The per-service SID is derived from the service name and is unique to each service. The topic Configure Windows Service Accounts and Permissions describes the per-service SID and provides the names in the section Windows Privileges and Rights. It is the per-service SID that must be assigned the access permission on the file location.

To Grant File System Permission to the Per-service SID

Using Windows Explorer, navigate to the file system location where the database files are stored. Right-click the file system folder, and then click Properties.

On the Security tab, click Edit, and then Add.

In the Select Users, Computer, Service Account, or Groups dialog box, click Locations, at the top of the location list, select your computer name, and then click OK.

In the Enter the object names to select box, type the name of the per-service SID name listed in the Books Online topic Configure Windows Service Accounts and Permissions. (For the Database Engine per service SID name, use NT SERVICE\MSSQLSERVER for a default instance, or NT SERVICE\MSSQL$InstanceName for a named instance.)

Click Check Names to validate the entry. (If the validation fails, it might advise you that the name was not found. When you click OK, a Multiple Names Found dialog box appears. Now select the per-service SID name, either MSSQLSERVER or NT SERVICE\MSSQL$InstanceName, and then click OK. Click OK again to return to the Permissions dialog box.)

In the Group or user names box, select the per-service SID name, and then in the Permissions for box, select the Allow check box for Full control.

Click Apply, and then click OK twice to exit.

How to add a user to Terminal Services RDP permissions by using WMI

This article describes three methods to add users or groups to Terminal Services Remote Desktop Protocol (RDP) permissions.

Original product version: В Windows Server 2012 R2
Original KB number: В 290720

Summary

Two of the three methods use Windows Management Instrumentation (WMI). One method is through the graphical user interface (GUI), and the other two methods use WMI by using a script and the WMI command-line utility, wmic.

More information

To add users or groups to Terminal Services RDP permissions, use one of the following methods.

Using the GUI

1. Open Terminal Services Configuration.
2. In the Connections folder, right-click RDP-Tcp.
3. Select Properties.
4. On the Permissions tab, select Add, and then add the wanted users and groups.

You can’t use the GUI to configure permissions to sign in to the console session with RDP. To change permissions for the console session (session zero), you must use the WMI methods below, and specify Console instead of RDP-Tcp for the terminal name.

Using WMI in a script

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. Which includes, but isn’t limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you’re familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure. However, they won’t modify these examples to provide added functionality or construct procedures to meet your specific requirements. Create a script by using the following code sample:

Where «Domain\User», X:

• Domain\User: Target domain and account (user or group) to which permissions are to be granted. For local accounts, replace Domain\User with only User, where User is a local account on the computer on which you’re running the command.
• X: The type of access to be granted:
  0 = WINSTATION_GUEST_ACCESS
  1 = WINSTATION_USER_ACCESS
  2 = WINSTATION_ALL_ACCESS

To change permissions for the console session, change the terminal name to Console instead of to RDP-Tcp.

To revert the permissions back to the default permissions, specify the relevant terminal name. Then, call the RestoreDefaults method.

Using the WMI command-line utility: WMIC

At a command prompt, type wmic.

If it isn’t in the path, add %SystemRoot%\System32\Wbem\ , or change to that directory and run wmic.

At the wmic:root\cli> prompt , type the following command:
PATH WIN32_TSPermissionsSetting.TerminalName=»RDP-TCP» call AddAccount «Domain\user»,X

Where «Domain\User», X:

• Domain\User: Target domain and account (user or group) to which permissions are to be granted. For local accounts, replace Domain\User with only User, where User is a local account on the computer on which you’re running the command.
• X: The type of access to be granted:
  0 = WINSTATION_GUEST_ACCESS
  1 = WINSTATION_USER_ACCESS
  2 = WINSTATION_ALL_ACCESS

To change permissions for the console session, change the terminal name to Console instead of to RDP-Tcp.

To revert the permissions back to the default permissions, specify the relevant terminal name. Then, call the RestoreDefaults method.

The following information is an example of the text that you’ll see after you run wmic and input the command:

Type quit to exit the wmic prompt and to return to the command prompt.

User access options with Windows Admin Center

Applies To: Windows Admin Center, Windows Admin Center Preview

When deployed on Windows Server, Windows Admin Center provides a centralized point of management for your server environment. By controlling access to Windows Admin Center, you can improve the security of your management landscape.

Gateway access roles

Windows Admin Center defines two roles for access to the gateway service: gateway users and gateway administrators.

Access to the gateway does not imply access to the target servers visible by the gateway. To manage a target server, a user must connect with credentials that have administrative privileges on the target server.

Gateway users can connect to the Windows Admin Center gateway service in order to manage servers through that gateway, but they cannot change access permissions nor the authentication mechanism used to authenticate to the gateway.

Gateway administrators can configure who gets access as well as how users will authenticate to the gateway.

If there are no access groups defined in Windows Admin Center, the roles will reflect the Windows account access to the gateway server.

Identity provider options

Gateway administrators can choose either of the following:

Smartcard authentication

When using Active Directory or local machine groups as the identity provider, you can enforce smartcard authentication by requiring users who access Windows Admin Center to be a member of additional smartcard-based security groups. Configure smartcard authentication in Windows Admin Center.

Conditional access and multi-factor authentication

By requiring Azure AD authentication for the gateway, you can leverage additional security features like conditional access and multi-factor authentication provided by Azure AD. Learn more about configuring conditional access with Azure Active Directory.

Role-based access control

By default, users require full local administrator privileges on the machines they wish to manage using Windows Admin Center. This allows them to connect to the machine remotely and ensures they have sufficient permissions to view and modify system settings. However, some users may not need unrestricted access to the machine to perform their jobs. You can use role-based access control in Windows Admin Center to provide such users with limited access to the machine instead of making them full local administrators.

Role-based access control in Windows Admin Center works by configuring each managed server with a PowerShell Just Enough Administration endpoint. This endpoint defines the roles, including what aspects of the system each role is allowed to manage and which users are assigned to the role. When a user connects to the restricted endpoint, a temporary local administrator account is created to manage the system on their behalf. This ensures that even tools which do not have their own delegation model can still be managed with Windows Admin Center. The temporary account is automatically removed when the user stops managing the machine through Windows Admin Center.

When a user connects to a machine configured with role-based access control, Windows Admin Center will first check if they are a local administrator. If they are, they will receive the full Windows Admin Center experience with no restrictions. Otherwise, Windows Admin Center will check if the user belongs to any of the pre-defined roles. A user is said to have limited access if they belong to a Windows Admin Center role but are not a full administrator. Finally, if the user is neither an administrator nor a member of a role, they will be denied access to manage the machine.

Role-based access control is available for the Server Manager and Failover Cluster solutions.

Available roles

Windows Admin Center supports the following end-user roles:

Role name	Intended use
Administrators	Allows users to use most of the features in Windows Admin Center without granting them access to Remote Desktop or PowerShell. This role is good for «jump server» scenarios where you want to limit the management entry points on a machine.
Readers	Allows users to view information and settings on the server, but not make changes.
Hyper-V Administrators	Allows users to make changes to Hyper-V virtual machines and switches, but limits other features to read-only access.

The following built-in extensions have reduced functionality when a user connects with limited access:

• Files (no file upload or download)
• PowerShell (unavailable)
• Remote Desktop (unavailable)
• Storage Replica (unavailable)

At this time, you cannot create custom roles for your organization, but you can choose which users are granted access to each role.

Preparing for role-based access control

To leverage the temporary local accounts, each target machine needs to be configured to support role-based access control in Windows Admin Center. The configuration process involves installing PowerShell scripts and a Just Enough Administration endpoint on the machine using Desired State Configuration.

If you only have a few computers, you can easily apply the configuration individually to each computer using the role-based access control page in Windows Admin Center. When you set up role-based access control on an individual computer, local security groups are created to control access to each role. You can grant access to users or other security groups by adding them as members of the role security groups.

For an enterprise-wide deployment on multiple machines, you can download the configuration script from the gateway and distribute it to your computers using a Desired State Configuration pull server, Azure Automation, or your preferred management tooling.

Access Permission

Applies to: Windows Server (Semi-Annual Channel), Windows Server 2016

Access permission is configured on the Overview tab of each network policy in Network Policy Server (NPS).

This setting allows you to configure the policy to either grant or deny access to users if the conditions and constraints of the network policy are matched by the connection request.

Access permission settings have the following effect:

• Grant access. Access is granted if the connection request matches the conditions and constraints that are configured in the policy.
• Deny access. Access is denied if the connection request matches the conditions and constraints that are configured in the policy.

Access permission is also granted or denied based on your configuration of the dial-in properties of each user account.

User accounts and their properties, such as dial-in properties, are configured in either the Active Directory Users and Computers or the Local Users and Groups Microsoft Management Console (MMC) snap-in, depending on whether you have Active DirectoryВ® Domain Services (AD DS) installed.

The user account setting Network Access Permission, which is configured on the dial-in properties of user accounts, overrides the network policy access permission setting. When network access permission on a user account is set to the Control access through NPS Network Policy option, the network policy access permission setting determines whether the user is granted or denied access.

In Windows Server 2016, the default value of Network Access Permission in AD DS user account dial-in properties is Control access through NPS Network Policy.

When NPS evaluates connection requests against configured network policies, it performs the following actions:

• If the conditions of the first policy are not matched, NPS evaluates the next policy, and continues this process until either a match is found or all policies have been evaluated for a match.
• If the conditions and constraints of a policy are matched, NPS either grants or denies access, depending on the value of the Access Permission setting in the policy.
• If the conditions of a policy match but the constraints in the policy do not match, NPS rejects the connection request.
• If the conditions of all policies do not match, NPS rejects the connection request.

Ignore user account dial-in properties

You can configure NPS network policy to ignore the dial-in properties of user accounts by selecting or clearing the Ignore user account dial-in properties check box on the Overview tab of a network policy.

Normally when NPS performs authorization of a connection request, it checks the dial-in properties of the user account, where the network access permission setting value can affect whether the user is authorized to connect to the network. When you configure NPS to ignore the dial-in properties of user accounts during authorization, network policy settings determine whether the user is granted access to the network.

The dial-in properties of user accounts contain the following:

• Network access permission
• Caller-ID
• Callback options
• Static IP address
• Static routes

To support multiple types of connections for which NPS provides authentication and authorization, it might be necessary to disable the processing of user account dial-in properties. This can be done to support scenarios in which specific dial-in properties are not required.

For example, the caller-ID, callback, static IP address, and static routes properties are designed for a client that is dialing into a network access server (NAS), not for clients that are connecting to wireless access points. A wireless access point that receives these settings in a RADIUS message from NPS might not be able to process them, which can cause the wireless client to be disconnected.

When NPS provides authentication and authorization for users who are both dialing in and accessing your organization network through wireless access points, you must configure the dial-in properties to support either dial-in connections (by setting dial-in properties) or wireless connections (by not setting dial-in properties).

You can use NPS to enable dial-in properties processing for the user account in some scenarios (such as dial-in) and to disable dial-in properties processing in other scenarios (such as 802.1X wireless and authenticating switch).

You can also use Ignore user account dial-in properties to manage network access control through groups and the access permission setting on the network policy. When you select the Ignore user account dial-in properties check box, network access permission on the user account is ignored.

The only disadvantage to this configuration is that you cannot use the additional user account dial-in properties of caller-ID, callback, static IP address, and static routes.

For more information about NPS, see Network Policy Server (NPS).