Vista hosting XPe tools/db

  • Thread starter Thread starter DPM
  • Start date Start date
D

DPM

This is Vista security preventing your account from connecting to the
database. Try logging in as the Administrator (not just a user in the
admin group). Disabling LUA would probably also enable that but i'm not
proposing you do that since that feature is enabled for a reason.

We need to propagate some information to the community wrt hosting XPe
on Vista, it's definitely supported but since the OS is so locked down,
the process of enabling the end to end developer scenario on Vista is no
longer as simple as it was on previous OS'. I believe someone may be
working on a stored procedure for the database to set the permissions
such that Vista will allow authorized users access to the DB without
having to tweak the OS.

I'll ping the PMs and try to get something on our blog next week or asap.
 
Hi there. We did some research into this issue and have come up with what
we believe is a decent workaround. Here is a short VBScript that you can
run (as Administrator) on your machine, that should grant the proper
permissions to your SQL database in order for your tools to connect properly
in Vista.

To use this script, copy the following script code to a file and save it as
"savesqlperms.vbs" . Then run the script as Administrator on the machine
hosting the database.

=== BEGIN SCRIPT CODE ===
Option Explicit

WScript.StdOut.WriteLine "SetSQLPerms.vbs: Grants sysadmin permissions to a
specified user on a SQL server."
If WScript.Arguments.Count < 2 Then
WScript.StdOut.WriteLine "Usage: setsqlperms.vbs ComputerName UserName"
WScript.Quit()
End If

Dim g_oSQLServer : Set g_oSQLServer = CreateObject("SQLDMO.SQLServer")
g_oSQLServer.LoginTimeout = 30
g_oSQLServer.LoginSecure = True
g_oSQLServer.Connect Trim(WScript.Arguments(0))
g_oSQLServer.ExecuteImmediate("EXEC sp_grantlogin '" &
Trim(WScript.Arguments(1)) & "'")
g_oSQLServer.ExecuteImmediate("EXEC sp_addsrvrolemember '" &
Trim(WScript.Arguments(1)) & "', 'sysadmin'")
g_oSQLServer.Close
Set g_oSQLServer = Nothing

WScript.StdOut.WriteLine "Done."
=== END SCRIPT CODE ===

The usage is as follows: setsqlperms.vbs ComputerName UserName
For example: setsqlperms MyComputer MYDOMAIN\Matt

Once you run this script, you should be able to access your database
properly without having to disable UAC or login as Administrator.

Please let us know how this works for you. :)
 
Just a clarification: This script requires that you run it under the CScript
host. If you haven't changed your default script host to CScript, you can
do that with the following command: cscript //H:CScript

Alternatively, you can run setsqlperms.vbs script as follows: cscript
setsqlperms.vbs ComputerName UserName
 
Matt,

I chose your second option, and here's what I got:

C:\XPe>setp

C:\XPe>cscript setsqlperms.vbs vista-64 dpm
Microsoft (R) Windows Script Host Version 5.7
Copyright (C) Microsoft Corporation. All rights reserved.

SetSQLPerms.vbs: Grants sysadmin permissions to a specified user on a SQL
server
..
C:\XPe\setsqlperms.vbs(9, 20) Microsoft VBScript runtime error: ActiveX
component can't create object: 'SQLDMO.SQLServer'

C:\XPe>

Now, the SQL server instance name is "MSSQLSERVER" Should the object be
"'SQLDMO.MSSQLSERVER'"?

Regards,
Dean
 
Nope. The instance name on the machine you're accessing shouldn't matter.

Are you running this script on the machine that actually has the SQL server
installed? The SQL DMO is an object that should exist on a machine that has
some form of SQL Server installed - if it doesn't, this script won't be able
to find the components necessary to perform its tasks.
 
Matt,

Yes - I'm running the script on a machine that has SQL 2005 Express SP1
installed and running, logged in as the "Administrator".

I attached the script I'm running, just to make sure.

Regards,
Dean
 
Huh. I'll do a little more research and see if there's some component that
SQL Express isn't installing that should be there. The error you're getting
indicates that the DMO isn't available - perhaps it's not included with SQL
Express (for whatever reason). I may need to rewrite the script to take
advantage of a different mechanism - interfacing with the built-in ODBC
support isn't as easy.
 
Okay, here's an alternative script using the Microsoft ADO (ADODB) as
opposed to the SQL DMO - this should (hopefully) allow this script to work
on your machine. I apologize for the confusion - please let me know how it
works for you. Thank you for your patience. :) (Also, I moved the
introductory line stating what the script does such that the script only
describes itself if you don't provide enough parameters.)

=== BEGIN SCRIPT CODE ===
Option Explicit

If WScript.Arguments.Count < 2 Then
WScript.StdOut.WriteLine "SetSQLPerms.vbs: Grants sysadmin permissions
to a specified user in a SQL database."
WScript.StdOut.WriteLine "Usage: setsqlperms.vbs ComputerName UserName"
WScript.Quit()
End If

Dim g_oSQLServer : Set g_oSQLServer = CreateObject("ADODB.Connection")
g_oSQLServer.Open "Provider='sqloledb';Data Source=" &
Trim(WScript.Arguments(0)) & ";Integrated Security='SSPI';"
g_oSQLServer.Execute("EXEC sp_grantlogin '" & Trim(WScript.Arguments(1)) &
"'")
g_oSQLServer.Execute("EXEC sp_addsrvrolemember '" &
Trim(WScript.Arguments(1)) & "', 'sysadmin'")
g_oSQLServer.Close
Set g_oSQLServer = Nothing

WScript.StdOut.WriteLine "Done."
=== END SCRIPT CODE ===
 
Matt,

This ADO script works. Note that the script (or probably the underlying ADO
infrastructure) requires "UserName" to be in the form "Domain\UserName".

Thanks for your efforts.

Regards,
Dean
 
Back
Top