| Author |
Topic |
|
jesus4u
Posting Yak Master
204 Posts |
 Posted - 2003-10-14 : 08:45:32
|
| I am finished with my project and all I need is to figure out this error so please help.We have 2 servers. One webserver with sql2000 at a remote location accessed thru VPN and another on the local network with same configuration.On the local machine I scheduled a package to upload TableA to the remote sql server every hour. BUT when the job runs it FAILS because it says that:"Error string: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 18452 (4814) Error string: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. "Now I know that this means that the remote server it is connecting to is rejecting my login. But this job is executing a package on thje LOCAL machine that in turn is uploading data to the remote machine. The package runs successfully without the schedule but not with a schedule.Can anyone help me out please?ThanksAlex Polajenko |
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2003-10-14 : 09:25:38
|
| We had a similar incident and came to learn that it was because the local machine did not have rights to itself. I know that it seems ridiculous, but we had to grant Read/Write to the SYSTEM and then it worked. We have found this problem on Win2000 and XP boxes.Hope this helps a little. (I was not there when they actually set the permissions, so I couldn't tell you what they did exactly.)Aj |
 |
|
|
jesus4u
Posting Yak Master
204 Posts |
Posted - 2003-10-14 : 09:37:22
|
| I don't understand that because you are saying that on our Production box I have to enable the System account to have write access? The error seems to be denying the local machine account from executing against the remote sql server.Alex Polajenko |
|
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2003-10-17 : 17:42:22
|
| I know, it is crazy, but the System user has to have acccess to itself to execute. Take a look at the permissions. Like I said, it may not fix the problem, but it is something I have seen before.Aj |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-17 : 18:04:33
|
| The problem is that the job is using the account that the SQLSERVERAGENT is configured for to access the remote server. Does this account have access to the remote server? If not, you need to grant it access.The reason why it works outside of the schedule is because when you do it this way it is using your account (or whoever is signed onto that machine).Tara |
|
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2003-10-20 : 08:05:37
|
| Tara, You know where SQLSERVERAGENT startup account is "System Account" , How does one find out which account this is.Background: the SQL Server was moved from an NT4 DOMAIN to a new Win2000 DOMAIN. It is a member server now still NT4. I know that the prefered method is to have both MSSQL service and SQLAGENT service start up with domain accout as you have said before. This goes along way to smoothing out job failures caused by permissions. How do I convince the authorities here that we should switch it?Voted best SQL forum nickname...."Tutorial-D" |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-10-20 : 08:19:16
|
If you go into the Services interface in the Administrative Tools, open up the SQLSERVERAGENT item by double clicking on it. You can look at, or change the account by selecting the "Log On" tab.As for convincing management, well... it's a good idea, so you might be in for a fight  Damian |
|
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2003-10-20 : 08:46:23
|
| Thanks Merkin, that's funny....Yea I go in there and see it is set as system accountMSSQLSERVER startup system accountSQLSERVERAGENT startup system accountThe local machine account SQLCMDEXEC (sp?) still exists.But there is no SYSTEM acoount as in a specific account called "SYSTEM"So really I don't know what specific accounts these two services are running under at all. It would be nice to know before I change 'em. Voted best SQL forum nickname...."Tutorial-D" |
|
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2003-10-20 : 08:50:30
|
| ..oh yea and to be more graphic.In the sysadmin role on the SQL server is saBUILTIN/AdministratorsadminAdministratorsAdministratorOldDOMAIN/AdministratorDomain1/AdministratorDomain2/Administratorxxxadministraoryyyadminso you see the mess it is. I don't know where all this junk came from,sad really.Voted best SQL forum nickname...."Tutorial-D" |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-10-20 : 08:53:34
|
| HiLook up "local system account" in BOL, there is a good bit of info there.Damian |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-20 : 12:10:45
|
| Sitka, you need to change both services so that they use a domain account, and one that has local admin privileges. This is highly recommended so that you don't run into permission problems. I would create an account such as sqladmin on the domain then add this account to the local admin group. Then modify both services to use this account instead of the local system account. Using the local system account is just not recommended.Tara |
|
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2003-10-20 : 14:37:00
|
| Thanks Tara and Merkin, I think I get most of it. Read some BOL etc. and all things are prettystraight forward it's just a need to communicate the errors in the current setup which is always hard todo when a plug and pray legacy has it running fine in a limited capacity. I fall way short when trying tocommunicate why something isn't right or as good as it needs to be.To what end in this case......Jobs requiring network access will run like log shipping!!Voted best SQL forum nickname...."Tutorial-D" |
|
|
|
|
Jobs and Packages and Logins Failing