XtremeData dbX Remote Connectivity
PDF and Video Tutorials are available here:
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.
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.
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
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
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
- 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
- Change to Category: Connection: SSH: Auth
- Select Allow agent forwarding
- Private key file for authentication: Browse and choose your private key generated earlier.
- 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.
- 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
- 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.
- 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
NOTE: This process needs to be repeated every time the PC is rebooted.
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]
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
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.
- 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
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 This email address is being protected from spambots. You need JavaScript enabled to view it.
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.
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]
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.
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.
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
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]
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]