Run MSSQL stored procedures containing SSIS packages using Windows Authentication from another computer



  • 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: https://dba.stackexchange.com/questions/39614/connect-to-sql-server-using-windows-authentication-from-another-pc-without-activ

    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.



  • 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 https://docs.microsoft.com/en-us/sql/integration-services/system-stored-procedures/catalog-start-execution-ssisdb-database?view=sql-server-ver15 , 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 https://docs.microsoft.com/en-us/sql/integration-services/security/integration-services-roles-ssis-service?view=sql-server-ver15 role.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2