locked
SSIS 2012 package runs under SSDT but fails with permission error from SSMS RRS feed

  • Question

  • I have a simple package that pulls data from a text file located on a Windows file server that runs successfully from SSDT on my client.

    However, when deployed and running the package via SSMS from the client, the package fails with a permission error, with the error stating that the data file could not be opened.

    I have done some investigation and have seen some info that states that a package run from SSMS runs under the account on which you are logged on to the machine, which I would have thought is correct. This is therefore the same account when running from SSDT (which works) and I can see from the SSIS Report that the 'Caller' is stated as my account, so if this is the case why isin't the package working.

    I've also seen an MSDN forum answer which stated the opposite that the package when run from the IS Catalog in SSMS doesn't run under the account on which the client is logged on with, which is the opposite of the above, but doesn't indicate which account it is using.

    So what account is being used to run SSIS 2012 packages from the IS catalog from within SSMS ?

    If it is the account on which you are logged onto the client running SSMS, why is it not working when the account has the necessary permissions (as provent when running from SSDT) ?

    Friday, November 14, 2014 2:08 PM

All replies

  • If the file is on a remote share SSMS would not delegate the account rights.

    In fact SSMS is an admin tool, not client tool.

    The packages need to be run by a proxy to have proper access. Typically set as a job http://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/


    Arthur

    MyBlog


    Twitter

    Friday, November 14, 2014 2:52 PM
  • That doesn't really answer the question. I know I can run it from a job, but I would like to be able to run it from SSMS.

    Not sure about the relevance of your point that SSMS is an admin tool not a client tool.

    Friday, November 14, 2014 2:57 PM
  • Hi Paul,

    User credentials under which SQL Server Data Tools (SSDT) is operating will be used to execute the packages that are executed within SSDT.

    When we right-click on a package under Integration Services Catalog \ SSISDB \ <Folder name> \ Projects \ <Project name> \ Pakages \ <Package name> and select Execute... to run a package, the package will run under the credentials used to connect to SQL Server Management Studio. Please note that we need run a package using Windows Authentication.

    As to your issue, please make sure the account connects to SQL Server Management Studio has required permissions to make connections and access the files outside the SSIS package.

    If there are any other questions, please feel free to ask.

    Thanks,
    Katherine Xiong

    If you have any feedback on our support, please click here.


    Katherine Xiong
    TechNet Community Support

    Monday, November 17, 2014 12:18 PM
  • Hi Katherine,

    I agree that the package would also run under the credentials used to connect to SSMS, which is the same concept as with SSDT as they are both tools running on the client.

    In this case therefore the account is the same.

    The account has the necessary permissions to make connections to access the files outside of the SSIS package as it is the same account that is running under SSDT, which as I have stated is the same account.

    I've dug a little deeper and made an RDP connection to the Server (with the same account) and opened SSMS from there, and executed the package successfully. The account is the same, but the only difference is that this account has admin rights on the Server, but not on the client. I wonder if that could be the issue somehow ?

    Tuesday, November 18, 2014 10:59 AM
  • Hi Paul,

    According to your description, the issue can be caused by the account doesn’t have permissions to execute the job.

    To configure a user to create or execute Microsoft SQL Server Agent jobs, you must first add an existing SQL Server login or msdb role to one of the following SQL Server Agent fixed database roles in the msdb database: SQLAgentUserRole, SQLAgentReaderRole, or SQLAgentOperatorRole.

    If these non-administrative users want to run jobs that execute other job step types (for example, SSIS packages), they will need to have access to a proxy account. 

    For more information about Configure a User to Create and Manage SQL Server Agent Jobs, please see:
    http://msdn.microsoft.com/en-IN/library/ms187901(v=sql.110).aspx

    Thanks,
    Katherine Xiong

    If you have any feedback on our support, please click here.


    Katherine Xiong
    TechNet Community Support

    Tuesday, November 18, 2014 11:20 AM
  • There's nothing in my description where I talk about the issue being caused by an account not having permissions to execute a job.

    Please read the original post. The issue is not being able to run a package from within SSMS. This is simply selecting a package from the IS Catalog, right-clicking and selecting Execute package.

    It is also nothing to do with having permissions to be able to execute the package. The package runs but fails to open a data file that the package calls. As already explained, the account running under SSMS is the same as running under SSDT and this account has access to the file in question as it runs successfully under SSDT as already explained.

    Tuesday, November 18, 2014 2:06 PM
  • It should be caused by the account has no admin rights on the current server. You can try to add admin rights to the account and check the issue again.
    Wednesday, November 19, 2014 1:29 AM
  • Already been stated above that the account has admin rights on the server.
    Wednesday, November 19, 2014 10:03 AM
  • The account rights propagation does not work though. Thing is SSMS does not do that.

    Arthur

    MyBlog


    Twitter

    Wednesday, November 19, 2014 3:57 PM
  • "The account rights propagation does not work though. Thing is SSMS does not do that."

    Please explain. Why does SSMS not propagate rights through ? This sounds strange because it essentially means that you can't run IS packages from the catalog in SSMS when that package calls data from a remote share.

    Can you provide a link with information that indicates that this is the case.

    Wednesday, November 19, 2014 4:11 PM
  • Yes, it is a security measure.

    Bear in mind, SSMS is an admin tool.

    I have no link other than you and I can Bing/Google that out.


    Arthur

    MyBlog


    Twitter

    Wednesday, November 19, 2014 4:40 PM
  • "Bear in mind, SSMS is an admin tool" - so what ?. SSDT is a Developer tool, but can also act as an admin tool as such in that it allows the creation, manipulation of data objects etc, similar to SSMS. Makes no sense therefore that SSMS would be locked down in such a way that SSDT isin't.

    "Yes it is a security measure"... Why provide the ability to run SSIS packages from within SSMS if a security exists that means remote shares cannot be accessed. This would be a severe limitation and I'm unaware this existed in previous versions of SQLServer. Also, it makes no sense since the account under which SSMS is running has access to the share in the first place.

    If you have no link or information (workings that prove that's how it works) that you can provide then how do you know?. In absence of any factual  information that backs up your statement then I can only conclude this is incorrect.

    Thursday, November 20, 2014 10:41 AM
  • FYI if you execute a package from the SSIS catalog manually (not via a job) then it will use your account and not the one of the service.

    You can test this by logging the System Variable "System:UserName" in for example a Script Task or Execute SQL Task.

    If you are using network shares you might want to test if it works when you add a share via the Execute Process Task: net use f: \\financial\public


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter


    • Edited by SSISJoostMVP Thursday, November 20, 2014 9:18 PM
    Thursday, November 20, 2014 9:16 PM
  • I am aware it's using my account and not one of the service. There's nothing in the posts above which suggests I'm using the service account. I don't know why people keep assuming that I'm running the package from a job. I've never stated that in any of the posts I'm running the package from a job. It's been made quite clear I'm running the package from the IS Catalog on SSMS:

    "This is simply selecting a package from the IS Catalog, right-clicking and selecting Execute package.", as per the Nov 18 post, and in the original post "....So what account is being used to run SSIS 2012 packages from the IS catalog from within SSMS".

    Just to be clear, the package is NOT being run from a job. It's using the Execute package option when selecting the package within SSMS from the IS Catalog and it's running under the account on which I am logged on to my client.

    A good suggestion about using a share in the task to map the drive and I will try this. I would find it strange that SSMS requires this mapping whereas SSDT does not though.

    Friday, November 21, 2014 3:42 PM
  • I am aware it's using my account and not one of the service. There's nothing in the posts above which suggests I'm using the service account. I don't know why people keep assuming that I'm running the package from a job. I've never stated that in any of the posts I'm running the package from a job. It's been made quite clear I'm running the package from the IS Catalog on SSMS:

    "This is simply selecting a package from the IS Catalog, right-clicking and selecting Execute package.", as per the Nov 18 post, and in the original post "....So what account is being used to run SSIS 2012 packages from the IS catalog from within SSMS".

    Just to be clear, the package is NOT being run from a job. It's using the Execute package option when selecting the package within SSMS from the IS Catalog and it's running under the account on which I am logged on to my client.

    Please read carefully. I was just answering your question "So what account is being used to run SSIS 2012 packages from the IS catalog from within SSMS ?" and stating that you where right and that it uses your account to run the package when you execute a package manually from the Catalog and that you can prove that by logging the system variable.

    A good suggestion about using a share in the task to map the drive and I will try this. I would find it strange that SSMS requires this mapping whereas SSDT does not though.

    Ok let us know your findings. If it doesn't work you can also test it with a password and username:
    net use f: \\financial\public  yourpassword /user:username

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Friday, November 21, 2014 9:41 PM
  • Just before making a change to the package and using the net use command, I tried to run the same package from SSMS to clarify the original error as I hadn't worked on this for a few days and suprisingly the package now runs successfully!

    That's good in that the error no longer exists but not so good in that it's very difficult to determine what the original issue was. To my knowledge no permissions have changed, the package has not changed & no other configs have changed. The only item that was changed was a rename of the project folder in the IS catalog so I changed that back to the original and the package still ran successfully so I'm a bit confused by this one. I even delved into the SSIS catalog views looking at the execution results to see if I could see anything different but found nothing.

    Anyway, one thing it does prove that one of the posts above about this does not work because SSMS doesn't do account propagation/security is incorrect as suspected as this statement made no sense.

    I'll monitor and see if the issues re-appears for whatever reason.

    SSISJoost, I appreciate your input.

    Wednesday, November 26, 2014 11:40 AM
  • I am bummed that it is unknown, for I am currently having this same issue.

    I am in SQL Server Management Studio 2016 connected to SQL Server 2016 SP1 instance and when I run the package in Integration Services manually it errors stating it can not open file \\lx01\div\somepath\file.csv.  If I schedule the job in SQL Server Management Studio it runs as the SQL Agent AD service account with no file permission problems.  It also runs as my self in Visual Studio 2015.  As my personal Active Directory account is admin and I am a sysadmin server role, it makes no sense why running the package in SSMS fails accessing a UNC path because of permissions.

    Thursday, March 30, 2017 3:19 PM
  • Just migrated some packages to a new 2016 instance and now I'm having the same issues.   I used to be able to execute packages from SSMS, but now I can only get them to work if I create a job for them first.

    The user account I'm using to test from SSMS has full access to the shares/folders/files and I'm still getting access denied errors.

    MS, what changed?

    Wednesday, July 12, 2017 2:10 PM