Run time jobs

A job is created to fetch data from source server and populate the
destination server. This job has the owner as windows authentication(user
account). This main job inturn creates sub jobs with owner as sa.
when i run the sub jobs from query analyser it works fine. But when ran from
sql server agent/jobs it is failing with the following error.
Msg 18456, Sev 14: Login failed for user 'CHNDomain\CHNSHL23456$'. [SQLS
TATE
28000]
Msg 7312, Sev 14: [SQLSTATE 01000].
[Note: CHNSHL23456$ is the destination system number]
when same job ran from query analyser that was logged using 'sa' account
fails with the following error.
Server: Msg 18456, Level 14, State 1, Line 1
Login failed for user 'sa'.
[OLE/DB provider returned message: Invalid connection string attribute]
The windows authenticated user ID exists in both source server/destination
server users list.
Pls hlp to get rid of this error.
Regards,
SSKSSK
Does the login have full pemissions? What is an account SQL Server Agent run
under?
"SSK" <SSK@.discussions.microsoft.com> wrote in message
news:CC9BF3BD-5394-40B1-A8E2-7D1DAC73BBA2@.microsoft.com...
>A job is created to fetch data from source server and populate the
> destination server. This job has the owner as windows authentication(user
> account). This main job inturn creates sub jobs with owner as sa.
> when i run the sub jobs from query analyser it works fine. But when ran
> from
> sql server agent/jobs it is failing with the following error.
> Msg 18456, Sev 14: Login failed for user 'CHNDomain\CHNSHL23456$'.
> [SQLSTATE
> 28000]
> Msg 7312, Sev 14: [SQLSTATE 01000].
> [Note: CHNSHL23456$ is the destination system number]
> when same job ran from query analyser that was logged using 'sa' account
> fails with the following error.
> Server: Msg 18456, Level 14, State 1, Line 1
> Login failed for user 'sa'.
> [OLE/DB provider returned message: Invalid connection string attribute
]
> The windows authenticated user ID exists in both source server/destination
> server users list.
> Pls hlp to get rid of this error.
> Regards,
> SSK
>|||What account is the SQL Agent service running under ? Jobs owned by sa will
access resources under the context of the SQL Agent service account.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"SSK" <SSK@.discussions.microsoft.com> wrote in message
news:CC9BF3BD-5394-40B1-A8E2-7D1DAC73BBA2@.microsoft.com...
>A job is created to fetch data from source server and populate the
> destination server. This job has the owner as windows authentication(user
> account). This main job inturn creates sub jobs with owner as sa.
> when i run the sub jobs from query analyser it works fine. But when ran
> from
> sql server agent/jobs it is failing with the following error.
> Msg 18456, Sev 14: Login failed for user 'CHNDomain\CHNSHL23456$'.
> [SQLSTATE
> 28000]
> Msg 7312, Sev 14: [SQLSTATE 01000].
> [Note: CHNSHL23456$ is the destination system number]
> when same job ran from query analyser that was logged using 'sa' account
> fails with the following error.
> Server: Msg 18456, Level 14, State 1, Line 1
> Login failed for user 'sa'.
> [OLE/DB provider returned message: Invalid connection string attribute
]
> The windows authenticated user ID exists in both source server/destination
> server users list.
> Pls hlp to get rid of this error.
> Regards,
> SSK
>|||The SQL server agent is started by Local System account.
The login account has admin privileges on source and destination server als
o.
"Jasper Smith" wrote:

> What account is the SQL Agent service running under ? Jobs owned by sa wil
l
> access resources under the context of the SQL Agent service account.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "SSK" <SSK@.discussions.microsoft.com> wrote in message
> news:CC9BF3BD-5394-40B1-A8E2-7D1DAC73BBA2@.microsoft.com...
>
>|||Local System does not have access to network resources. If
you are accessing other servers, you need to use a domain
account for the service with the appropriate permissions.
-Sue
On Wed, 24 Aug 2005 23:33:09 -0700, SSK
<SSK@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>The SQL server agent is started by Local System account.
>The login account has admin privileges on source and destination server al
so.
>"Jasper Smith" wrote:
>