DTS Permissions from VB program

[Ajb2528]

Hi!!

I am trying to run a DTS Package from a VB front-end. This runs fine on our development server (I have sysadmin rights) but fails with the following error on the production server:

Step 'DTSStep_DTSExecuteSQLTask_1' failed

Step Error Source: Microsoft OLE DB Provider for SQL Server
Step Error Description:[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:0

What actual rights do I need to get this running on the production server?

P.S. we are using integrated security.

Regards,

Alan

 

[AlexCuse]

Is the domain account you are testing with given access to you production server through integrated security?

If so,

What is the code you are using to execute your DTS? There are a couple different methods.

We use integrated security as well, but I usually pass the login info in my run commands anyway in case someone is logged on with a different domain account and needs to use the program. It has yet to happen, but ya never know ;-)

Good Luck,

Alex


It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[Ajb2528]

AlexCuse,

Thanks for the reply. The domain account only has (I think) read access to the SQL Server. For example I cannot recompile any views etc. The code that I am using is:

Dim objPackage As DTS.Package2

Set objPackage = New DTS.Package2

'Set the server/username/password and the DTS package to be run
objPackage.LoadFromSQLServer "LONANTDAT3E2E", "", "", DTSSQLStgFlag_UseTrustedConnection, "", "", "", "CSV102 All Syndicates"

'Set object to raise an error on failure and to do eventlogs
objPackage.FailOnError = True
objPackage.WriteCompletionStatusToNTEventLog = True

'Run DTS Package
objPackage.Execute

objPackage.UnInitialize

Set objPackage = Nothing

Regards,

Alan

 

[AlexCuse]

Hmmm. As long as you've got the server and package names right the code looks solid. I would first try

objDTS.LoadFromSQLServer "[i]LONANTDAT3E2E[/i]", "[i]UserName[/i]", "[i]Password[/i]", _
[b]DTSSQLStgFlag_Default[/b], , , , "[i]CSV102 All Syndicates[/i]"

Curiously I've never 'trusted' the trusted connection.

It does save me a lot of password typing though

If using the standard method doesn't work, I would check with your Admin to make sure that your domain account has access to the production server.

Hope this helps,

Alex

It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.