Getting things working on Vista (aka - Dealing with User Account Control)

  • Article

Update: After a recent article on CNNMoney.com (see my post with the link to the article) I thought I would add to this post to clarify that SQL Express SP1 is supported for use on Windows Vista. As discussed in this post, there are some issues that SP2 addresses, so I still say deploying SP2 is the way to go for those who can wait, but if you can't, deploying SP1 is supported. - Mike

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

One of my co-workers called me the other night and said, "I've got a question about SQL Express SP1 on Vista." My response, of course, was "Doesn't work right, does it?" Having had a little laugh at my friends expense, I proceeded to tell him how to get SQL Express SP1 running on Vista. Since he's asking, I'm sure others are too, so I thought I'd share it with the world.

First, a disclaimer: Full Vista support will happen starting with SQL Express SP2. We'll be releasing a Community Technology Preview real soon and you should target using SP2 for Vista compatibility in your applications. The workaround here is to tide you over until you can start testing with the CTP.

Note: SQL Express SP2 will be released to Microsoft Update, so existing installations will be upgraded automatically as I'm mentioned before.

Note: You should not use SQL Express RTM on Windows Vista, there are some known problems that impact functionality. SQL Express SP1 is reasonable for starting to test your applications once you're handled the UAC issues per the rest of this post.

What happened?

Three little words are changing the way you work on Windows in Vista, those words are User Account Control or UAC. I'm not going to go into the details of UAC here, rather I'll direct you to the UAC Overview page for more information. UAC changes the way Windows treats administrative users, in short, it makes you into a normal user, but gives you the ability to "elevate" your permissions when you need to be an Administrator. For those of you using Vista already, UAC is responsible for all those "Allow" dialogs that pop up. UAC is a good thing (no, really) and promises to make Windows a more secure place to play.

Why does this impact SQL?

In short, SQL Express assumes you're an administrator on the computer in order to give you permissions to do things. As part of the default installation, we create Logins for the Builtin\Administrator user and the Builtin\Administrators group and assign both of these Logins to the SysAdmin Fixed Server Role. This way, everyone on the computer who is part of the Administrators group, is automatically a SysAdmin. Neat and clean, yeah right…

UAC functions by removing the administrator ACEes from your User Token, which effectively makes you a normal user. When you log into SQL Express it takes a look at your Token to see if it knows anything about you. First, it sees that there is no specific Login for your User Account. (Strike One) Next, it checks if you're part of any group it has a Login for, because of UAC, you're not part of Builtin\Administrators unless you've elevated, which you didn't. (Strike Two) Finally, and this is unique to SQL Express, you are part of Builtin\Users, which does have a Login, so you're not quite out. (Hooray for the home team) Unfortunately, Builtin\Users doesn't have many (any?) permissions in a default installation of SQL Express, so you can log in, but that's pretty much it. If you try to do anything, you'll get an error indicating a failure, usually do to a permissions error.

Another Note: Like I said, this is unique to SQL Express, other Editions of SQL Server 2005 don't actually create a Login for Builtin\Users, so you're not part of any existing Login and you fail to even connect to the non-Express Server. (Strike Three, You're outta there and my metaphor is complete.)

What do we do?

To get the same functionality from a default install of SQL Express on Windows Vista, you will need to create a Login explicitly for your User Account. This is where it's important that you have the ability to elevate yourself to get administrative rights when you need them. I'll give the steps to do this using Management Studio Express, but you can do the same with SQLCmd and T-SQL statements, you just have to launch the command prompt (cmd.exe) as elevated.

  • On the Start menu, right-click on SQL Sever 2005 Management Studio Express and click Run as administrator.
  • When prompted, choose to Continue. (This causes SSMSE to run with your Admin Tokens intact so that SQL Express recognizes you as an Administrator.)
  • Once SSMSE is opened, connected to the Instance of SQL Express that you're interested in and open the Security folder.
  • Right-click on Logins and click New Login…
  • Fill in the information to create a Login for your Windows Account. Be sure to select the SysAdmin role from the Server Roles tab.
  • Click OK and you're ready to go.

The next time you launch SSMSE, you don't have to go through all this because you are now explicitly a SysAdmin on your Instance of SQL Express. You will need to do this for every Instance you have installed.

A note about data access and security

You should not provide data access via the SysAdmin Server Role, rather, explicit Logins and Users should be created to grant users the permissions they need in the databases they need to access. This is a common practice, and if you're doing this already, you will find that data access in your applications will continue to work without modification when running on Windows Vista with UAC enabled. If your application has been relying on the SysAdmin Role, you should move to a model of explicit Logins and Users that provide data access using the least privileges required by the user. This is just good security design for an application. Read the BOL topic Security Consideration for Databases and Database Applications to get more details about recommended security designs for applications.

A note about User Instances

If you developer applications that rely on User Instances to provide data access, they should just work. User Instances were designed to enable non-administrative use of SQL Server, which is exactly what happens in Windows Vista with UAC turned on.

What's next?

We've made some changes to the installer for SQL Express SP2 to help manage the UAC behavior. I'll discuss these in a future post once the CTP is released.

- Mike