December 9, 2002 at 12:58 pm
In my DTS I have a step that first copies over a CSV file from the network to the SQL before the transformation can start.
When I try to call the DTS from my VB app, it fails (No error messages) and I assume it might be something to do with the path accessability for the user when trying to copy over the file. I use the "\\...." method as an absalute path so that I would not have to rely on mappings.
I have tried logging on using the SQL (2000)logon as well as Windows (2000) logon.
I can get to the point to execute the DTS with SQL login but cannot login using the Windows login for the server.
It just saya my login failed for user "Domain\User". I log in via a Terminal Server session to the SQL Server in order to gain access to SQL on it, with a different domain, username and password than my normal login from my PC.
How can I logon to the DTS using the Windows logon used in the Terminal Server session that allowed me to create the DTS and on which the copy works, - or - How can I log on to the DTS using the SQL login and then allow the DTS Copy to find the path ?
Your help will be greatly appreciated.
Graham
December 9, 2002 at 1:35 pm
The VB program should be running under the context of the user who executes it. This is the account that needs to execute DTS and log into SQL Server, and read the package from msdb.
Steve Jones
December 9, 2002 at 2:02 pm
So what you are saying is that I must log on using the Domain/Username and password of that account I login with for the Terminal Services session ?
December 9, 2002 at 9:38 pm
I've done some work with VB and DTS - within your VB code, specify your SQL database, and your SQL user name and password (or NT authentication if your security is setup this way).
If you are able to successfully execute the DTS package through SQL Server MMC (right-click and Execute), then that account can be used; however, in order to get the package into VB
eg.
oPkg.LoadFromSQLServer sServer, sUsername, sPassword, DTSSQLStgFlag_UseTrustedConnection, , , , sPackageName
you need to read the details from the msdb database. In order to do that, your SQL account must also have security rights to be able read that information from the msdb tables. Be careful here, since the rights need to be very close to administration to access this data, especially in earlier versions of SQL.
see how you go....
December 10, 2002 at 5:15 am
Actually look at your connection object in the DTS package. Odds are you have set to Windows Authentication which no matter how you connect the package is going to try your Windows account. Try changing to a SQL Account that has access to the data and execute. May still run into some issues but that should get past that message.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply