July 12, 2018 at 2:07 am
Hi,
I'm new to deploying SSIS packages so apologies if this question is a simple one.
I have deployed a package that has a SQL Server authentication.
The package fails to run on the deployed server reporting that the logon failed for the SQL Server authentication logon.
I think the reason for this is that the password hasn't been deployed with the package even though I have chosen the "Save Password" checkbox in the connection manager.
Any help welcome.
Thanks,
Eamon
July 12, 2018 at 7:10 am
A couple of problems here. You should NOT be saving passwords in SSIS packages... major security risk in doing so. However, while I can assume your package worked fine locally within Visual Studio, the server environment does not have your user credentials to play with, so if you only provided a password, then that could be why. Also, the permissions under which the package runs on the server may well be different than what you have access to, so be sure to consider that as well. Fixing your specific problem may be a matter of validating that the password provided is correct and that the SQL Login actually exists on the server the package was deployed to.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 12, 2018 at 7:38 am
I agree with Steve to not save passwords in SSIS. Modify your SSIS package to set them with variables. Here are some links with explanations on how to do this:
https://www.hansmichiels.com/2016/11/19/using-sensitive-parameters-ssis-series/
https://zappysys.com/blog/how-to-run-an-ssis-package-with-sensitive-data-on-sql-server/
July 17, 2018 at 8:33 am
Thanks for the great guidance Guys.
After a couple of diffrrent approaches I've went with setting them variables and it works fine and makes sense.
Thanks again,
Eamon
September 13, 2018 at 8:50 am
Hi,
belated thanks for the great replies.
I have decided to go for the Powershell route and have a follow-up question.
Running the command at the powershell prompt works fine but from within A SQL job step I get the following error :
Executed as user: AAAAA\BBBBBB. A job step received an error at line 1 in a PowerShell script. The corresponding line is 'Invoke-WebRequest -URI http://MyWebPage.php'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'The term 'Invoke-WebRequest' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again. '. Process Exit Code -1. The step failed.
I have checked the version of Powershell and the server is running Version 4.
The command is:
Invoke-WebRequest -URI http://MyWebPage.php
As I say from the prompt it works fine but from the job is doesn't.
The job step is run as SQL Server Agent Service Agent, is that the issue?
Any help welcome
Thanks,
Eamon
September 13, 2018 at 10:36 am
EamonSQL - Thursday, September 13, 2018 8:50 AMHi,
belated thanks for the great replies.I have decided to go for the Powershell route and have a follow-up question.
Running the command at the powershell prompt works fine but from within A SQL job step I get the following error :
Executed as user: AAAAA\BBBBBB. A job step received an error at line 1 in a PowerShell script. The corresponding line is 'Invoke-WebRequest -URI http://MyWebPage.php'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'The term 'Invoke-WebRequest' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again. '. Process Exit Code -1. The step failed.
I have checked the version of Powershell and the server is running Version 4.
The command is:
Invoke-WebRequest -URI http://MyWebPage.phpAs I say from the prompt it works fine but from the job is doesn't.
The job step is run as SQL Server Agent Service Agent, is that the issue?Any help welcome
Thanks,
Eamon
Since this question is only slightly related to the initial question, you best start a new thread.
First hint: the PoSh used within SQL agent is not the complete PoSh from the command line.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply