SQL Server - Stored procedure doesn't run as a job

Asked By RodBeh on 26-Jun-08 07:58 AM
A stored procedure which performs an insert from an MS Access table (itself
linked to a Pervasive database) and then performs a series of update queries
to sanitise the data runs fine when invoked from a query window.

In order to regularly run this stored procedure on a schedule, it is set up
as a one-step job. The job fails, returning a message:

provider "Microsoft.Jet.OLEDB.4.0" for linked service "(null"). The specified
table or view does not exist or contains errors. [SQLSTATE 42000] (Error
7306). The step failed."

The query which appears to be generating the error is an insert query:

INSERT INTO Table (field1, field2,...)
SELECT field1, field2...
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', '[path]'; '[user]'; '[password]',
Table) As Alias

As I said, this query runs fine when invoked from a query window, but
returns the above error when run as a one-step job. DOMAIN\user has full
administrative rights.

What is the difference between a query window and a job that would cause this?




Rick Sawtell replied on 26-Jun-08 08:46 AM
Where does DOMAIN\User have full admin rights?  In the database as well the
O/S, or just the O/S.

It looks like a permissions issue to me.   When running as a job, it not
necessarily execute in the same context as what you are logged in as.   Try
logging in as DOMAIN\User, connecting to SQL Server and running your query
through Query Analyzer.  ;-)

The question is whether or not this is an INSERT permission, or a OPENROWSET
permission to the file in the path statement.


Rick Sawtell
Tracy McKibben (http://www.realsqlguy.com) replied on 27-Jun-08 06:24 AM
On Jun 26, 6:58=A0am, Rod Behr <RodB...@>
lf
ies
up
DB
fied
]',
this?

This is a permissions issue.  When run as a SQL job, the query runs as
the SQL Server service account.  When run in a query window, it runs
as whoever is running Query Analyzer/Management Studio.  You need to
make sure the service account has the necessary permissions to the
Access data and/or file location.
RodBeh replied on 26-Jun-08 10:59 AM
Thanks, Rick, but let me rephrase (now that I have spent more of my afternoon
on this one):

In order to bring these external .mdb data sources "into the fold", so to
speak, I have set them up as linked servers. I am able to browse the tables
and queries in these databases using SQL Server Management Studio, so long as
I do so manually.

Take the query:

SELECT * FROM LinkedServer...LinkedTable

They don't get much simpler than that. This works happily from within a
query window but not from a job.

I am logged in as DOMAIN\user. The job step (there is only one) is being run
as DOMAIN\user, inasmuch as DOMAIN\user is set up as a database login with
every permission possible (I know... once I crack this I'll throttle back)
and mapped to dbo of the database concerned.

The error I get is now slightly different:

because the current security context is not trusted. [SQLSTATE 42000] (Error
15274).  The step failed."

I interpret this to mean the linked server is not trusted. How do I go about
getting SQL Server to trust it?
Aaron Bertrand [SQL Server MVP] replied on 26-Jun-08 11:08 AM
Great, but what account is SQL Server Agent running as?  Job owner is not
the last handshake.  And their permissions inside a database in SQL Server
does not help some remote file share determine whether that user can access
a file.  This is like saying the Pope should be able to drive my car because
he's holding car keys (even though they're not for *my* car).


Can you post online somewhere a screen shot of the security in the linked
server's properties?
RodBeh replied on 26-Jun-08 11:15 AM
Thanks, Tracy. Please see my reply to Rick.

In reply to your suggestion, though, NT AUTHORITY\SYSTEM is set up as a
login, given full permissions and mapped to a user of the same name on the
database. It is a member of every server and database role. Still I get the
permissions thing.

It appears to be a trust issue with the data source, although this may also
have something to do with permissions.

Ideas welcome!
Aaron Bertrand [SQL Server MVP] replied on 26-Jun-08 11:22 AM
NT_AUTHORITY\SYSTEM is a *LOCAL* user.

If your file is on another server, there is not really a way for that server
to validate the permissions of some user that is effectively a member of
another domain.

So I suggest setting up the service account (or a proxy account) using a
domain account that can access both servers.  Or, push a copy of this
database to somewhere that the SQL Server CAN access.

And again, fixing permissions / roles etc. within a database is not going to
do squat to fix this problem.  File access outside of SQL Server is
completely unrelated to that user's ability to read/write data within a
database.
Tracy McKibben (http://www.realsqlguy.com) replied on 27-Jun-08 06:24 AM
On Jun 26, 10:15=A0am, Rod Behr <RodB...@>
e
he
so

You probably have SQL Server and SQL Agent running as "Local System",
and the linked server configured to use "current authentication".
would suggest creating a real domain login for SQL Server to use, and
configure both Agent and Server to run as that login.
Rick Sawtell replied on 26-Jun-08 11:16 AM
Still permissions issues.   Check that the following has been done:

1.  SQL Server must be running under a domain account with Trust for
Delegation in AD turned on.
2.  SQL Server Service account needs modify access on the folder where the
.mdb file is stored (Access creates a locking .ldb file when accessed).

HTH

Rick Sawtell
Aaron Bertrand [SQL Server MVP] replied on 26-Jun-08 02:17 PM
And also that this is not being referenced as a mapped drive letter that was
created under your normal login or a local user... since these are account
specific.