Sample Header Ad - 728x90

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

1 vote
1 answer
859 views
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.***
Asked by garethlam (11 rep)
May 4, 2022, 10:01 AM
Last activity: Jul 5, 2025, 03:01 PM