User Account Control and SQL Server

  • Article

By Devendra Tiwari, Microsoft SQL Server Product Team

See other Security Tip of the Month columns

Windows Vista includes User Account Control (UAC), a new feature that helps administrators manage their use of elevated privileges. When running on Windows Vista, administrators and other elevated users do not use their administrative privileges by default. Instead, they perform most actions as standard users, temporarily assuming their administrative privileges only when necessary. The elevation occurs at the process level meaning that only one instance of the application is elevated. For example, elevating one use of CMD.exe does not run all CMD.exe processes at the same privilege level even if they are started at the same time.

UAC provides a number of mechanisms by which an executable program can be elevated to run under administrator privilege at the time it is launched. These include listing the application name in the Windows database (AppCompat), associating a manifest file with the executable, and right-clicking on the executable for one-time elevation.

Impact on SQL Server

User Account Control affects Microsoft SQL Server in terms of connectivity (SQL Server login) and in limiting access to resources on the administrators’ access control list (ACL).

SQL Server Connectivity
In versions earlier than Windows Vista and Windows Server 2008, members of the local administrator group do not need their own SQL Server logins and they do not need to be granted administrator privileges inside SQL Server. They connect to SQL Server as the built-in server principal BUILTIN\Administrators (B\A), and they have administrator privileges because B\A is a member of the sysadmin fixed server role. Administrator rights are stripped away in Windows Vista and Windows Server 2008, so administrator users cannot connect to SQL Server instance by virtue of being B\A, unless connecting from an elevated client application.

Consider the following pre-Windows Vista scenario:

  1. Abby runs Windows NT with administrator privilege (unfortunately very common).

  2. Abby makes a request to connect to a SQL Server instance, which succeeds, and she is subsequently able to take any desired action on that instance.

Whether Abby realizes it or not, she has gained access to the SQL instance since the “BUILTIN\Administrators” Windows group is a member of the SQL Server sysadmin server role. When running on Windows Vista and Windows Server 2008, the connection attempt fails. Abby’s token does not include administrator privileges, and so the SQL Server instance does not recognize her as a valid login.

Administrator ACLs
By default prior to Windows Vista, administrators have full access to the local Windows resources like files, folders, and registry. Since the administrators perform most actions as a standard user on Windows Vista and Windows Server 2008, their access to these resources is limited. Any SQL Server application that accesses files and folders, registry keys and values, system services, operating system processes, or event logs and that relies on B\A will not work functionally in Windows Vista and Windows Server 2008.

The goal of SQL Server is to enable customers to run in the least privilege principle and provide good end-user experience in Windows Vista and Windows Server 2008 with UAC enabled. Microsoft SQL Server 2005 Service Pack 2 (SP2) addresses these issues:

  • SQL Server 2005 SP2 includes a provisioning tool that can be launched from either Setup or the SQL Server Surface Area Configuration tool, which allows you to add a user into the sysadmin fixed server role to perform administrator actions in SQL Server.

  • In SQL Server 2005 SP2, we have also selectively elevated SQL Server executables that perform administrator actions in Windows.

  • For Microsoft SQL Server 2008, we plan to improve the account provisioning and overall UAC experience. During the setup process, you will be able to provision more than one account into the sysadmin fixed server role to perform administrator actions on SQL Server. There will also be better separation between Windows and SQL Server administration.

The impact of UAC behavior on SQL Server is documented at https://msdn2.microsoft.com/en-us/library/bb326612.aspx.

How to Run SQL Server Applications Securely in Windows Vista and Windows Server 2008

The supported version of SQL Server for Windows Vista and Windows Server 2008 is SQL Server 2005 SP2. To run SQL Server applications securely, please note the following:

  • Do not elevate SQL Server 2005 SP2 applications by default. Applications that are elevated in SP2 include: Surface Area Configuration, SQL Diagnostics, SQL Configuration Manager, Reporting Services Configuration, Reporting Services Key Management, SQL Watson Error Reporting, and Hotfix.exe. These applications take Windows NT administrator action and require elevation.

  • Do not elevate SQL Server client applications like SQL Server Management Studio (SSMS).

  • At the end of SP2 setup, the SQL Server provisioning tool is launched. It is important to specify a user in the provisioning tool. This user is provisioned as a login inside SQL Server with sysadmin rights to avoid connectivity issues after SP2 setup from a non-elevated SQL Server client. If you do not specify a user in the provisioning tool, the SP2 setup invoker is provisioned as a login with sysadmin privileges. The provisioned login can then connect to the SP2 instance after setup and provision other users into appropriate roles

  • For console applications which require elevation For Ex Reporting Services Key Management (RSKeyMgmt.exe), run it from elevated command prompt.

The SQL Server Security Best Practices white paper covers some of the operational and administrative tasks associated with SQL Server 2005 security and enumerates best practices and operational and administrative tasks that will result in a more secure SQL Server system. The white paper provides security guidance on surface area reduction, authentication mode, service account consideration, password policy, administrator privileges, authorization, encryption, auditing, and patching. It is located at https://www.microsoft.com/technet/prodtechnol/sql/2005/sql2005secbestpract.mspx.

Other Resources