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:
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:
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.