locked
Select Into query to a job Context not trusted RRS feed

  • Question

  • I have a Query that retrieves data from a remote database and puts it into two tables  on my local db.

    Use local
    IF EXISTS(SELECT name FROM [local]..sysobjects WHERE name = N'web_invoice' AND xtype='U')
    	DROP TABLE web_invoice
    GO
    
    SELECT * 
    INTO local.dbo.web_invoice
    FROM server2.Remote.dbo.Web_Invoice
    GO
    
    IF EXISTS(SELECT name FROM [local]..sysobjects WHERE name = N'InvoiceItem' AND xtype='U')
    	DROP TABLE InvoiceItem
    GO
    
    SELECT * 
    INTO local.dbo.InvoiceItem
    FROM server2.remote.dbo.InvoiceItem
    Go
    I want to add this to a job. When attempting that I get a 'Context not secure' error. I have checked the trusworthy bits and all should be fine there. What else should I be looking for in SQL 2005?
    Tuesday, December 22, 2009 6:41 PM

Answers

  • Right click the linked server and look for its property and check with which option the linked server is created.

    Thanks, Leks
    • Marked as answer by C-E-Noles Wednesday, December 23, 2009 2:22 PM
    Tuesday, December 22, 2009 11:05 PM

All replies

  • Was the linked server setup with "Be made using this security context" ?

    When you run a job from a SQL Server Agent job, that running process has the permissions of the execution context configured for the job step. By default this is the SQL Server Agent service account, but it can also be a Proxy account, depending on how the job step was configured.
    If proxy account had not been defined and owner of a job step is not a member of sysadmin role, then the SQL Server Agent service runs in the security context of the Local System account.


    Thanks, Leks
    Tuesday, December 22, 2009 7:47 PM
  • I linked using sp_addlinkedserver 'remoteservername' (or something very much like that. I didn't set any context. Where would I set this?
    Tuesday, December 22, 2009 8:05 PM
  • Right click the linked server and look for its property and check with which option the linked server is created.

    Thanks, Leks
    • Marked as answer by C-E-Noles Wednesday, December 23, 2009 2:22 PM
    Tuesday, December 22, 2009 11:05 PM
  • Hello

    Try linked server configration using sp_addlinkedserver with remote server
    sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ] 
         [ , [ @provider= ] 'provider_name' ]
         [ , [ @datasrc= ] 'data_source' ]
        
    Thanks

    Regards
    RM Thirunavukkarasu
    http://thiruna.blog.com

    Wednesday, December 23, 2009 1:27 PM