Job doesn't login to linked server

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.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Job doesn't login to linked server

Author

Topic

PeshaShulc
Starting Member

5 Posts

Posted - 2007-11-13 : 12:06:25

My SQL Agent runs under system account
User DOMAIN\COMPUTER_NAME$ is added to users and has role system admin
This user has mapping to "sa" user to the linked server

Job runs procedure which select from remote DB

Error I have:
Executed as user: NT AUTHORITY\SYSTEM. Access to the remote server is denied because no login-mapping exists. [SQLSTATE 42000] (Error 7416). The step failed.

Can somebody help me?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-13 : 23:06:26

Have to start sql agent with domain account.

PeshaShulc
Starting Member

5 Posts

Posted - 2007-11-14 : 03:34:58

quote:
Originally posted by rmiao

Have to start sql agent with domain account.

I can't. There are another applications on that server.
I should work under user of Agent service

PeshaShulc
Starting Member

5 Posts

Posted - 2007-11-14 : 16:25:17

Another interesting think: If I make new login map "any users" -> "sa" remote query will work fine. If I remove mapping "any users" -> sa connection still working for a 20min.
But after some expiration period connection is lost

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-14 : 23:46:31

Map any user to non sa sql login on linked server.

PeshaShulc
Starting Member

5 Posts

Posted - 2007-11-15 : 04:33:38

When I trying to define mapping:
DOMAIN\COMPUTER$ -> sa
sa -> sa
"any user" -> MyPublicUser

My test job fails beacuse of lack of permissions MyPublicUser to some table.
It meens that SQL connects LocalSystem account to MyPublicUser???
But when I define temporary
"any user" -> sa
run job one time. It succeeded.
after that I return mapping "any user" -> MyPublicUser
Link continues to work well.

It seems to me very strange
I have to sove this problem ASAP.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-15 : 23:31:36

Non sa user is not any user, it needs proper permission.

PeshaShulc
Starting Member

5 Posts

Posted - 2007-11-18 : 03:32:48

quote:
Originally posted by rmiao

Non sa user is not any user, it needs proper permission.

I don't want to give high permissions to Non sa user
I want to force SQL server to use first row from logings list (DOMAIN\COMPUTER$ -> sa) But it uses the last one ("any user" -> MyPublicUser)

DOMAIN\COMPUTER$ -> sa
sa -> sa
"any user" -> MyPublicUser

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-18 : 20:55:09

Then pick another sql login.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources