How to connect JTL to Taxdoo

This article describes how you can connect your JTL system with Taxdoo and thus enable us to import transaction data from it.

Note: When connecting interfaces, please ensure that we cannot import transactions twice. No marketplaces or web shops should be connected directly if the transactions contained there are also recorded in JTL.

The Amazon interface is an exception, as we import other data in addition to sales. You can find out whether you need to connect Amazon as well in our support article, Do I also need to connect Amazon in addition to my ERP system?.

If you are uncertain which interfaces should be connected, please contact us via the help center.

Guide how to connect JTL

Please take the time and carefully read our article Information on the JTL interface.

Unfortunately, JTL Wawi offers no way of accessing transaction data externally. For this reason, we export orders, refunds and purchase prices directly from the JTL database. The integration is carried out depending on where the database is located and how accessible it is.

External hosting 

If you have the JTL database hosted externally (such as with JTL Wawi database hosting or in the Ecomdata cloud), the database can also generally be accessed by us directly. Only a database user needs to be set up for us. In the case of JTL Wawi database hosting, you typically have to ask JTL support to create a user.

Note: The AWS services we use are currently only allowing connections with IPv4 addresses. You can therefore currently not establish a connection if your server can only be reached under an IPv6 address.

Self-hosted 

If you run the JTL database server yourself (in your own business premises, for example), the database needs to be made accessible for us. There are 2 ways of doing this: 

1. Make the database accessible directly, such as via an open port on the router.
2. Set up an SSH server that allows us to connect to the database. This may also require an open port on the router for the SSH server. Please note the section "Configuring the SSH server" in this regard.

In both cases, you'll either need a static IP address or you can use a service like DynDNS. Please contact your IT staff or your IT service provider to set this up.

After connecting successfully, we recommend storing the connection information in a secure place (such as a password safe) to make it easier to reconnect in the future. We store the information in encrypted form, and it can therefore not be easily accessed by our support team.

1. Requirements

• Computer with Windows 7 (SP1) or newer which also supports JTL Wawi (i.e. with a connection to the database server)
• Access data for your Microsoft SQL database server, which is also used by JTL

2. Install SQL Server Management Studio

If you have already installed SQL Server Management Studio on a PC, you can skip this step. Studio is only required for the next step, "Create database user".

1. Download the setup file using this link.
2. Open the downloaded file.
3. Click on "Install".
4. If Windows asks for confirmation, click on "Yes".
5. Wait until installation is complete.
6. Restart the PC if necessary.
7. SQL Server Management Studio is now installed. 

2.1 Optional: Find database host 

This step is only necessary if you don't know the address of your database. To find this out, open the JTL database administration from JTL Wawi: 

You can find the database address in the following field: 

3. Create database user

1. Start "Microsoft SQL Server Management Studio" (e.g. by entering the name in the search field and then selecting it).
2. Establish a connection with the database. To do so, enter the name of the server or instance into the "Server name" field and input the login data for the database administrator, for example, in the username and password fields (the same data also configured for JTL).
3. Expand the "Security" section on the left.
4. Right-click on "Registrations".
5. Select "New registration..." (as pictured):

      6. Choose "TAXDOO" as the registration name.

      7. Select "SQL server authentication".

      8. Enter a secure password and confirm (note the password). The password should not be easily              guessed (e.g. not "taxdoo123"). A random password can also be generated in common                          password managers or using https://passwordsgenerator.net/.

      9. Remove the checkmark for "Force password expiry".

      10. The current window should now look like this:

     11. Select the page "User assignment" on the left.

     12. Place a checkmark next to the JTL database (usually "eazybusiness").

     13. Place a checkmark below, next to the entry "db_datareader".

     14. You'll now see the following window:

      15. Confirm with "OK".

You have now created a database user with reading rights (no writing rights).

Here, it's possible for an error to occur where insufficient rights exist to create a user. In particular, this can occur if you use JTL Wawi hosting directly with JTL. In this case, you'll need to contact JTL support (or your hosting provider) and ask them to set up a database user with reading rights.

The following cases have occurred with JTL support so far: 

• JTL support creates a database user without any further discussion. This is the case if you use an older license model with JTL.
• JTL support offers to set up a database user for a monthly fee (previously €19.99 per month).
• JTL support refuses to set up a database user. In this case, our final option is to use your own JTL Wawi database access data.

Now enter the access data in the corresponding form in the dashboard ("Settings" > "Interfaces" > "JTL") where you also find a description of the required fields.

Configuring the SSH server (Optional)

If we are to establish a connection with the JTL database via an SSH server, it's necessary to configure the SSH server accordingly.

The setup varies depending on the server used. For Linux systems, OpenSSH is usually pre-installed. For Windows (Server), OpenSSH may be installed, for example. In the case of current versions of Windows 10 and Windows Server, you can follow this guide.

The steps involved are roughly the following:

• Create a database user for us in the SQL Server database instance (see above) 
• Install the SSH server on a PC/VM from which the SQL Server database instance is accessible (or install the SSH server directly on the database server)
• Make sure the SSH server is accessible via the Internet (e.g. via port forwarding at the router and corresponding firewall rules)
• Save our public key for a user of your choice on the PC/VM with the SSH server (or create a new user, called "taxdoo" for example)
• Determine the fingerprint of the SSH server (see below)

Fingerprint of the SSH server 

We need the "fingerprint" of the SSH server to ensure that we're connecting to the correct address.  

You can obtain the fingerprint using the following command, for example (the hostname needs to be replaced by the address of the SSH server):  

ssh-keyscan hostname 2>$null | ssh-keygen -lf -

The fingerprint needs to be entered together with the format (as outputted by the above command). Here are a couple of examples of valid fingerprints: 

• MD5:f1:6d:0c:d7:6d:35:0d:ba:7c:32:d3:5e:40:72:db:08
• SHA256:oQGbQTuNtjGeNIgh0OhcEpA/BHxcYY+NxXtt3rTxQjs=

If the output encompasses multiple fingerprints, you should keep all fingerprints to hand for connecting via our dashboard.

Public key

We will authenticate ourselves using the public/private key. The following public key therefore needs to be entered for the SSH user you enter: 

ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQC8jPWgjcx1Tktm8n3Jl36mB9aeApq+NzgddcfPYS16T9MZa9IQrYQnueiWp5A/M5PvJEYvLlm7tmkNmkHfu7ZjxjA9c7vVXxnH9D58n71TVUkpdRWYDVS94MmK92SHiH4UmWfnJm8cplDkPTU5GHvA9VNreyNWkB9L8fUDmliBBOcnbmJBq1Xqcp9f4BpYjtODTusuhfkM4l0G2gqeLkzRWwFQhpP9sedrdKsfoIyE6fnYQEiUULBpxdmXub1Epw7DNVuRo2n9lD8Db0LbHBqeb48rqQx+wIDCq0cCWwGUOGhXn1SldXL9U/fZahLJIgHcIaRAdbHdAq8ujbb6V/in taxdoo-jtl

This key generally has to be saved in the file ~/.ssh/authorized_keys.

Required information

On the form in the dashboard ("Settings" > "Interfaces" > "JTL"), the following information needs to be entered to set up the SSH connection: 

1. Address and port of the SSH server
2. Username of the SSH user
3. Fingerprint of the SSH server (hexadecimal with colons separated by MD5 or Base64 for SHA1 or SHA256).

Security

You should assume that you are immediately exposed to botnet attacks with the standard configuration of an SSH server as soon as it is accessible via the Internet. To reduce the attack surface, the following measures can be taken:

• Deactivate password-based authentication (for us only authentication via public/private key is required), since the vast majority of attacks are targeted against that.
• Change the port of the SSH server (22 as standard) to a free port not used as standard. In particular, ports not used by other applications are normally ideal. A source for this is Wikipedia.
• Prevent SSH access to users like "root" or "admin".

Unfortunately, it's hardly possible to restrict the IP address range. For the imports, we use the AWS infrastructure, which means a wide range of IP addresses is possible. The IP address ranges also change from time to time. You can find information on this here (we exclusively use infrastructure in Frankfurt / eu-central-1).

Any questions?

Please contact us via the help center.