[pb_row ][pb_column span="span3"][pb_text el_title="Remote Connectivity r2.2" width_unit="%" enable_dropcap="no" appearing_animation="0" ]

Proof-of-Concept (POC)
Remote Connectivity r2.2
September 2016

[/pb_text][pb_button el_title="Back" button_text="<<< Back" link_type="url" button_type_url="https://xtremedata.com/support/tutorials" open_in="current_browser" div_margin_top="5" div_margin_bottom="15" button_alignment="center" button_size="btn-xs" button_color="btn-default" appearing_animation="0" ][/pb_button][/pb_column][pb_column span="span9"][pb_text el_title="XtremeData dbX Remote Connectivity" width_unit="%" enable_dropcap="no" appearing_animation="0" ]

XtremeData dbX Remote Connectivity

[/pb_text][pb_text el_title="Steps for remote connectivity" width_unit="%" enable_dropcap="no" appearing_animation="0" ]

This document describes the steps for remote connectivity to a dbX appliance on XtremeData’s premises. Many of these steps apply to remote access on any dbX cluster. You will first have to connect through our firewall and then connect to the database.

[/pb_text][/pb_column][/pb_row][pb_row ][pb_column span="span3"][pb_text el_title="Contents" width_unit="%" enable_dropcap="no" appearing_animation="0" ]

Contents:

[/pb_text][pb_button el_title="Connecting to an XtremeData Cluster from a Windows PC" button_text="Connecting to an XtremeData
Cluster from a Windows PC" link_type="url" button_type_url="#Connecting-Windows-PC" open_in="current_browser" button_alignment="left" button_size="default" button_color="btn-danger" appearing_animation="0" ][/pb_button][pb_button el_title="Download the PuTTY software" button_text="Download the PuTTY software" link_type="url" button_type_url="#Download_PuTTY" open_in="current_browser" div_margin_top="5" button_alignment="left" button_size="default" button_color="btn-default" appearing_animation="0" ][/pb_button][pb_button el_title="Generate public/private Keys" button_text="Generate public/private Keys" link_type="url" button_type_url="Generate_public" open_in="current_browser" div_margin_top="5" button_alignment="left" button_size="default" button_color="btn-default" appearing_animation="0" ][/pb_button][pb_button el_title="Send public key to XtremeData" button_text="Send public key to XtremeData" link_type="url" button_type_url="#Send_public_key" open_in="current_browser" div_margin_top="5" button_alignment="left" button_size="default" button_color="btn-default" appearing_animation="0" ][/pb_button][pb_button el_title="Configure Your PuTTY Session" button_text="Configure Your PuTTY Session" link_type="url" button_type_url="#Configure_PuTTY" open_in="current_browser" div_margin_top="5" button_alignment="left" button_size="default" button_color="btn-default" appearing_animation="0" ][/pb_button][pb_button el_title="OPTIONAL: Loading the
Private Key into PuTTY Ageant" button_text="OPTIONAL: Loading the
Private Key into PuTTY Ageant" link_type="url" button_type_url="#OPTIONAL_Loading" open_in="current_browser" div_margin_top="5" button_alignment="left" button_size="default" button_color="btn-default" appearing_animation="0" ][/pb_button][pb_button el_title="Start Your Session" button_text="Start Your Session" link_type="url" button_type_url="#Start_Session" open_in="current_browser" div_margin_top="5" button_alignment="left" button_size="default" button_color="btn-default" appearing_animation="0" ][/pb_button][pb_button el_title="Connecting to an XtremeData Cluster from a Mac" button_text="Connecting to an XtremeData
Cluster from a Mac" link_type="url" button_type_url="#Connecting_XtremeData" open_in="current_browser" button_alignment="left" button_size="default" button_color="btn-danger" appearing_animation="0" ][/pb_button][pb_button el_title="Create a Public/private key pair" button_text="Create a Public/private key pair" link_type="url" button_type_url="#Create_key_pair" open_in="current_browser" div_margin_top="5" button_alignment="left" button_size="default" button_color="btn-default" appearing_animation="0" ][/pb_button][pb_button el_title="Send the public key to XtremeData" button_text="Send the public key to XtremeData" link_type="url" button_type_url="#Send_public_key_XtremeData" open_in="current_browser" div_margin_top="5" button_alignment="left" button_size="default" button_color="btn-default" appearing_animation="0" ][/pb_button][pb_button el_title="Terminal Access" button_text="Terminal Access" link_type="url" button_type_url="#Terminal_Access" open_in="current_browser" div_margin_top="5" button_alignment="left" button_size="default" button_color="btn-default" appearing_animation="0" ][/pb_button][pb_button el_title="Accessing the Database" button_text="Accessing the Database" link_type="url" button_type_url="#Accessing_Database" open_in="current_browser" button_alignment="left" button_size="default" button_color="btn-danger" appearing_animation="0" ][/pb_button][pb_button el_title="XdQry" button_text="XdQry" link_type="url" button_type_url="#XdQry" open_in="current_browser" div_margin_top="5" button_alignment="left" button_size="default" button_color="btn-default" appearing_animation="0" ][/pb_button][pb_button el_title="ODBC/JDBC" button_text="ODBC/JDBC" link_type="url" button_type_url="#ODBC_JDBC" open_in="current_browser" div_margin_top="5" button_alignment="left" button_size="default" button_color="btn-default" appearing_animation="0" ][/pb_button][pb_button el_title="dbX Client Utilities" button_text="dbX Client Utilities" link_type="url" button_type_url="#dbX_Client" open_in="current_browser" div_margin_top="5" button_alignment="left" button_size="default" button_color="btn-default" appearing_animation="0" ][/pb_button][pb_button el_title="xdudb sql" button_text="xdudb sql" link_type="url" button_type_url="#xdudb_sql" open_in="current_browser" div_margin_top="5" button_alignment="left" button_size="default" button_color="btn-default" appearing_animation="0" ][/pb_button][/pb_column][pb_column span="span9"][pb_text el_title="Steps for remote connectivity VIDEO" width_unit="%" enable_dropcap="no" appearing_animation="0" ]

PDF and Video Tutorials are available here:

pdfXtremeData dbX Remote Connectivity (PDF Download)

[/pb_text][pb_divider el_title="Divider 1" div_margin_top="30" div_margin_bottom="30" div_border_width="1" div_border_style="solid" div_border_color="#E0DEDE" appearing_animation="0" ][/pb_divider][pb_text el_title="Connecting to an XtremeData Cluster from a Windows PC" width_unit="%" enable_dropcap="no" appearing_animation="0" ]

Connecting to an XtremeData Cluster from a Windows PC 

Access to the dbX appliance is through public/private key authentication. Mail your public key to XtremeData. Once received, XtremeData will setup the key for authentication and provide access to a dbX appliance. If you have an existing public/private key with SSH2-RSA authentication, you can use that and skip to step c). If you do not have a public/private key, you can generate one using the free “PuTTYgen” program.

[/pb_text][pb_text el_title="Download the PuTTY software" width_unit="%" enable_dropcap="no" appearing_animation="0" ]

a)    Download the PuTTY software

  • Download the PuTTY from http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html.
  • After navigating to the page, download putty--installer.msi.
  • Once the file is downloaded, install the programs by double-clicking the msi file.
  • When finished you should have new Icons on your desktop including putty.exe, pageant.exe, and puttygen.exe.

[/pb_text][/pb_column][/pb_row][pb_row ][pb_column span="span3"][/pb_column][pb_column span="span5"][pb_text el_title="Generate public/private Keys" width_unit="%" enable_dropcap="no" appearing_animation="0" ]

b)    Generate public/private Keys   

  • Double-click the puttygen.exe icon. The PuTTY Key Generator window will appear.
  • Under Parameters at the bottom make sure SSH-2 RSA is selected and that the number of bits is set to 1024.
  • Click the Generate button and move the cursor in the empty box until the green bar is full.
  • Key comment: [your_email_address]
  • Key passphrase: [key password]
    Confirm passphrase: [key password]
    This password is needed to login to dbX unless the key has been loaded into the pageant program as described later in this document.
  • Select Save private key and save as [your_name]_private_key.pub
    Example: acme_private_key.pub

[/pb_text][/pb_column][pb_column span="span4"][pb_text el_title="Puttygen Key" width_unit="%" enable_dropcap="no" appearing_animation="0" ]

[/pb_text][/pb_column][/pb_row][pb_row ][pb_column span="span3"][/pb_column][pb_column span="span9"][pb_text el_title="c) Send public key to XtremeData" width_unit="%" enable_dropcap="no" appearing_animation="0" ]

c)    Send public key to XtremeData  

Mail the public key to XtremeData. The private key should remain under your control and should only be released to trusted partners. The private key and the key password ensure security. Note that every time you login to the dbX appliance, you will need to authenticate using your private key and passphrase (key password).

XtremeData will install your public key on the dbX appliance and will provide you with the following connectivity information for the appliance. Once you receive this information, you are ready to login. Examples are provided for clarity.

  • ssh host: office.xtremedatainc.com
  • ssh Firewall Port: 2374
  • ssh Appliance User: poc567hr ← “hr” name initials distinguish between different users if necessary
  • ssh Appliance password: [appliance_password]
  • dbX user: poc567hr
  • dbX password: [dbx_password]
  • dbX server name: poc567
  • dbX server port: 7555
  • dbX database name: testdb

[/pb_text][pb_text el_title="Generate public/private Keys" width_unit="%" enable_dropcap="no" appearing_animation="0" ]

b)    Generate public/private Keys   

  • Double-click the puttygen.exe icon. The PuTTY Key Generator window will appear.
  • Under Parameters at the bottom make sure SSH-2 RSA is selected and that the number of bits is set to 1024.
  • Click the Generate button and move the cursor in the empty box until the green bar is full.
  • Key comment: [your_email_address]
  • Key passphrase: [key password]
    Confirm passphrase: [key password]
    This password is needed to login to dbX unless the key has been loaded into the pageant program as described later in this document.
  • Select Save private key and save as [your_name]_private_key.pub
    Example: acme_private_key.pub

[/pb_text][/pb_column][/pb_row][pb_row ][pb_column span="span3"][/pb_column][pb_column span="span5"][pb_text el_title="d) Configure Your PuTTY Session" width_unit="%" enable_dropcap="no" appearing_animation="0" ]

  • Open the putty.exe desktop icon
  • Change to Category: Connection: Data
  • Auto-login username: poc567hr [ssh Appliance User]
  • Leave all other default options as shown

[/pb_text][/pb_column][pb_column span="span4"][pb_text el_title="SSH PuTTY session" width_unit="%" enable_dropcap="no" appearing_animation="0" ]

[/pb_text][/pb_column][/pb_row][pb_row ][pb_column span="span3"][/pb_column][pb_column span="span5"][pb_text el_title="Change to Category: Connection: SSH: Auth" width_unit="%" enable_dropcap="no" appearing_animation="0" ]

  • Change to Category: Connection: SSH: Auth
  • Select Allow agent forwarding
  • Private key file for authentication: Browse and choose your private key generated earlier.

[/pb_text][/pb_column][pb_column span="span4"][pb_text el_title="SSH PuTTY session 2" width_unit="%" enable_dropcap="no" appearing_animation="0" ]

[/pb_text][/pb_column][/pb_row][pb_row ][pb_column span="span3"][/pb_column][pb_column span="span5"][pb_text el_title="Change to Category: Connection: SSH: Tunnels" width_unit="%" enable_dropcap="no" appearing_animation="0" ]

  • Change to Category: Connection: SSH: Tunnels
  • Source Port: 2400
  • Destination: localhost:2400
  • Click Add to save the entry.
  • Forwarded ports: The new entry should appear in the box.

[/pb_text][/pb_column][pb_column span="span4"][pb_text el_title="SSH PuTTY session 3" width_unit="%" enable_dropcap="no" appearing_animation="0" ]

[/pb_text][/pb_column][/pb_row][pb_row ][pb_column span="span3"][/pb_column][pb_column span="span5"][pb_text el_title="Change to Category: Connection: SSH: Tunnels" width_unit="%" enable_dropcap="no" appearing_animation="0" ]

  • Change to Category: Connection: SSH: Tunnels
  • Source Port: 7555 [dbX server port]
  • Destination: localhost:7555 (Click Add to save the entry.)
  • Forwarded ports: The new entry should appear in the box

[/pb_text][/pb_column][pb_column span="span4"][pb_text el_title="SSH PuTTY session 4" width_unit="%" enable_dropcap="no" appearing_animation="0" ]

[/pb_text][/pb_column][/pb_row][pb_row ][pb_column span="span3"][/pb_column][pb_column span="span5"][pb_text el_title="Change to Category: Session" width_unit="%" enable_dropcap="no" appearing_animation="0" ]

  • Change to Category: Session
  • Host Name (or IP Address): office.xtremedatainc.com [ssh host]
  • Port : 2374 [ssh Firewall Port]
  • Connection type: SSH
  • Saved Sessions: XtremeData-dbX (or any name for the session you prefer)
  • Select Save. The new session should appear in the box below.

[/pb_text][/pb_column][pb_column span="span4"][pb_text el_title="SSH PuTTY session 5" width_unit="%" enable_dropcap="no" appearing_animation="0" ]

[/pb_text][/pb_column][/pb_row][pb_row ][pb_column span="span3"][/pb_column][pb_column span="span9"][pb_text el_title="e) OPTIONAL: Loading the Private Key into PuTTY Pageant" width_unit="%" enable_dropcap="no" appearing_animation="0" ]

e)    OPTIONAL: Loading the Private Key into PuTTY Pageant  

If you want to avoid typing in the PuTTY key password every time a new session is opened, then you can load your private key into a PuTTY pageant.

[/pb_text][/pb_column][/pb_row][pb_row ][pb_column span="span3"][/pb_column][pb_column span="span5"][pb_text el_title="e) OPTIONAL: Loading the Private Key into PuTTY Ageant" width_unit="%" enable_dropcap="no" appearing_animation="0" ]

  • Double-click the pageant.exe desktop icon
    Nothing will appear to happen
  • In the icon tray of the menu bar select Show hidden icons
  • Right-click the Pageant icon with a terminal and a fedora
  • Select Add Key
  • File name: path to your private key
    c:acme_private_key.ppk
  • Select Open
  • You will be prompted for your PuTTY key password
  • To verify the key is loaded select Show hidden icons in the Pageant icon tray of the menu bar
  • Right-click the icon with a terminal and fedora
  • Select Views Keys
    Your ssh-rsa key should appear in the window

[/pb_text][/pb_column][pb_column span="span4"][pb_text el_title="PuTTY_Ageant.jpg" width_unit="%" enable_dropcap="no" appearing_animation="0" ]

PuTTY Ageant

[/pb_text][/pb_column][/pb_row][pb_row ][pb_column span="span3"][/pb_column][pb_column span="span9"][pb_text el_title="NOTE: This process needs to be repeated every time the PC is rebooted." width_unit="%" enable_dropcap="no" appearing_animation="0" ]

NOTE: This process needs to be repeated every time the PC is rebooted.

[/pb_text][/pb_column][/pb_row][pb_row ][pb_column span="span3"][/pb_column][pb_column span="span5"][pb_text el_title="F) Start Your Session" width_unit="%" enable_dropcap="no" appearing_animation="0" ]

f)    Start Your Session 

  • In the Putty Configuration window Load the saved session if it is not already loaded
  • Select Open

If you have not loaded your private key in the PuTTY ageant then you will be prompted for the key password.

  • A terminal window should open.
  • You will be prompted to accept the addition of a new ssh entry.  Select Yes
  • You can now get access to the database using either the dbX command line tool: $ xdudb sql poc567 testdb
  • Or by tunneling to the server in a web browser:

https://localhost:2400/xdadm
login: poc567hr
password: [appliance_password]

[/pb_text][/pb_column][pb_column span="span4"][pb_text el_title="PuTTY_Ageant.jpg" width_unit="%" enable_dropcap="no" appearing_animation="0" ]

[/pb_text][/pb_column][/pb_row][pb_row ][pb_column span="span3"][/pb_column][pb_column span="span9"][pb_divider el_title="Divider 1 2" div_margin_top="30" div_margin_bottom="30" div_border_width="1" div_border_style="solid" div_border_color="#E0DEDE" appearing_animation="0" ][/pb_divider][pb_text el_title="2. Connecting to an XtremeData Cluster from a Mac" width_unit="%" enable_dropcap="no" appearing_animation="0" ]

2. Connecting to an XtremeData Cluster from a Mac  

Connecting a Mac is accomplished via an SSH terminal.

a)    Create a Public/private key pair  

  • Spotlight Search → terminal (terminal window opens)
  • macuser$ ssh-keygen
    Generating public/private rsa key pair.
  • Enter file in which to save the key (/Users/macuser/.ssh/id_rsa): /Users/macuser/.ssh/acme_id_rsa
    Created directory '/Users/macuser/.ssh'.
  • Enter passphrase (empty for no passphrase): [choose key_password]
  • Enter same passphrase again: [key_password]

Your identification has been saved in /Users/macuser/.ssh/acme_id_rsa.
Your public key has been saved in /Users/macuser/.ssh/acme_id_rsa.pub.
The key fingerprint is:

SHA256:ptej1ti+RHJj3FGR4N8kfnQx2i/Mrcf0Q macuser@maccomputer

The key's randomart image is:

+---[RSA 2048]----+
|                 |
|                 |
|             E...|
|            ,.*o*|
|        So= .+ B=|
|       o.-++o o.=|
|      . .*=+.ooo.|
|       .+o=o+* ..|
|       ...+_o.=. |
+----[SHA256]-----+

  • macuser$ ssh-add
  • Enter passphrase for /Users/macuser/.ssh/acme_id_rsa: [key_password]
    Identity added: /Users/macuser/.ssh/acme_id_rsa (/Users/macuser/.ssh/acme_id_rsa)
  • macuser$ cd .ssh
    macuser$ ls
    acme_id_rsa          acme_id_rsa.pub

[/pb_text][pb_text el_title="b) Send the public key to XtremeData" width_unit="%" enable_dropcap="no" appearing_animation="0" ]

b)    Send the public key to XtremeData  

  • Send the acme_id_rsa.pub portion of the key to XtremeData.  The acme_id_rsa is your private portion of the key and should only be transmitted to trusted entities over secure channels.
  • XtremeData will set up the dbX appliance with your public key and will provide you with the following connectivity information for the appliance.  Once you receive the information, you are ready to login. Examples are provided for clarity.

[/pb_text][/pb_column][/pb_row][pb_row ][pb_column span="span4"][/pb_column][pb_column span="span6"][pb_text el_title="ssh host: office.xtremedatainc.com" width_unit="%" enable_dropcap="no" appearing_animation="0" ]

  • ssh host: office.xtremedatainc.com
  • ssh Firewall Port: 2374
  • ssh Appliance User: poc567hr
  • ssh Appliance password: [appliance_password]
  • dbX user: poc567hr
  • dbx password: [dbx_password]
  • dbX server name: poc567
  • dbX server port: 7555
  • dbX database name: testdb
    dbX server port: A four digit number identifying the port which connects to the database server on the XDI cluster
    ssh Appliance User: The linux user on the XDI cluster which has been granted database access privileges

[/pb_text][/pb_column][pb_column span="span2"][/pb_column][/pb_row][pb_row ][pb_column span="span3"][/pb_column][pb_column span="span9"][pb_text el_title="c) Terminal Access" width_unit="%" enable_dropcap="no" appearing_animation="0" ]

c)    Terminal Access  

you can gain access using the following command:
macuser$ ssh -A -p [dbX server port] -L 2400:localhost:2400 [ssh Appliance User]@[ssh host]

Example :
macuser$ ssh -A -p 7555 -L 2400:localhost:2400 poc567hr@office.xtremedatainc.com
poc567@xdiserver $
Access has been granted when the linux prompt appears as shown above.  You can now run the xdudb sql command line tool from this terminal.

[/pb_text][/pb_column][/pb_row][pb_row ][pb_column span="span3"][/pb_column][pb_column span="span9"][pb_divider el_title="Divider 1 2 3" div_margin_top="30" div_margin_bottom="30" div_border_width="1" div_border_style="solid" div_border_color="#E0DEDE" appearing_animation="0" ][/pb_divider][pb_text el_title="3. Accessing the Database" width_unit="%" enable_dropcap="no" appearing_animation="0" ]

3. Accessing the Database  

Once the connection is established through the firewall, you can access the database in one of four ways.  You will need the following information which should have been provided by XtremeData.  Examples are shown in blue.

  • dbX server name: poc567
  • dbx server port: 7555
  • dbx database name: testdb
  • dbx user: poc567hr
  • dbx password: [dbx_password]

[/pb_text][/pb_column][/pb_row][pb_row ][pb_column span="span3"][/pb_column][pb_column span="span5"][pb_text el_title="a) XdQry" width_unit="%" enable_dropcap="no" appearing_animation="0" ]

a) XdQry  

XdQry is dbX’s browser based query tool.  

  • Start a browser and go the following URL:
    https://localhost:2400/xdqry
  • Server Name: poc567
    [dbx server name]
  • Database Name: testdb
    [dbx database name]
  • Role Name: poc567hr
    [dbx User]
  • Password: [dbx_password]
  • Select Sign In
    You can now query the database.

[/pb_text][/pb_column][pb_column span="span4"][pb_text el_title="xdQry.jpg" width_unit="%" enable_dropcap="no" appearing_animation="0" ]

[/pb_text][/pb_column][/pb_row][pb_row ][pb_column span="span3"][/pb_column][pb_column span="span9"][pb_text el_title="b) ODBC/JDBC" width_unit="%" enable_dropcap="no" appearing_animation="0" ]

b) ODBC/JDBC  

dbX can connect to any third party SQL query tool that supports ODBC or JDBC.  You will need to install the ODBC drivers or JDBC drivers, and then configure the connection.

ODBC drivers can be found at — http://ftp.postgresql.org/pub/odbc/versions/msi/psqlodbc_08_04_0200.zip.

[/pb_text][/pb_column][/pb_row][pb_row ][pb_column span="span3"][/pb_column][pb_column span="span5"][pb_text el_title="Configure ODBC:" width_unit="%" enable_dropcap="no" appearing_animation="0" ]

Configure ODBC:

  • Data Source: dbX
  • Database: testdb
    [dbX database name]
  • Server: localhost
  • Port: 7555
    [dbX server port]
  • User Name: poc567hr
    [dbX User]
  • Password: [dbx_password]
    SSL Mode: disable

[/pb_text][/pb_column][pb_column span="span4"][pb_text el_title="Configure_ODBC.jpg" width_unit="%" enable_dropcap="no" appearing_animation="0" ]

[/pb_text][/pb_column][/pb_row][pb_row ][pb_column span="span3"][/pb_column][pb_column span="span9"][pb_text el_title="JDBC drivers can" width_unit="%" enable_dropcap="no" appearing_animation="0" ]

JDBC drivers can be found at: http://jdbc.postgresql.org/download/postgresql-8.2-512.jdbc3.jar
Class name is: org.postgresql.Driver  

To connect using JDBC, use the following syntax:

    jdbc:postgresql://localhost:[dbX server port]/[dbX database name]

c)    dbX Client Utilities  

dbX uses the same client utilities as Postgres.  You can use the pgAdmin utility to connect to dbX.  See pgAdmin documentation for configuring the utility.

pgAdmin can be downloaded from http://www.pgadmin.org/.

d)    xdudb sql  
“xdudb sql” is the command line utility on dbX which can be accessed via a terminal login as shown in the procedures above.  This option is for advanced users only and requires knowledge of Linux.

poc567hr $  xdudb sql [dbX server name] [dbX database name]

Example:
poc567hr $ xdudb sql poc567 testdb
Welcome to sqldbx, the interactive SQL terminal, for dbX 4.0.100.
Type:  copyright for distribution terms
       h for help with SQL commands
       ? for help with psql commands
       g or terminate with semicolon to execute query
       q to quit
testdb=# [type query here]

[/pb_text][/pb_column][/pb_row]