Getting things working on Vista (aka - Dealing with User Account Control)
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
Comments
Anonymous
October 11, 2006
PingBack from http://edgardorossetto.net/2006/10/11/programadores-net-vale-la-pena-windows-vista/Anonymous
October 18, 2006
The SQL Server Express team has published how to get SSE working on Windows Vista .Anonymous
October 18, 2006
В блоге команды SQL Server Express опубликовано как использовать SQL Server ExprAnonymous
October 18, 2006
The SQL Server Express team have published some instructions on getting SQL Express SP1 running on Vista.Anonymous
October 22, 2006
The SQL Server Express team have published some instructions on getting SQL Express SP1 running on Vista.Anonymous
November 07, 2006
The November CTP of SQL Express SP2 is available at a download center near you. As I've written beforeAnonymous
November 13, 2006
Приближение Windows Vista заставляет задуматься, а что делать с необходимым ПО. Например, с SQL ServerAnonymous
November 15, 2006
Now that SQL Server 2005 SP2 has been released I thought I should make good on my promise to explainAnonymous
December 12, 2006
I ran into one little snag while getting Sql Management Studio 2005 running with Vista. This post solvedAnonymous
December 19, 2006
Owen Thomas wrote an article for the Business 2.0 secton of CNNMoney.com on December 15,2006 that madeAnonymous
January 03, 2007
The SQL Server Express team have published some instructions on getting SQL Express SP1 running on Vista.Anonymous
March 08, 2007
from Microsoft's site: http://www.microsoft.com/sql/editions/express/default.mspx Microsoft SQL Server Desktop Engine (MSDE) Will Not Be Supported on Microsoft Vista If you plan to build an application and redistribute a database with it, you should be using SQL Server 2005 Express Edition SP1 as Windows Vista will not support MSDE.Anonymous
May 09, 2008
The SQL Server Express team have published some instructions on getting SQL Express SP1 running on Vista. Note that the RTM Version will not work as there are some known Issues, but the SP1 Install will work for Testing purposes. According to the Post