Share via

SQL Express SP2 and Windows Vista UAC

  • Article

Now that SQL Server 2005 SP2 has been released I thought I should make good on my promise to explain more about how we handle Windows Vista User Access Control in SQL Express SP2. I explained earlier that UAC works by removing the administrator ACEs from your User Token when you are a member of the Builtin\Administrators group. This effectively limits your permission to connect to an manage SQL Server because we assign permissions based on membership in the Builtin\Administrators group. To address this in SQL Server SP2, we created two ways to add a user directly to the SysAdmin Server role during the installation process. One of these ways is unique to SQL Express, the other is available for all editions of SQL Server SP2, but is exposed differently for SQL Express.

Adding the "setup user" to SysAdmin

In order to address the unique needs of SQL Express, we added a new option into the setup process that allows you to automatically add the user who is running setup into the SysAdmin Fixed Server Role. This option is turned off by default, so you have to take a specific action to make this happen. If you don't take this action, the default install of SQL Express SP2 will behave exactly the same as SP1 does with regards to UAC. If you miss setting the option during installation, you can use the provisioning tool that I describe later to accomplish the same thing.

Using the Setup UI - We've added a new checkbox to the Configuration Options page of the Setup UI with the caption 'Add user to the SQL Server Administrator role'. The checkbox is unchecked by default. Checking this box will result in a Login being created for the user account that is running Setup.exe and that Login being associated with the SysAdmin role. This is how we provide permissions for the Builtin\Administrators group already.

Watch out! When you start the installer for SQL Express SP2 on Windows Vista with UAC enabled you will be asked to "elevate" the process and give it administrative privileges. If you are already an administrator on the computer, you can elevate the process using your own account with your Admin Token restored. Windows Vista also supports the ability to allow normal users (e.g. non-administrative users) to provide the credentials of a different user who has administrative privileges in order to elevate the installer. If you do this, Setup will be running as the administrative user, not as the normal user who is logged in. In this case, a Login would be created for the administrative user, not the normal user.

Using the Setup command line - To support embedding, we've added a new command line argument that results in the same behavior. The argument is ADDUSERASADMIN with the following usage:

To add a Login for the user running setup:

ADDUSERASADMIN=1

To not add a Login for the user running setup:

ADDUSERASADMIN=0 (default)

Using the SQL Server User Provisioning on Vista tool

All editions of SQL Server SP2 will include the SQL Server User Provisioning on Vista tool. There are two ways to launch this tool:

  • On non-Express editions, the tool can be launched from a link on the last page of the Setup UI.
  • For all editions, including SQL Express, you can launch the tool from within the SQL Surface Area Configuration tool. I'll discuss this part since it applies to SQL Express.

When you launch SQL SAC in SQL Express SP2, you should see a link that reads 'Add New Administrator'. Clicking this link should launch the provisioning tool and allow you to specify the user you want to add to the SysAdmin role and the SQL instances that you want to make the addition to. You can specify more than once instance at a time, but only a single user at a time. For SQL Express, you will only see SQL Server instances listed, but for non-Express editions other SQL services will be listed as well.

Note: I've found in recent builds that the link in SQL SAC is not working correctly, I'm not sure if this is the case in the November CTP or not. If the link in SQL SAC does not work, you can launch the provisioning tool manually by double-clicking the file located at:

C:\Program Files\Microsoft SQL Server\90\Shared\sqlprov.exe

When not to use this

If you're application is already creating specific Logins (whether SQL or Windows Authentication) as part of your installation process then you will likely not be impacted by Windows Vista UAC. Use of SQL Express provisioning mechanism or the provisioning tool is targeted at installing SQL Express for use in an administrative environment, in fact, both SQL Setup and the provisioning tool require the user to have administrative privileges on the computer. When you are installing SQL Express to be used as part of an application, it is a best practice to create specific Logins and Users with appropriate permissions for the use of your application.

- Mike

Comments

  • Anonymous
    December 15, 2006
    Vista flaw could haunt Microsoft Poor journalism is one thing, but deliberately trying to misinform...

  • Anonymous
    December 19, 2006
    Owen Thomas wrote an article for the Business 2.0 secton of CNNMoney.com on December 15,2006 that made

  • Anonymous
    February 20, 2007
    It's been a long road filled with many shouts of "Are we there yet?", so I'm happy to say that yet, we're

  • Anonymous
    June 18, 2007
    Some customers have experienced problems connecting to SQL Server 2005 on Vista and Longhorn. Even though

  • Anonymous
    August 07, 2007
    I just gave up trying to get SQL server installed on a vista system. Its a nightmare. It feels like running windows millenium on an alpha blended system (pun intended). We are reverting back to windows 2000 server. UAC and the system integration with rights management is a bloated .... [words fail me]. You guys tried to get (l)unix features into an operating system, had to many program/product managers have there say and the result is a headache. Its been a very long time ago (been into the business successfully 20 years) where I actually had to say: I give up on this.

  • Anonymous
    March 25, 2010
    I tried the switch ADDUSERASADMIN while installing on a Windows 7 machine, but it didn't seem to work.  Is there a switch to use when installing SQL Express 2005 on a Windows 7 machine to make the Windows user an admin in the instance of SQL Server Express being installed at the command prompt?