External Connections

Reporting From External Database Connections

Note: The instructions on this page primarily apply to_hosted_PCR-360 Customers who want to access their data using third-party software. This does_not_discuss the PCR-360 feature to import and run Crystal Report RPT files. That feature is found at Crystal Reports (Deprecated). More specific discussion of developing Crystal Reports in particular is found at Crystal Development.

PCR-360 for Hosted Customers runs on a protected server that doesn't allow direct access to the data service. You must have a SSH login to connect to the server, and "tunnel" your database connection through that SSH connection. For purposes of instruction, this discussion will focus on Crystal Reports, but generally applies to any Windows software that uses ODBC to connect to data sources.

Crystal Reports only runs on Windows, and Windows has certain limitations when it comes to database server connections. For one, Windows doesn't inherently understand SSH, which is the most common way to connect directly to a Linux server. At this time, all installations of PCR-360 run on Linux servers, and require SSH access to connect to the application's database. If you're working with Crystal Reports or another Windows-based report development software, you'll need a way to help Windows access the server using SSH.

Requesting Server Access

• Hosted Customers:
  • Contact PCR Help Desk to request server and database access.
  • Once your access request is verified, PCR will provide server connection, SSH credentials, and database credentials.
• Internal (Instance) Customers:
  • Your network administrator is responsible for providing access to the_server_PCR-360 is running on. Please contact that person for assistance.
  • You will_also_need to contact PCR Help Desk to request database access. You should coordinate this request with your network administrator so all parties are fully informed of the request.
  • Once your access request is verified, PCR will provide database credentials. Your administrator is responsible for server connection and/or SSH credentials.

PuTTY and Bitvise

Bitvise SSH Client This is the tool you'll need to establish a proxy from Windows to the application/database server. A proxy accepts specific network traffic, and redirects it somewhere else. In this case, Windows can't directly send network traffic using the SSH protocol, so your database connection is going to send its traffic to Bitvise, which will in turn send it on to the server.

The Bitvise Client is free. Don't confuse it with Bitvise Server, which requires a paid license. Installation instructions are on the website to walk you through the basic setup.

Setting Up The Proxy

Create a Bitvise connection profile

Open Bitvise Client from your Start Menu. It opens to a new blank profile.

Using the server connection and SSH credentials provided to you, fill in the Login Tab. Under Authentication, choose password or publickey.

SSH Key Access

If you've been instructed to use a SSH Key to connect, you should have your private key available, and it must be in PuTTY format (has a .ppk file extension). See below for additional instruction. Once you're prepared, import your Key into the profile:

1. Click the Client Key Manager link as indicated in the illustration.

2. Click the [Import] button.

3. Provide your Key's passphrase if prompted.
4. On the import dialog, you can choose to make the key Global - it will be available for any profile you create - or Profile only.
5. Finish the import, and a row is added to your Client Key Manager. Close that form.
6. Select the imported Key ID from the list.
7. If you provide your Key's passphrase in the space provided, it may not be saved with the profile (for security reasons), meaning you will need to provide it each time you want to connect so Bitvise can unlock your Key.

Client-to-Server Forwarding

In order for Bitvise to forward your database traffic through SSH to the server, it needs to know what network port to listen to, and what port the server side is listening to.

1. On the C2S Tab of the profile, click [Add] to set up a traffic relay.

2. Enter 127.0.0.1 as the Listen Interface, localhost as the Destination Host, and the port appropriate to your database platform as the Dest. Port

   • For Hosted Customers, MySQL is configured to use 3306 - if your organization is running PCR-360 internally with MySQL, your network administrator can provide this info.

   • For Instance Customers running Oracle, the typical port is 1521 or 1526. Confirm with your network administrator what port to enter here.

3. The List. Port setting is a little bit special. For most cases, it's safe to use the same value here as the Dest. Port. Make note of the value you set here - you'll need it further down.

Save your profile.

Click the [Login] button to test your connection. If all is correct, you should see log activity without errors, and the [Login] button switches to [Logout]. That's it. The connection sits quietly and does its job. There are other functions you can perform with Bitvise, like opening a command prompt on the server you're connected to, but that's not covered here.

SSH Key Conversion (if needed)

PuTTY SSH Toolkit Download and run the PuTTY installer. If you've been instructed to use a SSH Key to access the server, you may need this to convert the key to PPK format for Bitvise (below).

If you obtained your key from your own network administrator, please confirm the key's format. To convert from OpenSSH or ssh.com format, follow these steps:

1. After installing the PuTTY Toolkit noted above, open PuTTYgen from your Start Menu
2. Open the Conversions Menu, and click Import keyfile.
3. If your key is protected with a passphrase, you'll be prompted to enter that to unlock the key.

4. PuTTYgen will load the key's signature. Click Save Private Key to save a PuTTY-formatted copy as a .ppk file. If there was a passphrase, it will be retained on the new Key file.