September 16, 2011 at 2:09 am
Hi Guys
Can someone please explain exactly how SSIS security works? I have a project that generates data into an Excel workbook to a destination on our network. I generate the data using a SQL stored procedure, all the security to allow my project to connect to the SQL database works (I use an XML config file). BUT!! the destination folder where the Excel book will be placed wont allow the file to be created, stating the user does not have sufficient rights??
Now if I disable the package configurations and run under my domain login I am able to prepare the data and generate the physical file, when I use the config and SQL authenticated user I can generate the data but not the physical file..
User what username will the SSIS package attempt to connect to the directory? Can I change that user to one that will have rights?
Thanx for reading..
September 16, 2011 at 8:53 am
Mark Kinnear (9/16/2011)
Hi GuysCan someone please explain exactly how SSIS security works? I have a project that generates data into an Excel workbook to a destination on our network. I generate the data using a SQL stored procedure, all the security to allow my project to connect to the SQL database works (I use an XML config file). BUT!! the destination folder where the Excel book will be placed wont allow the file to be created, stating the user does not have sufficient rights??
Now if I disable the package configurations and run under my domain login I am able to prepare the data and generate the physical file, when I use the config and SQL authenticated user I can generate the data but not the physical file..
User what username will the SSIS package attempt to connect to the directory? Can I change that user to one that will have rights?
Thanx for reading..
Hi,
It depends on how you execute the SSIS package. If you schedule it through SQL Agent, the package will be executed by using the credentials of the SQL Agent Service Account. If you run the package in design mode, your credentials (the logged-in user) will be impersonated. If the package is executed through SSMS, I believe the SQL Server Service Account's credentials will be impersonated.
The problem in this case is that the account used for executing the package (SQL Agent or SQL Server service account) does not have the appropriate permissions on the target folder of the generated file.
There are a few ways you could resolve this:
1. If the service account of SQL Agent (I am assuming you would schedule the package through Agent) is a domain user, grant the appropriate permissions to that account on the target folder.
2. If the service account is not a domain user, you should look at creating a proxy account which can be used for this purpose.
Hope this helps.
Martin.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply