1

I am new to MSSQL. I have a question about executing MSSQL Stored Procedure.

Let me briefly talk about my development environment first:

  • PC400 (Computer/Server A), where SQL Server 2012 is installed
  • PC401 (Computer/Server B), where Java application is running on

PC400 has SSIS project & package(s) deployed to its SSISDB. Of course, I have no problem in executing the packages LOCALLY in PC400.

But this is not what I need. I want the java application installed on PC401 to be able to execute those packages stored in PC400. I have no problem in using a sa/temp user account with "SQL Server Authentication" to login the server: enter image description here

But based on my research, "SQL Server Authentication" does not allow me to deploy/execute packages in SSISDB.

Then, I found the following advice: Connect to SQL Server using windows authentication from another PC without Active Directory

Let say the windows user account of PC401 is "HKB\Hello123". By creating an identical Windows user under "MSSQL -> Security -> Logins" in PC400:

enter image description here

my java program is able to use the following codes to execute a stored procedure in PC400 using "Windows Authentication":

con = DriverManager.getConnection("jdbc:sqlserver://HKA-PC400:1433;DatabaseName=TempTest;integratedSecurity=true"); 
CallableStatement cs = null;
cs = this.con.prepareCall("{call SP_ETL_B}");
cs.execute();

But is there any other methods to achieve this? Besides, I hope my java program would be able to use a sa/temp user account("SQL Server Authentication") to pretend to be a "Windows Authentication" one OR simply connect to an existing Windows user account, then to trigger Stored proc/SQL Server Agent job to run the packages in PC400.

Improve this question
5
  • What are your needs around concurrent package execution? Would you be able to have a SQL Agent job that runs the ssis package as an account that used Integrated Authentication?
    – billinkc
    May 5, 2022 at 0:20
  • @billinkc 1. Is your first question asking me for the purpose of setting up ssis packages? 2. So far I know that I need a admin account to use Integrated authentication account. Is it possible that I use a non-admin(sql server authentication) to run a SQL Server agent job which calls packages in "Running Packages/Stored Packages"(setup in "Integration Services")?I remembered that I tried this way 2-3 weeks ago. It seems that it did not work. But I forgot to capture any screens/error messages.....
    – garethlam
    May 5, 2022 at 2:44
  • @billinkc Actually, I need to deploy a SSIS projects(created by VS2019) with 3 folders(IMPORT, EXPORT and SSIS). SSIS stores dstx files, IMPORT stores multiple csv files and EXPORT stores output files created by one of the packages. To simplify the job, a package will read those csv files -> create and write to temp/existing tables in MSSQL server -> do some data transforms (e.g. * -1, truncation, delete columns, etc) -> finally export 2-3 csv files.
    – garethlam
    May 5, 2022 at 2:50
  • Can you please add which version of SQL Server you're using as a tag to your post?
    – J.D.
    May 5, 2022 at 3:52
  • 1
    @J.D. edited. Thank you for your reminder.
    – garethlam
    May 5, 2022 at 4:26

1 Answer 1

Reset to default
0

You can use SQL Server Authentication / a SQL Login to execute SSIS packages, as long as that account has the appropriate permissions provisioned. This might be the simpler route to go for your application.

You probably want to take a look at the docs on catalog.start_execution (SSISDB Database), which is a stored procedure that allows you execute a particular SSIS package.

Please see the following regarding the permissions needed:

This stored procedure requires one of the following permissions:

  • READ and MODIFY permissions on the instance of execution, READ and EXECUTE permissions on the project, and if applicable, READ permissions on the referenced environment

  • Membership to the ssis_admin database role

  • Membership to the sysadmin server role

Only one of the aforementioned permissions is needed. The simplest choice but best compromise with security without over-provisioning would probably be to add the User of that SQL Login to the ssis_admin role.

Improve this answer
3
  • The ability to use SQL Server accounts with the SSISDB might work with a 2019 but 2012-2017 will error out with "Msg 27123, Level 16, State 1, Procedure SSISDB.catalog.create_execution, Line 41 [Batch Start Line 0] The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Integrated Authentication."
    – billinkc
    May 4, 2022 at 14:38
  • @billinkc you're right. Actually, I tried this a few weeks. But I did not know it may work for SQL Server 2019.
    – garethlam
    May 5, 2022 at 1:19
  • @billinkc Hmm interesting. 🤔 I feel like I was using my SA account to execute packages in SQL Server 2016 but I've recently upgraded to 2019 so hard to say.
    – J.D.
    May 5, 2022 at 3:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.