My apologies for cross-posting, but I'm not sure which newsgroup this belongs in.

I am attempting to execute a DTS package from Visual Basic, and I'm encountering a problem.
In a nutshell, how does a low-level user (from a security standpoint) execute a DTS package?

' here's the relevant part of the VB procedure:

Dim pConnectionString As String
pConnectionString = "Provider=SQLOLEDB.1;Data Source=xyzzy;Initial
Catalog=Trident;User ID=Troll;PWD=plugh"
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.ConnectionTimeout = 10
conn.ConnectionString = pConnectionString
conn.CommandTimeout = 30
conn.Open

Dim comm As ADODB.Command
Set comm = New ADODB.Command
comm.CommandType = adCmdText
comm.ActiveConnection = conn
comm.CommandText = "EXEC AcademicAdmin..s_Test"
comm.Execute


-- here's the stored procedure being executed
CREATE PROCEDURE s_Test
AS

if exists (select * from sysobjects where id = object_id(N'[dbo].[dentrev1]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table dbo.dentrev1

exec master..xp_cmdshell 'DTSRun /~S 0x4AE5E2A4783E358A718ED290354476AC /~U
0x04382A57E6267DBDFD1F9877D7A9BC7B /~P 0x88FFF4913468C337CAE4A98BA18F0319 /~N
0xF3E618F4AA771D65FDA455B0EF6D57299B878D8A403B8A29 E7F0AFA6F66F1C1C'
-- END OF STORED PROCEDURE


When I run the VB code (which logs in to SQL Server as a low-level user), it runs until it
executes the comm.Execute line and the following error message appears:

Run-time error '-2147217911 (80040e09)':
EXECUTE permission denied on object 'xp_cmdshell', database 'master', owner 'dbo'

However, when I add this user to the SQL Server role System Administrators, the VB
procedure works fine.

What are my options at this point?
How do I get around the xp_cmdshell issue?
This user cannot be added to any of the administrator-level roles in SQL Server.

I found this on SQLDTS.com; is this a better way to go?

' simplified version
Private Sub SimpleExecutePackage()
Dim oPKG As New DTS.Package
oPKG.LoadFromSQLServer "MyServer", , , _
DTSSQLStgFlag_UseTrustedConnection, , , , "MyPackage"
oPKG.Execute
oPKG.UnInitialize
Set oPKG = Nothing
End Sub

Thanks in advance --

Carl

By default, only sysadmin role members can execute xp_cmdshell. For
non-sysadmin users to execute xp_cmdshell, you'll need to configure the
proxy account using the procedure below.

1) create a Windows account for the SQL Agent proxy

2) uncheck the 'only users with sysadmin ...' checkbox in Enterprise
manager under SQL Agent properties/Job system.

3) specify the proxy account as the Windows account created in step 1

4) grant the proxy account any permissions needed by your DTS package

Don't grant direct execute permissions to xp_cmdshell. With SQL 2000 SP3+,
you can change your user database owner to 'sa' and enable cross-database
chaining (EXEC sp_dboption 'MyDatabase', 'db chaining', true) so that direct
execute permissions on xp_cmdshell are not needed when executing your proc.
Users only need permissions to execute your user stored procedure.

Note that you should enable cross-database chaining in an sa-owned database
when only sysadmin role members can create dbo-owned objects in that
database.

The best approach may be to execute the package directly from your app
rather than from SQL Server. Of course, this depends on what your package
is doing.


--
Hope this helps.

Dan Guzman
SQL Server MVP

"Carl Imthurn" <nospam (AT) all (DOT) com> wrote

"Carl Imthurn" <nospam (AT) all (DOT) com> wrote


Yes, using the DTS libraries is definitely (okay, PROBABLY) the better way
to go, but bear in mind that using DTS directly from your VB program means
that the DTS package will execute on the CLIENT machine whereas with your
stored procedure it is executing from the SERVER. This has a huge impact if
local drives are being accessed anywhere in the package.

Hi Dan --

I have more experience on the SQL Server programming side of things than administration,
so I have a few (hopefully not too) dumb questions for you. See inline.

Dan Guzman wrote:

username, or can I pick a name myself?
By the way, I'm running SQL Server 7 SP3 -- my apologies for not specifying that in the
original post.
Thanks for your time Dan -- very much appreciated.

Carl

This is what I use from VB.NET. I have an app that imports and exports
Excel Spreadsheet for a user to analyze. The downside is that you have to
register a bunch of SQL Server DLLs on the client PC. You also have to add
'Microsoft.DTSPackage Object Library' (COM reference) as a reference to
your project.

Dim oPKG as New DTS.Package
'Set Parameter Values
Dim sServer as String = "YourServerName"
Dim sUsername as String = "YourUserName"
Dim sPassword as String = "YourPWD"
Dim sPackageName as String = "YourDTSPackageName
'Load Package
oPKG.LoadFromSQLServer(sServer, SUsername, sPassword, _
DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default , , , , sPackageName)
oPKG.Execute()
oPKG.UnInitializer()
oPKG = Nothing

Here is where you can find the SQL Server DLLs to register on the client PC.
http://www.sqldts.com/Default.aspx?225 This site tells you where to find
them and which ones to register.

Hope this helps!

Chuck Foster

"Robbe Morris [C# MVP]" <info (AT) turnkeytools (DOT) com> wrote

I assumed you were running the latest version, SQL Server 2000. The proxy
account is not configurable in SQL 7 and is the local SQLAgentCmdExec
account that was created when you installed SQL Server. It is this account
that will need to be granted permissions on resources needed by your DTS
package.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Carl Imthurn" <nospam (AT) all (DOT) com> wrote

Thanks Dan -- this gets me headed in the right direction.
I appreciate your time.

Carl

Dan Guzman wrote:

Thanks folks -- I appreciate all your input and in particular your time.
I'm not sure which direction I'll be heading right now, but seeing the different ways to
accomplish my objective helps greatly.

Carl