| Author |
Topic |
|
porcrim
Starting Member
13 Posts |
 Posted - 2004-01-21 : 21:10:07
|
| I have some DTS packages that I am executing from some VB Code. Within the same VB module I am calling some stored procedures using the command object. For both cases I am using SQL authentication and provide the proper username and password in the code. The DTS packages run fine but when I try to run the stored procedures I get an error that "Login failed for user 'sa'". Why do I get this error when I am supplying a valid username other than 'sa'?Thanks,Matt |
|
|
rihardh
Constraint Violating Yak Guru
307 Posts |
Posted - 2004-01-22 : 02:43:29
|
| Check your connection string again (happens to me all the time). |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-01-22 : 12:45:32
|
| It is because you have saved the sa account and password inside the DTS package. It is not because of the connection string. Edit the DTS package and save the connections with an account other than sa. Use an account where the password and permissions do not change.Tara |
|
|
|
porcrim
Starting Member
13 Posts |
Posted - 2004-01-22 : 13:25:34
|
| I created a new login (DTSUser) and assigned the connections in the DTS packages to that login. I still get the "login failed for SA" error" when I excecute it from VB. What am I doing wrong? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-01-22 : 13:53:01
|
| The DTS Package also needs to be Saved As a different account. Go to the save as inside the DTS designer.Tara |
|
|
|
porcrim
Starting Member
13 Posts |
Posted - 2004-01-22 : 14:27:24
|
| Tara,Thanks for your help but I'm still getting the error. I deleted the old packages and re-created them new. I made sure that the SQL username on the connection property as well as the package save were set to my DTSUser login. There should no longer be a trace of the "sa" login anywhere but that error still fires.Matt |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-01-22 : 14:30:30
|
| Do you get login failed for sa when you execute the DTS Package through Enterprise Manager?Tara |
|
|
|
porcrim
Starting Member
13 Posts |
Posted - 2004-01-22 : 14:44:21
|
| No, they run fine from Enterprise Manager |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-01-22 : 14:47:01
|
| Ah, then I am mistaken. Check your connection string then as this the sa account is being passed by your application.Tara |
|
|
|
porcrim
Starting Member
13 Posts |
Posted - 2004-01-22 : 16:07:30
|
| Tara, I think I figured out what the problem is. I ran the same VB procedure against another server that has the same DTS packages and it worked fine. The only thing I changed is the SERVER attribute of my connection string. Since it worked on that server I figured the connection string was fine and I looked at the server properties for each. The server that it works on has the Startup Service account set to use a defined domain\username. The problem server has that setting set to use the system account. When I try to make the change I get "A required privilege is not held by the client" |
|
|
|
|
Code Wants to Run Under SA