SSIS Windows Auth failure RRS feed

  • Question

  • I'm creating a process in SSIS that pulls data from a database on a linked server to my local SQL Server.  The linked server has windows authentication.  I wrote the process in Visual Studio and can execute it successfully from within the IDE.  I set up a job within the SQL Server Agent so I can schedule it to run on a daily basis.  I know the SQL Server Agent uses a different id to run the job so I created credentials that mimic my id and then created a proxy that uses those credentials.  When creating the job in SQL Server Agent, I set it to use my proxy.  I thought this would get me through the Windows Authentication on the linked server.

    When I run the job, it fails immediately with the message:

    The job failed.  Unable to determine if the owner (domain\myid) of job MyJobName has server access (reason: Could not obtain information about Windows NT group/user domain\myid, error code 0x54b.  [SQLSTATE 42000] (error 15404)).

    I know the issue would go away by switching from a Windows Auth account to a SQL Auth account but our data security group makes it very difficult to get a SQL Auth account.

    Any thoughts on how I can make it work?


    Thursday, February 17, 2011 10:13 PM