FTP Task: 'The password was not allowed'
-
Hello,
I'm getting a fairly common error with the FTP task. The error is "The password will not be allowed." This is after I attempt to set the password of the ftpconnection manager in a script task that is called before the FTP task.
I'm emulating these examples:
http://www.ideaexcursion.com/2008/11/24/set-ftp-password-in-ssis/
http://www.proteanit.com/b/2008/02/13/ssis-ftp-task-code-to-set-the-password/
I set the ServerName, ServerUserName and ServerPassword properties for the FTP connection manager. I've used debugging and logging to verify that these values are what they should be prior to the FTP task being called.
Here's my code (in the script task which precedes the FTP task), which is nearly identical to the two examples I am linking to:
Dim FTPConnectionManager As ConnectionManager
'Set variable to an existing connection manager
FTPConnectionManager = Dts.Connections("FTP Connection Manager")
FTPConnectionManager.Properties("ServerName").SetValue(FTPConnectionManager, Dts.Variables("ServerName").Value)
FTPConnectionManager.Properties("ServerUserName").SetValue(FTPConnectionManager, Dts.Variables("ServerUserName").Value)
FTPConnectionManager.Properties("ServerPassword").SetValue(FTPConnectionManager, Dts.Variables("ServerPassword").Value)
btw, the 'DelayValidation' property of the FTP Task is set to true.
I'm trying to run the package on a test server and in BIDS. When running on the test server, I set the package protection level to 'ServerStorage' and then copy it over. When running from BIDS, 'EncryptAllWithUserKey' seems to be the only protection level I can use.
Does anyone have any suggestions? Thanks in advance,
-
I think frustrated SSIS developers should swarm on Microsoft's HQ with lit torches and sharp knives and demand to know why they have made it damn near impossible to use the FTP task. Even worse, there are no examples of what will work.
I've tried just about everything now. I've configred the FTP configuration manually, and "test connection" succeeded. Run the package on the server (using 'ServerStorage' as the protection level) and I *still* get the error: Password was not allowed.
I found this page:
I have tried changing the connection string in the script task prior to the FTP task. I have tried a variety of connection strings, such as:
FTPServername:21
FTPServername:21; User ID=FTP User Name; Password=FTP Password
FTPServername:21.FTP User Name.FTP Password
With everything I have tried, I have received the error message: "The password was not allowed."
Is there software in Linux that performs essential SSIS operations without torturing the developer? I didn't plan on spending *days* trying to get an object that is part of Visual Studio to work. (It might be faster for me to hop on a plane and deliver a CD than to continue trying to get the FTP task to work in BIDS.)
All help appreciated. thanks.
-
Hi cafescott
I tried to replicate your issue on my workstation, I set a local FTP server and it worked fine, I had some password rejection because I did not enabled basic role on the FTP server.
I understand you tried everything, I will suggest this test.
1. Find the account your package is using when running at the server.
2. Login into the server with the account credentials your package is using (they are not the FTP credentials)
3. Open a command prompt
4. At the command prompt (the DOS like black screen) try to login to the ftp server using the FTP credentials:
ftp savername
user TheFtpLogin
password TheFtpPassword
I expect it to fail with a password error; if it fails you have permissions issues at the server for the FTP account.
If you succeed login to the FTP server, then try either the dir or ls command, if they fail, your FTP account is missing read-write permissions at the remote FTP server.
If you managed to login to the FTP server with the FTP credentials your package is using, and able to list a directory, then your SSIS package has issues (thinking about 32 or 64 bits as a possible reason -I am just guessing here-)
One last test is to put a file at the FTP remote server, I will try that if you are trying to transfer files to the remote server, but I am not sure if your packages is getting files from the FTP server or sending files there.
Let us know the outcome of this test, if it fails, the Net Framework contains the classes to implement FTP, although it seems you are having permissions issues with your remote FTP server.
Cheers,
Hope this helps,
Rock from VbCity -
Hey Rock,
Thanks for the reply. You've helped me identify the problem is related to permissions. I have confirmed this by:
1. logging into the server that I'm trying to FTP to;
2. running the package;
3. Looking at the event viewer of #1, and seeing a new error in the Application log.
Our setup is a little different than perhaps what you are describing. From my work PC, I have used the FTP credentials (using the native FTP service) and they work.
Here's where things get a little confusing. Our SQL server, like all servers on our network, is virtual. At the SQL server I am connecting to "Server2", and here I am running the package.
However, while the SQL server says I am connecting to Server2, when the package writes a file to the "C:\temp" folder, it is not being saved on Server2. So, there is a disconnect between the box that is running SQL server and the server that I am connecting to.
The package tries to FTP a file on our network to a test folder set up for FTP also on "Server2".
Following your suggestion, if I login to Server2 (using my network credentials) and then once inside, open a command window and attempt to "ftp server2" the connection is immediately closed.
On Server2, in the event viewer, the error I am seeing concerns IIS. It begins with:
IISWMSVC_AUTHENTICATION_UNABLE_TO_READ_CONFIG
An unexpected error occurred while retrieving the authentication information.
These two threads offer solutions to it:
http://forums.iis.net/t/1151093.aspx
http://learn.iis.net/page.aspx/321/configure-ftp-with-iis-manager-authentication-in-iis-7/
I'm seeing if I can fix the problem. If not, I'm going to have to wait until one of our network guys comes back to the office.
I'll post here once the problem is (hopefully) resolved. Thanks again for the help.
-
I ran a test on my workstation before posting my suggestion, I was chasing permissions problems, I did not try to use my local server because FTP permissions issues are better resolved on the offending environment (they are hard to duplicate), now knowing your are deploying on a virtual servers environment I am glad I did not try 😀
I am happy to know you are in the right track, it is very likely you will need assistance from the network team at your place, I hope you can post a follow up reply sharing the solution details.
Cheers,
Hope this helps,
Rock from VbCity -
Hey Rock,
I finally got it working. I was getting the "password was not allowed" error until I modified a script task that appears before the FTP task. The script task sets the server name, port, user name and FTP password properties in the FTP Connection manager. It also sets the connection string. The code looks like this:
Dim FTPConnectionManager As ConnectionManager
FTPConnectionManager = Dts.Connections("FTP Connection Manager")
FTPConnectionManager.ConnectionString = _
Dts.Variables("ServerName").Value & _
":" & Dts.Variables("ServerPort").Value & _
"." & Dts.Variables("ServerUserName").Value & _
"." & Dts.Variables("ServerPassword").Value
FTPConnectionManager.Properties("ServerName").SetValue(FTPConnectionManager, Dts.Variables("ServerName").Value)
FTPConnectionManager.Properties("ServerPort").SetValue(FTPConnectionManager, Dts.Variables("ServerPort").Value)
FTPConnectionManager.Properties("ServerUserName").SetValue(FTPConnectionManager, Dts.Variables("ServerUserName").Value)
FTPConnectionManager.Properties("ServerPassword").SetValue(FTPConnectionManager, Dts.Variables("ServerPassword").Value)
...
Dts.TaskResult = ScriptResults.Success
The FTP task follows this script task, and it is now, finally working.
Thanks for the help!
-
Thanks CafeScott for sharing your solution, you are using an Script Task to setup the FTP Connection Manager 'dynamic' properties, you can set them directly in the Connection Manager's Expressions; it exposes all its properties and probably an easier approach to achieve the same result.
I just posted these details for anybody dealing with FTP tasks, it will illustrate the different options available.
Cheers,
Hope this helps,
Rock from VbCity -
Rock, thanks for the information. You'll see that there is no ability to specify the FTP connection's password using the expression builder. (They don't make the password property available.)
So, you have to set the password in a script task and/or by setting the connection string. I was trying just the latter for a long time until I decided to set the password in both places.
I don't mind if it looks amateurish. All I need is for it to work.
thanks,
-
Wow, the password is not there, it is nice to know 🙂
Hope this helps,
Rock from VbCity
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply