Hello all, I have created a SSIS package, which executes some TSQL codes at a number of SQL servers. Some connection managers are created with Windows authentication and some with sa accounts. Each task in the package executes succesfully, (each task is using a different connection manager) and the package also executes successfully in BIDS. But when I deploy the package in my local SQL server and then run the package with a SQL agent job, the package and job fails. The error indicates that SQL agent account does not have enough permission to execute the package successfully. But once the job starts, shouldn't the job use the authentication options that are given in the package to execute tasks in different servers? Why the package execution depends on the SQL Agent account of the local server? Thanks.

Please provide the exact error(s) reported. Talk to me now on

When you schedule a SSIS package to run as a Job, the account used to execute the package is the account assigned to SQL Agent Service, therefore any package using Windows Authentication will execute in the context of the SQL Agent account. You have two options: Give the SQL Agent Service account the appropriate permissions to access the server(s) and database(s) in your package. Set the "Owner" of the SQL Job (Job properties) to be an account that has the appropriate permissions to access the database(s) in your package. If your package needs to access files off the server or any other location, the account used to execute the job will also require the appropriate file system permissions to allow it to access the files. Cheers JeffJeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt)

http://support.microsoft.com/kb/918760.Happy to help! Thanks. Regards and good Wishes, Deepak.

I would like to suggest you to create a proxy account with proper permissions and run the SQL job under that account. Let us TRY this | My Blog :: http://quest4gen.blogspot.com/

I would like to suggest you to create a proxy account with proper permissions and run the SQL job under that account. Let us TRY this | My Blog :: http://quest4gen.blogspot.com/ Proxy accounts are used to map SQL Server accounts to Windows credentials, thus enabling SQL Server accounts to access the server filesystem and other network resources. If the package doesn't require access to such resources, or the package is configured to run under Windows Authentication, there is no need to create a proxy account as this just complicates the situation. Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt)

I would like to suggest you to create a proxy account with proper permissions and run the SQL job under that account. Let us TRY this | My Blog :: http://quest4gen.blogspot.com/ Proxy accounts are used to map SQL Server accounts to Windows credentials, thus enabling SQL Server accounts to access the server filesystem and other network resources. If the package doesn't require access to such resources, or the package is configured to run under Windows Authentication, there is no need to create a proxy account as this just complicates the situation. Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) I think using a proxy is a good solution. Apparently the package needs access to external resources, othwerise the OP wouldn't have this issue. Creating a proxy may add complixity, but it allows you to define clear boundaries on what that specific job can run or not. It has the same effect as changing the owner of the job. Elevating the permissions of the SQL Server Agent account doesn't seem like a good idea to me, as suddenly every other Agent Job that runs under the Agent account has more permissions than they need to have.MCTS, MCITP - Please mark posts as answered where appropriate.

I think using a proxy is a good solution. Apparently the package needs access to external resources, othwerise the OP wouldn't have this issue. Creating a proxy may add complixity, but it allows you to define clear boundaries on what that specific job can run or not. It has the same effect as changing the owner of the job. Elevating the permissions of the SQL Server Agent account doesn't seem like a good idea to me, as suddenly every other Agent Job that runs under the Agent account has more permissions than they need to have. You are incorrect in making this assumption, especially considering the OP clearly states "which executes some TSQL codes at a number of SQL servers". You are also incorrect in stating that the OP wouldn't have this problem if the package didn't access external resources. These errors can occur if the account executing the package has insufficient permissions to execute the TSQL on a given database. Creating a proxy isn't the same as changing the owner of the job. Besides, the OP has stated that the package uses Windows Authentication so why create a proxy account when the simple solution is to create a Windows account in AD to run the package and give that account the required permissions. I agree that elevating the permissions of the SQL Serv Agent account is not the best way to approach this problem.

You are incorrect in making this assumption, especially considering the OP clearly states "which executes some TSQL codes at a number of SQL servers". You are also incorrect in stating that the OP wouldn't have this problem if the package didn't access external resources. These errors can occur if the account executing the package has insufficient permissions to execute the TSQL on a given database. Creating a proxy isn't the same as changing the owner of the job. I made an assumption that the SQL Servers are not local. If it is correct or not, we both don't know. The OP has to give us more information than that. Usually, the SQL Server Agent account has access to local databases (if we generalize a bit), when there is no specific account created for SQL Server Agent (but again, we don't know. The OP has to give us more info on that). Using a proxy and changing the owner of the job is certainly not the same. I said it has the same effect: you set the account that the job uses to an account that has the necessary permissions. My point was that giving the SQL Server Agent account extra permissions so that it can access extra resources (be they local or remote), just for one job, seems like a bad idea.MCTS, MCITP - Please mark posts as answered where appropriate.

My point was that giving the SQL Server Agent account extra permissions so that it can access extra resources (be they local or remote), just for one job, seems like a bad idea. MCTS, MCITP - Please mark posts as answered where appropriate. Totally agree. I took "Some connection managers are created with Windows authentication and some with sa accounts" as meaning that those servers not reachable on a domain were using the SQL Server sa account (another bad idea. The sa account should NEVER be used. Hence the reason why it's disabled by default in later versions of SQL Server). Given that the tasks running under the sa credential require a password, it's unlikely that access to these databases would work in BIDS and fail as a job. On the other hand, jobs by default run under the context of the SQL Server Agent account therefore the packages would be executed in the context of this account. Packages executed in BIDS run under the account of the person logged onto the PC, not the SQL Server Agent account. This difference would indeed cause a package to fail as a job as it's more likely that the developer has greater permissions to databases than the SQL Server Agent account on each server. Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt)

Thank you all for your replies.

No problem and thanks for marking you question as answeredJeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt)