Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jprealini
Starting Member
2 Posts |
 Posted - 2011-10-17 : 11:16:02
|
| Hi… I need a little help, hope someone can give me a hint on this.I am running an SQL 2000 Server with 7 databases, 30 programmed tasks running different DTS Packages every 15 minutes, and Oracle linked servers against which we run queries and inserts.Historically in this company, “sa” user has always been left with no password.Last week we decided to put a bit more security, so we changed every connection string in our applications so each one will use its own login user and password. The goal is to leave “sa” unused by applications, and then finally set a password for it, and use it only for internal server purposes.After succesfully creating logins and changing all connections strings, now I am ready to set a password for sa. I did it first in test environment, and everything worked fine (I had to re-connect the server to Enterprise Manager with sa and password, and then edit the connections inside packages to set sa password, so all programmed tasks will run normally).The problem is that when I did this in production server, even when I set the password for sa in all packages’ connections, the tasks will give error when trying to run (Error: -2147217843 (80040E4D); Provider Error 18456 (4818))Any help will be appreciated/// Last minute update:I kept investigating, and I found several possible causes for the problem, which after being fixed did not solve it. These are the issues I found:1. The user "dbo" (which in testing environment is linked to "sa" login) was orphan in production. I asociated the user in every database to the sa login2. Just for testing, I checked who was the owner of the DTS', and it was my domain user ("DOMAIN/user"). I assigned "sa" as owner for one of the jobs, tested, and the problem is still there. When I assign a password to sa, the job gives the same error (but running the DTS directly from Enterprise Manager gives no error). |
|
|
|
|
|
|
|
SQL 2000 jobs error after changing SA password