May 20, 2012 at 12:55 pm
Hi everyone,
I have a question, I have a job that is running under a sql server login account. It runs a SSIS package that queries a database and creates an Excel file on another server with the results of the query. My question is, which account is it using to try to access the folder on the other server? I assume that it is the SQL Agent user account, but I am not sure. Thanks.
May 20, 2012 at 10:06 pm
By default jobs run under SQL Agent account.
However, Proxy account can be used to run an SSIS package.
Open the job step and see what is there under "Run As".
May 21, 2012 at 10:36 am
Thanks for your reply. Yes, the job is set to run under the proxy account, which is a sql server login. So, when it tries to create this excel file on another server, which account is it trying to use?
May 22, 2012 at 12:43 am
The account running the job must be a Windows account with rights to create the file in the folder on the other server.
May 22, 2012 at 8:08 am
Seems like you are finding yourself in a situation similar to one we encounter from time to time where I work. That is, you need to have a job step run with different credentials than the SQL Server Agent Account. If you're in the similar scenario, you don't have an option to change the agent account (really only need this one step to have elevated access on a different server, but you don't need all job steps to have this elevated access).
To accomlish this, having the job step use a different set of credentials than the Agent account when creating the excel file, you will need to create and use credentials.
Credentials: A credential is a record that contains the authentication information (credentials) required to connect to a resource outside SQL Server. This information is used internally by SQL Server. Most credentials contain a Windows user name and password.
(BOL: http://msdn.microsoft.com/en-us/library/ms161950(v=sql.105).aspx)
Create a credential that is mapped to a Windows Login Account with sufficient permissions to wrtie to the Excel File on the other server.
(BOL: http://msdn.microsoft.com/en-us/library/ms189522(v=sql.105).aspx)
In the job step properties tab select the credential from the "run as" drop down.
(BOL: http://msdn.microsoft.com/en-us/library/ms187668(v=sql.105).aspx)
Now when your job runs, it will use the credential when writing the excel file which will have permissions to write the file.
[Edit: in all the typing I forgot to answer the question at hand. Yes unless specified as described above, Access outside of SQL Server is performed in the context of the SQL Server Agent Service Account].
-
May 24, 2012 at 5:13 pm
Thank you very much all
Jason, your post is most appreciated, thank you!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply