Executing a DTS package from VB
in [Visual Basic]
|
Prev: InternetSetFilePointer
Next: MS Calendar Control
From: Carl Imthurn on 3 Mar 2005 08:50 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 0xF3E618F4AA771D65FDA455B0EF6D57299B878D8A403B8A29E7F0AFA6F66F1C1C' -- 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
From: Dan Guzman on 3 Mar 2005 09:11 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. > I found this on SQLDTS.com; is this a better way to go? 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(a)all.com> wrote in message news:%23qi7te$HFHA.3196(a)TK2MSFTNGP15.phx.gbl... > 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 > 0xF3E618F4AA771D65FDA455B0EF6D57299B878D8A403B8A29E7F0AFA6F66F1C1C' > -- 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 >
From: Jeff Johnson [MVP: VB] on 3 Mar 2005 09:29 "Carl Imthurn" <nospam(a)all.com> wrote in message news:%23qi7te$HFHA.3196(a)TK2MSFTNGP15.phx.gbl... > 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 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.
From: Robbe Morris [C# MVP] on 3 Mar 2005 09:33 http://www.eggheadcafe.com/articles/20030923.asp -- 2005 Microsoft MVP C# Robbe Morris http://www.robbemorris.com http://www.learncsharp.net/home/listings.aspx "Carl Imthurn" <nospam(a)all.com> wrote in message news:%23qi7te$HFHA.3196(a)TK2MSFTNGP15.phx.gbl... > 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 > 0xF3E618F4AA771D65FDA455B0EF6D57299B878D8A403B8A29E7F0AFA6F66F1C1C' > -- 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 >
From: Carl Imthurn on 3 Mar 2005 11:34
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: > 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 What is the "SQL Agent proxy"? Do I need to create a Windows account with a very specific username, or can I pick a name myself? > > 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 I looked around in Enterprise Manager and could not find where to do this. > > 4) grant the proxy account any permissions needed by your DTS package > 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 |