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.
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.
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):
You can use the following link to download „Package files“, containing all necessary tools: https://www.chiark.greenend.org.uk/~sgtatham/putty/latest.html.
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.
Before creating a new session, your SSH key needs to be added to Pageant. To do so, perform the following steps:
In your task bar right-click on the Pageant symbol.
Click Add Key and select your SSH private key file.
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.
Creating an SSH tunnel using PuTTY consists of the following substeps:
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:
Go to Session.
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.
Enter the following in the respective fields and select the appropriate radio buttons:
|Telnet command, or local proxy command||plink.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:
Go to Connection | SSH | Auth.
Enable the checkbox Allow agent forwarding.
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:
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
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:
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).
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.
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.