URGENT: SQL Packager C# template requires admin privileges..

hswami

I am faced with a very serious issue that could potentially hold up our software deployment.

We are using SQL Packager to generate a database installer which we ship with our client software installer to create a local MSDE database on the client machine. Our customer locks down the client machines to prevent the non-technical users from messing around with system settings - most users are either regular Users or Power Users - none has Administrator privileges.

Our installation including the database creation which is done by our customer's IT department as an Admin user on each machine works fine. We install the database installer on the client machine as a way for the user to get back to a blank database in case of any software failures or database corruption that renders the database unusable. Given that our users run under severely limited Windows accounts, they are unable to run the database installer which is a condition of successful deployment of our software.

The client machines are not desktops running on a corporate LAN. Instead, these are ruggedized laptops that are user by telephone technicians spread out over 9 US states and hence pose significant deployment and update challenges. It is mandatory that we provide fail-over mechanisms with our installation to "reset" the software back to "factory-state" in case of any unforseen failures, and we were relying on the database installer to do the database reset as part of a larger software reset procedure.

If a fix for this problem cannot be provided, at least provide some information as to how we could fix this ourselves. It is obvious that the culprit is the C# packager template shipped with SQL Toolkit. The problem is easily recreatable by creating and running a database install or upgrade package under a regular Windows User account (least privileged user account). The problems seem to be centered around access to the registry and the event log, although there may be more. Please do the needful and/or advise.

Brian Donahue

Hello Himanshu,

Thanks for your post. If the problem is that users can't run the database package produced with Packager, but have rights to the database, then it could be an issue with assembly trust.

Because the packages run on .Net Framework, it's possible that assembly trusts exist on the system that would cause untrusted assemblies to not be able to access the registry or other resources.

You can elevate the trust level by going to the Control Panel, then using the .Net Framework Wizard's Trust an Assembly Wizard.

Please let me know if this helps.

hswami

Brian,

Thanks for your reply. However, requiring the users to use the assembly trust wizard is not an acceptable solution - we do not have control over the machine configurations, especially any that are security-related.

To make the deployment picture a little clearer, our customer's user base consists of about 20,000 technicians spread out across 9 US states. We cannot ask our customer to tweak assembly trust rights because we cannot dictate security policies on their machines - we are given a set of privileges to work with and there is no wiggle room. We are provided with Ghost images of the target machine configurations and we have to cater our software to those configurations - period.

To complicate deployment problems further, since our customer's users are so far flung and geographically spread out, the only way to deliver updates for our application is by the means of a patch - again we intend to use the SQL packager to deliver updates to the database - except that we cannot in light of this problem.

The C# template code has to be changed so that it does not require full trust privileges on the client. Our entire application has been designed on this premise. I just want to know if I or RedGate support can patch/fix the packager's C# template code to achieve the same. Please advise.

Brian Donahue

Hello,

We might be able to do something if we know what the problem is. What happens when the user tries to run the package? Is there an error or exception message shown?

hswami

Brian,

I will try to get some debug logs and stack traces by running the package under the CLR debugger on Monday morning EST.

Regards,

Himanshu

hswami

Brian,

I think I have found the solution to this problem. If I start the database upgrade package from the command-line, I have to explicitly specify all the information on the command-line (database, username, password). Previously I was specifying only the database on the command-line which caused the package to use Windows authentication by default even though the package was built using SQL Server authentication. The explicit command-line does the trick and hopefully this should work. I am not sure of the default/expected behavior so if it is a bug, maybe you could look into this for a future releases. Thanks for your help over the weekend. It is nice to have a vendor be responsive to customer needs.

Regards,

Himanshu

Brian Donahue

Hello Himanshu,

Thanks!

Using SQL authentication rather than Windows authentication is probably the best way to side-step the problem of requiring the currently logged in user to be an administrator and therefore, on SQL Servers that are configured for integrated security, have the necessary rights to create a database.

hswami

Brian,

The reason the database package was crashing under limited accounts was that in case of exceptions it was attempting to write to the EventLog and the account does not have the necessary privileges to access the event log.

I could look at the code and come up with a kludgy fix myself but maybe you guys could look at it and come up with a more elegant solution for reporting errors in cases where access to the event log or even the file system may be limited or even denied. Some kind of a logging facility that can log to the event log, a file, or even to the screen (for extremely limited environments) - such that this logging facility is not tied to whether the package is being run with or without the /quiet option as is the case currently (specifying /quiet logs to stdout but provides no progress UI, omiting it provides a UI but always logs to event log).

Another nice feature would be the addition of a /noconfirm switch that would allow turning off the last notification message box when being run from within automated update processes.

Regards,

Himanshu

Brian Donahue

Hello Himanshu,

From what I can see in the Packager code template, it will display all recognized errors (SQL exceptions, etc) in a messagebox, and will only write exceptions to the event log if they're an 'unknown' type (a generic System.Exception).

hswami

Here is a dump of the 'output window' when running the package under the debugger. The package fails to run since it uses Windows authentication by default - the command-line contains only /makedatabase. It then proceeds to log the exception to the event log and chokes with an unfriendly message. See if you can provide some level of control over the logging through additional command-line switches.

---

'DefaultDomain': Loaded 'c:\winnt\microsoft.net\framework\v1.1.4322\mscorlib.dll', No symbols loaded.
'TechAccess.Database.Install': Loaded 'C:\Program Files\TechAccess\TechAccess.Database.Install.exe', No symbols loaded.
'TechAccess.Database.Install.exe': Loaded 'c:\winnt\assembly\gac\system.windows.forms\1.0.5000.0__b77a5c561934e089\system.windows.forms.dll', No symbols loaded.
'TechAccess.Database.Install.exe': Loaded 'c:\winnt\assembly\gac\system\1.0.5000.0__b77a5c561934e089\system.dll', No symbols loaded.
'TechAccess.Database.Install.exe': Loaded 'c:\winnt\assembly\gac\system.drawing\1.0.5000.0__b03f5f7f11d50a3a\system.drawing.dll', No symbols loaded.
'TechAccess.Database.Install.exe': Loaded 'c:\winnt\assembly\gac\system.data\1.0.5000.0__b77a5c561934e089\system.data.dll', No symbols loaded.
'TechAccess.Database.Install.exe': Loaded 'c:\winnt\assembly\gac\system.xml\1.0.5000.0__b77a5c561934e089\system.xml.dll', No symbols loaded.
Login failed for user 'SALEMGB\LMJFYPX'.
An unhandled exception of type 'System.Security.SecurityException' occurred in mscorlib.dll

Additional information: Requested registry access is not allowed.


Unhandled Exception: System.Security.SecurityException: Requested registry access is not allowed.
at Microsoft.Win32.RegistryKey.OpenSubKey(String name, Boolean writable)
at System.Diagnostics.EventLog.FindSourceRegistration(String source, String machineName, Boolean readOnly)
at System.Diagnostics.EventLog.SourceExists(String source, String machineName)
at System.Diagnostics.EventLog.WriteEntry(String message, EventLogEntryType type, Int32 eventID, Int16 category, Byte[] rawData)
at System.Diagnostics.EventLog.WriteEntry(String source, String message, EventLogEntryType type, Int32 eventID, Int16 category, Byte[] rawData)
at System.Diagnostics.EventLog.WriteEntry(String source, String message, EventLogEntryType type, Int32 eventID, Int16 category)
at System.Diagnostics.EventLog.WriteEntry(String source, String message, EventLogEntryType type, Int32 eventID)
at System.Diagnostics.EventLog.WriteEntry(String source, String message, EventLogEntryType type)
at Package.TechAccessDatabaseInstall.PackageUtils.WriteExceptionToEventLog(Exception x)
at Package.TechAccessDatabaseInstall.PackageUtils.ShowMsg(Exception x)
at Package.TechAccessDatabaseInstall.PackageMain.Main(String[] args)

The program '[1292] TechAccess.Database.Install.exe' has exited with code 0 (0x0).

Brian Donahue

Hello Himanshu,

I see from the stack trace that the error is being logged because the 'exception' type is not a packager exception: it's a security exception.

Permanently changing the SQL Packager Code Template can help. If you edit PackageMain.cs and change this:

public static void ShowMsg(Exception x)
		{
			//Check exception type
			MessageBoxIcon msgTypeIcon = MessageBoxIcon.Error;
			string msgTypeText = "Error: ";
			if(x is PackageExceptionInfo)

...to this...

public static void ShowMsg(Exception x)
		{
			//Check exception type
			MessageBoxIcon msgTypeIcon = MessageBoxIcon.Error;
			string msgTypeText = "Error: ";
			if(x is PackageExceptionInfo || x is System.Security.SecurityException)