SSH Connection Manager is SSIS Connection Manager for establishing SSH connections. Use the API to build components based on SSH Connection Manager.
The Secure Shell (SSH) Connection implements the following standards:
- SSH Transport Layer Protocol, as described in IETF RFC 4253,
- SSH Authentication protocol, as described in RFC 4252, and
- SSH Connection Protocol as described in RFC 4254.
As a result, it performs the following functionality:
- Authenticates the server for the client
- Authenticates the client for the server
- Multiplexes the encrypted tunnel into several logical channels.
The SSH Connection provides the ability to login securely to a remote server and to use other secure network services over an insecure network. Public/private key encryption is used for the client and server to authenticate each other. A password is used to login to an account on the server.
The secure connection always gets closed at the end of package execution.
Currently, the SSH Connection may be used with the File Transfer Task (for SFTP), the SSH Execute Task, the SCP Task, and the Secure Tunnel Task (this is a script that can be used with Script Task Plus). Other tasks, components, and scripts may be added to this list in the future.
In order to use the SSH Connection, you will need to have access to the encryption key file and the passphrase for it. You’ll also need to have the user name and password, as well as the IP address of the host you are connecting to and the port to use for that connection. The default port is 22 and can be changed to a port of your choosing. Consult with your network and security administrators to obtain the encryption file and other credentials to use for your environment.
The Advanced tab gives you the ability to turn on verbose logging and to select the binary file transfer mode. If you do not select binary file transfer mode, the ASCII file transfer mode will be used by default.
Under the Proxy tab, you may specify all the information necessary for using a proxy server: host IP address, port, user name, password, and proxy type (ie. Tunnel, SOCKS4, SOCKS5).
Use the Server page of the SSH Connection Manager dialog to specify properties for connecting to an SSH-enabled server.
- Test Connection
- Confirm connection manager configuration by clicking Test Connection.
Specify the name or IP address of the SSH server.
Specify the port number on the SSH server to use for the connection. The default value of this property is 22.
Specify user name to access the SSH server.
- This parameter is deprecated since 1.6 SR-3 version.
Specify authentication type. This property has the options listed in the following table.
Value Description By Password User is authenticated with password. Selecting this value displays the dynamic option Password. By Key File User is authenticated with private key file, unlocked with passphrase. Selecting this value displays the dynamic options Key File, Passphrase.
Specify password to access the SSH server. Specify arbitrary value for anonymous account.
Select private key file to access the SSH server.
Specify passphrase to unlock the private key file.
Specify the number of seconds before timing out session connect. The default value of this property is 60 seconds.
Specify if all clients should use the same connection. This property has the options listed in the following table.
Value Description True All clients use same connection. False Every client use different connection (default).This property is not visible in the connection manager dialog.
Use the Advanced page of the SSH Connection Manager dialog to to specify additional properties if your server configuration is different from the default.
Specify verbose log file name for investigating SSH connection issues.
Specify to use binary mode for file transfers.
Specify SSH backend engine in use. This property has the options listed in the following table.
Value Description 1 Use legacy SSH backend engine. 2 Use new 1.5 SSH backend engine (default).This property is not visible in the connection manager dialog.
Specify remote server host fingerprint to verify before authentication.This property is not visible in the connection manager dialog.
Specify to use compression.
Use the Proxy page of the SSH Connection Manager dialog to specify properties if your server is behind firewall.
Specify proxy type. This property has the options listed in the following table.
Value Description None Proxy not specified Tunnel Use tunnel (HTTP) proxy type. SOCKS4 Use SOCKS4 proxy type. SOCKS5 Use SOCKS5 proxy type.
Specify the name or IP address of the proxy server.
Specify the port number on the proxy server to use for the connection. The default value of this property is 80.
Specify user name to access the proxy server.
Specify password to access the proxy server.
- The Execute method on the task returned error code 0x80004003 (Object reference not set to an instance of an object.). The Execute method must succeed, and indicate the result using an "out" parameter.
... and also:
Failed to decrypt protected XML node "ServerPassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.
The default package protection level: EncryptSensitiveWithUserKey, doesn't work very well with SQL Agent Jobs.
If you decide to setup package encryption with EncryptAllWithPassword or EncryptSensitiveWithPassword you have to make sure you provide the password to the command line for DTEXEC application. More information how to setup DTEXEC in SQL Server Job Agent is available here and information about DTEXEC command line options is available here
You may also find useful the following articles:
- Error: 0xC0014005 at : The connection type "SSH" specified for connection manager "SSH Connection Manager 1" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.Starting from SSIS+ 1.3 SR-2, this error is resolved. Use the 64bit installation.
By default SSIS+ is installed under the 32bit - Program Files (x86) folder. You have two options:
- Set Run64BitRuntime project property to False to force SSIS to execute with the 32bit runtime.
- or ... find CozyRoc.SSISPlus.dll library and manually copy it to the following 64bit folders:
- Microsoft SQL Server\90\DTS\Connections
- Microsoft SQL Server\90\DTS\Tasks
- Microsoft SQL Server\90\DTS\PipelineComponents
- Microsoft SQL Server\90\SDK\Assemblies
PuttyGen doesn't generate proper DSA keys, which can work with the SSH Connection Manager. You may use ssh-keygen application, which generates keys recognized by SSH Connection Manager. The application is part of the OpenSSH package. Use the following command to generate 1024 bit DSA keys:
ssh-keygen.exe -b 1024 -t dsa
You may have firewall/proxy issue. Make sure you can successfully telnet your server. You should get back text starting with "SSH-2.0".
The SSH Connection Manager doesn't support directly Putty Private Key (PPK) Files. You have to export your key in OpenSSH format. You can do this in PuttyGen application:
- Open your .PPK file in PuttyGen.
- Select Conversions -> Export OpenSSH key
- New: A new parameter Compression.
- New: Modified to permit weaker server RSA keys (512 bits).
- Fixed: Failed with "The server's RSA key (1023 bits) is weaker than expected minimum (1024 bits)." error when connecting to certain servers.
- Fixed: Incomplete file download when file size between 32kb and 49kb and when using SSH-2.0-FileCOPA or Syncplify server software (Thank you Kevin and Greg).
- Fixed: Failed to upload a file to a restricted folder (Thank you, Daniel).
- New: A new parameter Passphrase.
- New: A new parameter ServerFingerprint to verify remote host fingerprint before authentication.
- Fixed: Failed with "Cannot access a disposed object." error when using new backend engine.
- Fixed: Failed with "Bad Message" error when using Serv-U server and Remote parameter contained wildcard.
- Fixed: Disabled zlib compression because of file corruption (Thank you, Dmitry).
- New: A new parameter RetainSameConnection to use same connection in client components.
- Fixed: Failed to connect to servers requiring both key file and password for authentication (Thank you, Jeremy).
- Fixed: When connected to GlobalSCAPE failed with "inputstream is closed" error message, trying to retrieve directory list with more than 250 files.
- New: Included support for keyboard-interactive mode of authentication.
- New: Introduced connection.
Ready to give it a try?
COZYROC SSIS+ Components Suite is free for testing in your development environment.