| Author |
 Topic Search  Topic Options
|
Gary 
Senior Member
Joined: 20 March 2002
Location: United Kingdom
Status: Offline
Points: 326
|
 Post Options
 Thanks(0)
 Quote  Reply
 Posted: 05 November 2007 at 4:40pm |
Here's the code that I am using (excluding all presentation and error handling)....
sSQL_Server_Name = "UKSLBA52"
sDTS = "Comm_Edu_Import"
CALL RunDTS(sDTS)
' *** RUN DTS PACKAGE ***
FUNCTION RunDTS(sDTS)
Const DTSSQLStgFlag_Default = 0
Const DTSStepExecResult_Failure = 1
Dim oPkg, oStep, sMessage, bStatus
Set oPkg = Server.CreateObject("DTS.Package")
oPkg.LoadFromSQLServer sSQL_Server_Name, "sa","password",DTSSQLStgFlag_Default,"","","",sDTS
oPkg.Execute()
END FUNCTION
The DTS package basically has a SQL Connection to the DB itself, a SQL Task that deletes data from TableX, an Excel connection and a Data Pump that imports data from Excel to TableX
Edited by Gary - 05 November 2007 at 4:41pm
|
 |
socialanimal
Newbie
Joined: 14 October 2007
Location: Leeds, UK
Status: Offline
Points: 39
|
Post Options
Thanks(0)
Quote Reply
Posted: 05 November 2007 at 4:54pm |
|
As the "sa" / "password" combo is a default SQL server authentication method, is this what your actually using in your script ?
|
|
Gary
Senior Member
Joined: 20 March 2002
Location: United Kingdom
Status: Offline
Points: 326
|
Post Options
Thanks(0)
Quote Reply
Posted: 05 November 2007 at 5:06pm |
Yes I am using the sa account and the associated password (which incidentally isn't "password").
The asp script is using SQL Server authentication and the SQL connection within the DTS package is also using the SQL Server authentication.
Should I be using a domian account to connect the asp to SQL and if so, how do I go about doing that?
|
|
socialanimal
Newbie
Joined: 14 October 2007
Location: Leeds, UK
Status: Offline
Points: 39
|
Post Options
Thanks(0)
Quote Reply
Posted: 05 November 2007 at 5:13pm |
|
No it should be using the SQL server account so thats correct, its the permissions in the script thats failing for some reason. Could you try replacing the DTSSQLStgFlag with the following -
DTSSQLStgFlag_UseTrustedConnection and see what the results are, if any?
|
|
Gary
Senior Member
Joined: 20 March 2002
Location: United Kingdom
Status: Offline
Points: 326
|
Post Options
Thanks(0)
Quote Reply
Posted: 05 November 2007 at 5:39pm |
OK, I get the same error when I change the script to use:
oPkg.LoadFromSQLServer sSQL_Server_Name, "sa", "password", DTSSQLStgFlag_UseTrustedConnection,"","","",sDTS
If I then remove the SQL Account details:
oPkg.LoadFromSQLServer sSQL_Server_Name, "", "", DTSSQLStgFlag_UseTrustedConnection,"","","",sDTS
I get the following:
Microsoft OLE DB Provider for SQL Server error '80040e4d'
Login failed for user 'fanle01'
Now, the strange thing is that user 'fanle01' is an actual domain account,but where is it picking this up from? It certainly is not in any of my scripts, connections, etc. Most strange!
(I haven't checked what permissions 'fanle01' has - should I check, and if so, what should I check - folder/file perms / SQL perms?)
|
|
socialanimal
Newbie
Joined: 14 October 2007
Location: Leeds, UK
Status: Offline
Points: 39
|
Post Options
Thanks(0)
Quote Reply
Posted: 05 November 2007 at 5:49pm |
|
Ok, It looks like we're finally hitting the correct spots with our troubleshooting.
On the PC which is running IIS who is logged on to this machine ?
On the PC which is running SQL 2000 who is logged in to this machine ?
What kind of NT account does fanle01 have ? Is it a domain user / admin ? What kind of permissions do they have ?
Ideally I think we need to be looking at the NT user account which is trying to execute this.
Could you try putting the code into a vbs file and running it from your workstation and log any results ?
|
|
Gary
Senior Member
Joined: 20 March 2002
Location: United Kingdom
Status: Offline
Points: 326
|
Post Options
Thanks(0)
Quote Reply
Posted: 05 November 2007 at 6:03pm |
Both the web and database servers are logged in under a different domain account which is a member of the local admins group on both boxes.
fanle01 is a standard user account on the domain. However it does have access to the folder in whoch the Excel file is located and admin access to the SQL Server installation.
Now I come to think about it - I did get 'fanle01' to log onto my laptop a week or so ago. And I have just had confirmation from the user that he changed his password at the end of last week. Maybe the user / password are somehow cached on my laptop? Thing is though, why is it picking up that user account and not mine that I am currently logged on as?
As far as getting the code into a vbs is concerned, I'm afraid I dont really know how to do that. I saved as a VB .bas file and tried running, but it came up with user defined type errors.
|
|
socialanimal
Newbie
Joined: 14 October 2007
Location: Leeds, UK
Status: Offline
Points: 39
|
Post Options
Thanks(0)
Quote Reply
Posted: 05 November 2007 at 6:08pm |
|
The first post I made on this thread was taken directly from a .vbs file simply copy and paste that in to a text file and save as filename.vbs but replace the response.write with msgbox.
To check if it is using a cached version of fanle01's credentials (though like you i don't see why it should) then try creating a test asp page to write out request.servervariables("AUTH_USER") then at least you can confirm or deny this theory.
Lets go from there and see how we get on.
|
|
