Re: Error for a job that uses a MSDASQL linked server

Perhaps you need to setup a credential (CREATE CREDENTIAL) and an Agent Proxy for that login?

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Pasquale" <Pasquale@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:7D7531F7-F234-4930-B8FF-2015D548B1BB@xxxxxxxxxxxxxxxx
I have two servers with SQL Server 2005 Enterprise Edition 64-bit RTM
version, SQL1 (db server) e SQL2 (app server). On SQL2 I have created a SQL
Server linked server
to SQL1.
Moreover, on SQL 2 I have created a SQL Server Agent job with a step
that calls a remote stored procedure saved on SQL1. The owner of the job
is a SQL account, fe mySQLaccount; this login is used to access my db on
SQL1, too.
SQL Server Agent on SQL2 runs as NT AUTHORITY\SYSTEM.
MySQLAccount is the account for the SQL Server linked server to call the
remote stored procedures saved on SQL1.
The remote stored procedure (on SQL1) use a OPENQUERY that uses a MSDASQL
linked server, created on SQL1.
When I execute the job I have had this error for the step that calls the
remote stored procedure with the OPENQUERY:
executed as user NT AUTHORITY\SYSTEM. Cannot execute the query ... against
OLE DB Provider ... "MSDASQL" for linked server ... [SQL State 42000](Error
7320). The Ole DB Provider "MSDASQL" for linked server reported an error. The
provider did not give any information about the error. [SQL State
42000](Error 7399). The step failed.
This problem doesn't occur when for mySQLAccount assign the sysadmin as
a server role.
I have tried to reproduce the error onto a test environment (with two SQL
Server 2005 Enterprise Edition but with SP2) and the step that calls the
remote stored procedure functions normally also with Public and dbcreator as
server roles without
selecting sysadmin for mySQLAccount.
Any helps to solve this issue? Why this different behaviour?
Many thanks

.

Relevant Pages

  • RE: Fulltext failure on a 2 node cluster
    ... Server full-text search resource online: "SQL Cluster Resource 'Full Text' ...
    (microsoft.public.sqlserver.clustering)
  • Re: HELP PLEASE ~ ???
    ... You mentioned that it went ahead and added a SQL ... SQL Server 2000 database for all my data. ... find the connectionString in the newly recreated SQLExpress database. ... The connection string specifies a local Sql Server Express instance ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Multi-Channel Raid VS SAN Storage
    ... A 5~6 years old server is a very old server. ... As I mentioned, the server is one node in a cluster environment, and SQL is ... We actually are running RAID 1+0 and our aplication is definately more ... needs it's own SAN device, or at least a dedicated IO channel on the SAN. ...
    (microsoft.public.sqlserver.setup)
  • Re: Multi-Channel Raid VS SAN Storage
    ... A 5~6 years old server is a very old server. ... As I mentioned, the server is one node in a cluster environment, and SQL is ... We actually are running RAID 1+0 and our aplication is definately more ... needs it's own SAN device, or at least a dedicated IO channel on the SAN. ...
    (microsoft.public.sqlserver.setup)
  • RE: How do I get back to base SQL server on my SBS 2003 server?
    ... After you uninstall SQL 2005, the instance which you upgraded will not go ... Uninstall and then reinstall ISA server. ... Uninstall and then reinstall the Monitoring component. ... Tools to Maintenance, change Monitoring component to Install, and then ...
    (microsoft.public.windows.server.sbs)
Loading