Connecting DB with SSH Tunnel to PowerBI

Hello everyone! 

It turned out that PowerBI does not have a built-in ability to configure access to a database protected by an SSH tunnel. We have to get out. This article helped me a lot - thank you kind and competent person in writing instructions, without you I would have fallen into despair. 

And yet, not all the nuances are revealed in it. I'll add the following in my post:

• Two unique tips on how to prevent the established tunnel from crashing after authorization

  
  
  
  
  

• Additional instructions for connecting to SSH using a private key, not a username and password

  
  
  
  
  

• Screenshots from PowerBI itself with setting up the database and tips on how fetching from a connected database works and how to update data obtained from SQL queries.

  
  
  
  
  

• Plus, I focus my material on product analysts and data analysts, that is, those who do not have access to edit the database and who may not know what port forwarding and SSH tunnel are in principle.

  
  
  
  
  

So let's go.

You will need (preparation stage):

1. Installed Putty. You can take it here - https://www.putty.org/

   
   
   
   
   

2. Data from your backend or devops according to the list:

   
   
   
   
   

   1. SSH server IP address;

      
      
      
      
      

   2. SSH server port;

      
      
      
      
      

   3. username to access the SSH server;

      
      
      
      
      

   4. password for access or a bunch of private and public keys *

      
      
      
      
      

   5. The IP address of the database itself (usually 127.0.0.1);

      
      
      
      
      

   6. the port of the database itself;

      
      
      
      
      

   7. name of the database;

      
      
      
      
      

   8. ( , username SSH-);

      
      
      
      
      

   9. .

      
      
      
      
      

_{* , , , - . . , , , . , .}

SSH-

1. Putty

   
   
   
   
   

2. Category/Session IP- SSH-, SSH- - Close window on exit Never

   
   
   
   
   

   

3. Category/Connection/SSH Don’t start a shell or command at all

   
   
   
   
   

   

    

   
   
   
   
   

4. Category/Connection/SSH/Tunnels, Source port , Destination IP- : . Add.

   
   
   
   
   

   

5. _{* , , , 6}

   
   
   
   
   

   1. PuttyGen ( Putty)

      
      
      
      
      

   2. Conversions/Import Key

      
      
      
      
      

   3. , ( 2d “ ”) . Windows . , , “ ”

      
      
      
      
      

      

   4. Save private key. .

      
      
      
      
      

   5. Putty. Category/Connection/SSH/Auth Browse Private key file for authentication

      
      
      
      
      

      

   6. 5d .

      
      
      
      
      

6. Category/Session, Saved Session (), Save. . Open

   
   
   
   
   

   

7. Login as username SSH- Enter

   
   
   
   
   

   

8. _{* , , , 9.} 

   
   
   
   
   

   1. SSH- Enter

      
      
      
      
      

      

PowerBI

SSH- , Putty. PowerBI. “ MySQL” “ PostgreSQL” , . , - , MySQL PostgreSQL . , . 

1. IP- :  

   
   
   
   
   

2. 
   
   
   
   

3. “ ” SQL , . , PowerBI , ( , , )

   
   
   
   
   

4. ok

   
   
   
   
   

   

5. ,

   
   
   
   
   

   

6. , ok

   
   
   
   
   

   

PowerBI

1. SSH- Putty

   
   
   
   
   

2. PowerBI .