Tuesday, March 27, 2012

ADDUSERASADMIN doesn't work if not in normal user

As pointed out in the SQL Express blog recently http://blogs.msdn.com/sqlexpress/archive/2006/11/15/sql-express-sp2-and-windows-vista-uac.aspx (look for the section that starts "Watch out!"), the SQL Express SP2 argument ADDUSERASADMIN will not work correctly if the user is a normal user. If the user is a member of the BUILTIN\Administrators group then Vista will prompt to elevate them to allow the Admin rights to be effective.

However, my reading of the blog post is that if they are normal users, Vista will prompt for the Administrator credentials. This effectively runs the install as the Administrator user. So the ADDUSERASADMIN argument works, but adds the Administrator rather than the normal user.

This is not what I need to happen. Is there any way around this? I have a ClickOnce application. Is there any way to restrict the ClickOnce install to require the user to be a member of the BUILTIN\Administrator group?

Thanks.

Rather than this being a SQL Express problem its looking more like a ClickOnce deployment issue under Vista. When you publish a ClickOnce application it creates a Bootstrapper called setup.exe. Vista's Installer Detection Technology will recognise the string "setup" in the filename etc, and if found, assume it is an installer and prompt for Admin rights. http://technet2.microsoft.com/WindowsVista/en/library/00d04415-2b2f-422c-b70e-b18ff918c2811033.mspx?mfr=true

In my case of a standard user, it will prompt for the Administrator password and then run the install as the Administrator. That may explain why SQL Express adds the wrong use as Admin, rather than the standard user. Anyone had similar issues?

Scott.

|||

Hi Scott,

There are really two completely separate things going on here, first the Vista behavior related to setup.exe and second how ADDUSERASADMIN is working...

ClickOnce / Vista / UAC - ClickOnce is designed to install applications in a secure, per user cache with the benefit being that you do not require administrative permissions to install the VS part of a ClickOnce deployed applicatons. I hadn't really considered this, but you're right that Vista will notice the fact that a process named "setup.exe" has been started and automatically offer a prompt to request admin elevation. You're also correct that if you are a normal user and have to provide a different administrative account, that administative account will be used to run setup.exe. Technically, you don't need to be an administrator to install the VS part of a ClickOnce application, but I've never tried declining the request for admin elevation to see what would happen. This is an interesting question to discuss on the ClickOnce forum, I don't know what their answer is to this.

But that leads us to the second issue...

SQL installation does require admin permissions - Independent of the permission requirements of ClickOnce installation, SQL Express always requires adminstrative permissions to complete. There is no way around this. As you've found, the ADDUSERASADMIN switch will add the the account that is running setup to the SysAdmin group. In the case of a "normal user" who has to elevate the SQL Setup process (and remember, you must do this), the account that is running the setup process will be the adminstrative account that was provided for elevation. Long story short, this is just the way Vista works, we tried to find a way to figure out the actual account of the normal user who really started setup, but there was no way to do it, so we chose to err on the side of working most of the time rather than failing all of the time and forcing users to go deal with this manually for every installation on Vista. In the case of the "normal user" you will have to go back after installation and add the appropriate user with the provisioning tool located at C:\Program Files\Microsoft SQL Server\90\Shared\sqlprov.exe. The tool can be used to add any user to SysAdmin and supports a command line so it can be called from within some type of wrapper. We're looking for ways to make this experince better in the next version of SQL Express.

Mike

|||

Thanks Mike,

That has helped me to sort a few things out in my own mind. My main objective here is to have a good experience for users installing my software. I want them to be able to run install off the CD or website, click a SQL Express EULA, have it install, and then the app fires up. The first step in the app after install is to create the database, if it doesn't already exist. User instances don't work because the data needs to be available over a small LAN. The symptom I face is that if they are a normal user, the install works, but the app fails because it can't create a database. The Windows Administrator is the SqlAdmin, not the installing user :-(

My current thinking is that if I can stop the ClickOnce setup.exe from requesting admin elevation and run as the normal user, the SQL Express bootstrapper package checks will fail because it doesn't have Admin rights. I'm comfortable changing the package to provide a useful error message for my install. If the user installing is in the Admin group, the ClickOnce installer should run without elevation, but the SQL Express installer will require elevation. From your blog, I believe the ADDUSERASADMIN option would then work correctly. Have I missed something?

I tried embedding a manifest in the ClickOnce bootstrapper setup.exe, but got errors. I tried adding stuff about Microsoft.VC80.CRT to the manifest as suggested in a couple of posts, but I got several confusing messages about VC++ and side-by-side (SxS). As I'm a C# guy this all went over my head. I'll take another look at it, but I'm wondering if I'll have any more luck with a standalone manifest.

Appreciate your help,

Scott.

|||

My theory was flawed. I got the manifest embedded in the ClickOnce bootstrapper, but when it tried running SqlExpressChk.exe from the bootstrapper package it seemed to have failed because it had no Admin rights, and then SQL Express didn't get installed at all. I have decided to abandon the ADDUSERASADMIN approach and use the sa login to create the new database from within my startup code. I set the SAPASSWORD when SQL Express is installed, and will include the (encrypted) password in my code.

sql

No comments:

Post a Comment