Monday, October 21, 2013

Use Windows account to grant SSIS packages R/W access to files on file share

Problem

To schedule a job that runs SSIS packages that need read/write access on folders and files on a file share, the windows account running the SSIS service needs access to the file share, or you can setup a proxy account with a dedicated windows account that has access to the file share and that can run the SSIS package. Packages are stored in the SSIS package store.

A solution is also explained in Running a SSIS Package from SQL Server Agent Using a Proxy Account

Solution

  • Create a windows account that has sufficient rights on the file share.
    • e.g. MYDOMAIN\ssisfileuser with password xyz
  • On the SQL server where the SQL Server Agent will be running the jobs, add the windows account as a new login.
    • In the user mapping of the Login properties, select the ‘msdb’ database and assign the ‘db_ssisoperator’ Database role membership for msdb to the login.
  • To run the package from a SQL job, the SQL Agent has to use the windows account as a proxy, to define the proxy we have to define a credential, where the password of the windows user can be stored.
    • Under Security / Credentials add a new credential
      • Credential name: ssisfileusercred
      • Identity: MYDOMAIN\ssisfileuser
      • Password: xyz
    • Under SQL Server Agent / Proxies, add a new proxy
      • Proxy name: ssisfileuserproxy
      • Credential name: ssisfileusercred
      • Activate the following subsystems:
        • Operating system (cmdexec) (if you are still running on 64-bit version of SQL 2008, not on SQL 2008 R2)
        • SQL server Integration Services Package
  • Define a new job to be run under the windows account
    • Under SQL Server Agent Jobs, add a new job
      • General
        • owner can be your own windows account name
        • category : I use ‘Data collector’ to put the job under this kind of category
      • Steps – add new step
        • Name: use a name of your choice, I use the same name as the package to run in this step.
        • Type: Sql Server Integration Services Package
        • Run as:ssisfileuserproxy
        • General
          • Package source: SSIS Package store
          • Server: my servername
          • Use windows Authentication is default and only setting
          • Package: select the package name to be run.
      • Schedules: add a schedule when to run the job.

With this approach you can schedule packages that need file access on a share to run from the SSIS package store.

No comments: