Deployed package with SQL Server authentication logon fails

Question

Deployed package with SQL Server authentication logon fails

EamonSQL

SSCrazy

Points: 2307
More actions
July 12, 2018 at 2:07 am

#414359

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110639 More actions · Answer 1

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)

HanShi SSC-Dedicated Points: 33506 More actions · Answer 2

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/

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

EamonSQL SSCrazy Points: 2307 More actions · Answer 3

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

EamonSQL SSCrazy Points: 2307 More actions · Answer 4

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

HanShi SSC-Dedicated Points: 33506 More actions · Answer 5

EamonSQL - Thursday, September 13, 2018 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

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.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **