How to Start or Stop SQL Services without UAC Prompts

  • Article

Scenario

You have OS administrative rights and would like to start/stop SQL Server & SQL Agent services via a script without getting any UAC elevation prompts in an environment where UAC is enabled.

Please review this related blog post for steps to grant non-admin users permissions to stop/start SQL Services: https://blogs.msdn.com/b/sqlupdates/archive/2015/05/14/how-to-start-or-stop-sql-services-without-os-admin-rights.aspx.

For an overview of UAC, please review https://blogs.msdn.com/b/sqlupdates/archive/2015/05/20/understanding-user-account-control-uac-a-brief-summary.aspx. Key points from that post:

  • User rights are different from permissions because user rights apply to user accounts and permissions are attached to objects.
  • Conflicts between privileges (rights) and permissions normally occur only in situations where the rights that are required to administer a system overlap the rights of resource ownership. When rights conflict, a privilege (“right”) overrides a permission. This is partly why when UAC is enabled, you may get prompted when trying a certain administrative operation even though you have full control permissions on the folders/regkeys needed. The problem is typically attributed to the administrative right not being invoked.
  • UAC comes into play at process creation.

 

Solution

1. If the application is marked as needing administrative rights but in reality you know it does not perform any actions which actually require admin rights, consider unmarking it as needing admin rights via one of the following methods:

a. By creating an external manifest file. Note: Embedded manifests have priority over external manifests, if the application already has a manifest, this will not work.

                                       i. https://blogs.msdn.com/b/patricka/archive/2009/12/09/answers-to-several-application-manifest-mysteries-and-questions.aspx

b. Shim the application by using the Application Compatibility Toolkit or by setting the AppCompatFlags regkey manually

                                       i. https://blogs.technet.com/b/askperf/archive/2010/08/27/act-suppressing-elevation-prompts-for-legacy-applications.aspx?Redirected=true

                                     ii. https://blogs.msdn.com/b/windowsvistasecurity/archive/2007/08/09/faq-why-can-t-i-bypass-the-uac-prompt.aspx

                                    iii. https://msdn.microsoft.com/en-us/library/bb756929.aspx

                                    iv. https://blogs.msdn.com/b/cjacks/archive/2008/05/20/enabling-diagnostic-output-from-shims.aspx

                                     v. For Example: [HKEY_CURRENT_USER\Software\Microsoft\Windows NT\CurrentVersion\AppCompatFlags\Layers] "C:\\Windows\\System32\\myapplication.exe"="RunAsInvoker"

c. Uncheck the “Run this program as an administrator” privilege level (right click the exe properties ->compatibility tab->privilege level) if that is how the application chose to indicate it requires admin rights.

2. If you are logged on as an administrator and your application indeed requires admin rights, you can work around the UAC prompts via the following methods.

a. Spawn an elevated process via a non-elevated process.

                                       i. Create a shortcut to the batch script or executable, set the shortcut properties to “Run as Administrator” . Clicking on the shortcut will run the script or executable in administrative mode without a prompt.

RunAsAdministrator_Shortcut
RunAsAdministrator_ShortcutProp

· Note: Any non-admin user who clicks on the shortcut would get prompted for admin credentials (admin user/password).

                                     ii. Similarly, you can create a batch script (such as PStest.cmd), which starts a PowerShell script (such as PStest.ps1) that in turn starts PowerShell in an elevated fashion (using the –verb RUNASargument of the PowerShell.exe).

PowerShell_RunAsAdmin

· Note: Any non-admin user who clicks on the batch file would get prompted for admin credentials (admin user/password).

b. [This effectively removes UAC for admins] Set the security option “User Account Control: Behavior of the elevation prompt for administrators” to “Elevate without prompting”

                                      i. This would change the behavior for all applications requiring admin rights on the server. They would no longer prompt for elevation. Instead, if an administrator runs an application requiring elevation, he/she will immediately receive a full privileged token.

                                     ii. Reference: https://windows.microsoft.com/en-US/windows7/How-do-I-change-the-behavior-of-User-Account-Control-by-using-Group-Policy

c. [Preferred Method] Determine the granular rights/permissions required to run the script or executable, and grant the user those granular rights. Most processes do not require full-blown admin rights. This is the preferred method because your process will not require elevation and hence will not prompt for elevation. If the app is marked as requiring admin rights, you could then go through step 1 above to unmark it.

                                       i. Please review this blog post for steps to grant non-admin users permissions to stop/start SQL Services: https://blogs.msdn.com/b/sqlupdates/archive/2015/05/14/how-to-start-or-stop-sql-services-without-os-admin-rights.aspx

d. References: https://blogs.technet.com/b/securitycontent/archive/2011/04/28/threats-and-countermeasures-guide-security-settings-in-windows-server-2008-r2-and-windows-7-updated.aspx

 

Fany Carolina Vargas | SQL Dedicated Premier Field Engineer | Microsoft Services