+ Reply to Thread
Results 1 to 10 of 10

Stored procedure doesn't run as a job

  1. Stored procedure doesn't run as a job

    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:

    "Executes as user: DOMAIN\user. Cannot open the table"Table" from the OLE DB
    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?

  2. Re: Stored procedure doesn't run as a job


    "Rod Behr" wrote in message
    news:BF1E9032-B6C5-4CF4-BACE-E58BE2C0BDEB@microsoft.com...
    >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:
    >
    > "Executes as user: DOMAIN\user. Cannot open the table"Table" from the OLE
    > DB
    > 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?


    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



  3. Re: Stored procedure doesn't run as a job

    On Jun 26, 6:58*am, Rod Behr
    wrote:
    > 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:
    >
    > "Executes as user: DOMAIN\user. Cannot open the table"Table" from the OLEDB
    > 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?


    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.

  4. Re: Stored procedure doesn't run as a job

    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:

    "Executed as user: DOMAIN\user. Access to the remote server is denied
    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?

  5. Re: Stored procedure doesn't run as a job

    > I am logged in as DOMAIN\user. The job step (there is only one) is being run
    > as DOMAIN\user,


    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).

    > I interpret this to mean the linked server is not trusted. How do I go about
    > getting SQL Server to trust it?


    Can you post online somewhere a screen shot of the security in the linked
    server's properties?


  6. Re: Stored procedure doesn't run as a job

    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!

  7. Re: Stored procedure doesn't run as a job


    "Rod Behr" wrote in message
    news:4389225A-A13D-4A86-81ED-7EC4F3830592@microsoft.com...
    > 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:
    >
    > "Executed as user: DOMAIN\user. Access to the remote server is denied
    > 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?


    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




  8. Re: Stored procedure doesn't run as a job

    > 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.


    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.



  9. Re: Stored procedure doesn't run as a job

    On Jun 26, 10:15*am, Rod Behr
    wrote:
    > 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!


    You probably have SQL Server and SQL Agent running as "Local System",
    and the linked server configured to use "current authentication".
    "Local System" doesn't map to anything, thus it's not trusted. I
    would suggest creating a real domain login for SQL Server to use, and
    configure both Agent and Server to run as that login.

  10. Re: Stored procedure doesn't run as a job

    > 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).


    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.



+ Reply to Thread