Document Properties
Kbid2931V6
Last Modified16-Jun-2020
Added to KB21-Jan-2020
Public AccessEveryone
StatusOnline
Doc TypeGuidelines, Concepts & Cookbooks
Product
  • ICM 7.10
  • Intershop CaaS

Guide - CaaS Database Access

1 Introduction

This document describes how to access the database (DB) in a CaaS context.

A precondition for accessing the database is that your public SSH key is added to the INT live or edit environment. This way you are able to access database either directly via command line, or, preferably and much more comfortable via SQL Management Studio or Azure Data Studio.

To do so, you need to connect to INT via SSH and establish an SSH tunnel/port forwarding to enable to access DB via a local port on your machine forwarding traffic to the remote host.

Credentials and connection information can be found here: /var/intershop/share/system/config/cluster/orm.properties.

Note

It is sufficient to establish an SSH tunnel/port forwarding to either INT (LV) or INT (ED) in order to be able to access DB as the same physical DB machine operates in the background where databases are located.

Also note that a connection via SSH can only be established if the originating public IP address, where attempting to access from, is included on the whitelist.

1.1 References

1.2 Prerequsites

The easiest way to establish an SSH tunnel/port forwarding is via a Linux-like shell using OpenSSH client. This is a built-in functionality in Linux- and Mac-based operating systems and can also be used in Windows, provided WSL (Windows Subsystem for Linux) is configured and a Linux OS image is installed.
For more information on installing WSL and Linux on Windows (10), refer to the official Microsoft documentation.

If going for this option, there is just one command line needed to achieve SSH tunnel/port forwarding, see section 2.2.11.

Another common option, working without Linux and/or WSL is using PuTTY.

The following tools are required (using PuTTY with Windows operating system):

  • PuTTY

  • Pageant

  • Plink

Tip

You can use the following link to download „Package files“, containing all necessary tools: https://www.chiark.greenend.org.uk/~sgtatham/putty/latest.html.

2 Establish an SSH Tunnel

To create an SSH tunnel to a database using PuTTY (e.g. any appserver, port: 1433) follow the instructions in the next subsections.
It is possible to use this tunnel to connect to the database via SQL Management Studio/Azure Data Studio from your local machine.

2.1 Adding Your SSH Private Key to Pageant

Before creating a new session, your SSH key needs to be added to Pageant. To do so, perform the following steps:

  1. In your task bar right-click on the Pageant symbol.

  2. Click Add Key and select your SSH private key file.

    Note

    If you do not already have a file, use the puttygen tool to create one and send the public key to the Intershop Services Operations team. Please contact the dedicated project manager.

2.2 Creating an SSH Tunnel Using PuTTY

Creating an SSH tunnel using PuTTY consists of the following substeps:

  • Creating a new session
  • Specifying the username
  • Adding the Jump Server as a proxy
  • Setting the authentication method
  • Adding a tunnel to the destination port

More details on how to perform these steps are provided in the instructions below. Firstly, a new session needs to be created in PuTTY. To do so, perform the following steps:

  1. Open PuTTY.

  2. Go to Session.

  3. As Host Name (or IP address) specify the IP address of the SSH server. It follows the pattern:  ish<XX>-<YYYY>-webappsolr<ZZZZZZ>. As Port, enter 22.



    In addition to the creation of the session it is necessary to set the username "intershop". Therefore proceed as follows:

  4. Go to Connection | Data.
  5. Enter "intershop" as Auto-login username.
    The terminal details will appear automatically.


    In the next step, add the jump server as a proxy by doing the following:

  6. Go to Conncetion | Proxy.
  7. Enter the following in the respective fields and select the appropriate radio buttons:

    SettingData
    Proxy typeLocal
    Proxy hostnameishXXservice.<AAAA>.cloudapp.azure.com
    Port22
    Usernameintershop
    Telnet command, or local proxy commandplink.exe -2 %user@%proxyhost -nc %host:%port

    Furthermore, enable the checkbox Consider proxying local host connections.

    Afterwards, the authentication method needs to be set. Therefore proceed as follows:

  8. Go to Connection | SSH | Auth.

  9. Enable the checkbox Allow agent forwarding.
    Allow_agent_forwarding_checkbox

    Note

    You do not need to select a key as the key has already been added to Pageant, see Adding Your Private SSH Key to Pageant.


    Finally, a tunnel can be added to the destination port by following the instructions below:

  10. Go to Connection | SSH | Tunnels.
  11. Provide the following data:
    The Source port can be any available local port, e.g. 1433, 14333, 2000, 43423, 34567, ...
    The Destination port should be: ishXX-pre-db01:1433


Tip

Using a Linux-based operating system and openssh-based command line client client makes establishing SSH Tunnel/port forwarding even easier.

According to the example(s) above this would look like:

Command Line
ssh -A intershop@ish<XX>-<YYYY>-webappsolr<ZZZZZZ> -o GSSAPIAuthentication=no -o ProxyCommand='ssh -W %h:%p intershop@ishXXservice.<AAAA>.cloudapp.azure.com' -L 14333:ishXX-pre-db01:1433

You can now start using SQL Management Studio/Azure Data Studio connecting to "127.0.0.1, 14333" (or an equivalent port as of your choice, see above) using the credentials you gathered from /var/intershop/share/system/config/cluster/orm.properties on INT (LV) and/or INT (ED).

Note

Please use 127.0.0.1 in SQL Management Studio as server name. Do not use "localhost" as this might lead to an error. Also keep in mind that port needs to be comma-separated in this configuration field.

Disclaimer

The information provided in the Knowledge Base may not be applicable to all systems and situations. Intershop Communications will not be liable to any party for any direct or indirect damages resulting from the use of the Customer Support section of the Intershop Corporate Web site, including, without limitation, any lost profits, business interruption, loss of programs or other data on your information handling system.

Customer Support
Knowledge Base
Product Resources
Support Tickets