How to connect JTL to Taxdoo

Patrick Buchhardt
  • Updated

Note: Please do not connect JTL if transactions contained in JTL are already imported via another system connected to Taxdoo.

Guide to integrating JTL 

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.

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.

Please set aside some time and read through our general information on our JTL interface: https://support.taxdoo.com/hc/de/articles/360022001851 

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.

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: https://go.microsoft.com/fwlink/?LinkID=840946
  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.

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:
  1. 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 licence 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").

Configuring the SSH server 

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: https://docs.microsoft.com/de-de/windows-server/administration/openssh/openssh_install_firstuse

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: https://docs.aws.amazon.com/de_de/general/latest/gr/aws-ip-ranges.html (we exclusively use infrastructure in Frankfurt / eu-central-1).

Alternatives to Taxdoo JTL Explorer

This section is only intended for users of our Taxdoo JTL Exporter. If you are a new client, this will not be relevant to you.

Since we intend to discontinue Taxdoo JTL Explorer as of 1 August 2019, an alternative needs to be implemented by then so that we can still access your transaction data after this date.

The following sections offer a number of alternatives. 

Switch to a hosted SQL Server database 

You can switch to a hosted solution for the SQL Server database. The following providers have special offers for JTL Wawi hosting:

Another option are also general hosting services, where you have to set up the database server and Wawi worker yourself. For example: 

Make your own database server accessible 

You can make your database server accessible via the Internet, such as using an open port on your router. For greater security, we recommend not making the database server directly available. Instead, set up an SSH server that allows us to connect to the database.

CSV upload / own API connection 

If the two options above are not possible for you, you'll need to switch to CSV uploads. Here, you'll have to define an export format in JTL and upload CSV files to our dashboard every month. Alternatively, you can also establish a connection yourself using our API (https://dev.taxdoo.com).

Comments

0 comments

Please sign in to leave a comment.

Powered by Zendesk