SSIS - executing package from SQL Server by stored procedure

Question

How to execute SSIS package from stored procedure? At this moment I am able to do this by using Windows authentication. However, I must do it using SQL Server authentication, but SQL Server requires Windows authenticated user.

• Arin Taylor: If something in the package requires permissions to external folders/files then you may need to grant the user that the SQL Server service is running as permissions to those resources.
• blobbles: What version of SQL Server are you using and are you using SSISDB to store the packages?
• user114705: 2012 in both cases
• blobbles: Is SSISDB being used to store the packages? Or are you storing packages on the file system?
• blobbles: What? That did not answer my question... file system or SSISDB?

Answer 1

If you are using SSISDB (which I think you are?), you can Execute a set of standard stored procedures that are in the SSISDB. These are outlined in the MSDN Article here.

This article also states the permissions that are required to run these procedures. It sounds like you need to set up a SQL Server login which has these permissions. How to describe this is located here. You will need to do this with your existing Windows Authenticated user if it has permission to allocate permissions (ALTER ANY LOGIN or ALTER LOGIN permissions).

You may also need to create a user who is mapped to this login as described here.

Your question is not very specific so without further information about a particular problem you are having, it is difficult to give a clear answer.

• user114705: The problem is about these procedures (you named them standard). When I call any of them using SQL Authenticated user I got error something like: you must use window authenticated user
• blobbles: Can you give us a screenshot of this error? As far as I know, SQL Server should allow you to use either a Windows Authenticated user or a SQL Server User as long as they have the appropriate permissions as specified. It should not matter which type of user it is, the only important thing is their permissions.
• blobbles: One thing to remember is that the SSIS packages are run under the permission of the SQL Server Service account. So this account must have permission across the file shares that the package elements are accessing. So in your package, if you are pointing to a file: "\\filesharename\foldername\textfile.txt", the SQL Server Service account needs to have permission to access this file. Without this permission in place you may get the error you are talking about.
• user114705: Once again: Logging with SQL Authentication and calling start_execution I got "You should use winodws authenticated user", however logging on windows authenticated user I didn't get an error.
• blobbles: Does your SQL Server Service Account have all appropriate permissions to all the fileshares that are in your package? You aren't being entirely helpful with your explanations. You should edit your question and be specific about the steps you are taking to receive the error. You may know what you are doing but we have no idea and cannot see the errors or your screen. Screenshots and a list of steps taken to get the error is what is required to give you an answer. You have written 3 lines and expect us to solve a basically unspecified problem.