Azure Data Factory - How to create a Connection to Mysql Clients Database

Question

Hi, I am new to ADF and I am trying to connect to client's On Prem MySQL database. I am trying to connect Link Service and I need to provide below information to make the connection. However I can see only UserName , Password m port and database name to put as Linked Service connection. How can i give below details to create a link service ? I can connect to the Database via MySQL Workbench so there's no access issues

 Connection Method : Standard TCP/IP over SSH 
 Parameters 
	SSH Host Name
	SSH User Name 
	SSH Password 
	MySQL Host Name 
	MySQL Server Port 
	User Name and Password
	Default Schema 

Answer 1

Hello @Mark Welcome to the Microsoft Q&A and thank you for posting your questions here. You were asking on how to provide credential information to connect Link Service connection to your MySQL client Database. Firstly, you will need to ensure a Self-Hosted Integration Runtime (IR) is Installed on a machine that can access both the On-Premises MySQL database and Azure Data Factory. Since you're using PowerShell, here is a sample code:

$SSHHostName = "your_ssh_host_name"
$SSHUserName = "your_ssh_user_name"
$SSHPass = ConvertTo-SecureString "your_ssh_password" -AsPlainText -Force
$MySQLHostName = "your_mysql_host_name"
$MySQLPort = "your_mysql_server_port"

$secpasswd = ConvertTo-SecureString "your_mysql_user_password" -AsPlainText -Force
$mycreds = New-Object System.Management.Automation.PSCredential ("your_mysql_username", $secpasswd)

# Create SSH Tunnel
ssh -L 3306:$MySQLHostName:$MySQLPort -l $SSHUserName -pw $SSHPass $SSHHostName

Also, ensure you modify all the answers in code to suite your configuration and make sure the script is running continuously as long as you need the connection. I hope this is helpful! Do not hesitate to let me know if you have any other questions. Please remember to "Accept Answer" if answer helped, so that others in the community facing similar issues can easily find the solution. Best Regards, Sina Salam

Answer 2

Hi

Thank you for reaching out and clarifying your steps.

With what you have done, you can utilize the JSON.

However, in assumption that you want to use this for running data movement, external and pipeline activities in an on-premises / private network by installing the integration runtime and to connect to a MySQL database from Azure Data Factory, you can follow these steps to configure a New Linked Service:

1. In the Azure Data Factory portal, go to the Manage tab.
2. Navigate to the Link Service and click on the "+" button to create a new linked service.
3. In the New Link Service, select Database Tab, Search and select "MySQL" as your linked service, then click continue.
4. In the Connect via integration runtime. Click the drop down and select + sign to create New integration runtime.
5. In the Integration Runtime setup. Chose Self Hosted, if MySQL reside on your premise. Otherwise, you chose Azure if it's hosted on Azure or External if hosted in another platform. Then, click Continue by the down left corner.
6. The provide Integration runtime setup Name, type something in the Description box and click Create.
7. In the Setting Tab, download and install on MySQL system. Also copy the keys Option 2: Manual setup Step 1: Download and install integration runtime. Step 2: Use this key to register your integration runtime. NOTE: But Express setup is faster and reduce issues with file corruption
8. Click close button, then Integration page will show error if you select the Integration name you created. as shown below. So, click on Edit button. In the Edit Integration runtime. Select Apply button as shown below: NOTE: Once your linked service it is online. There will CONNECTION.
9. Then, Fill in the required information suitable for your configurations as shown below: NOTE: Get ready the below information and don't throw it away.

• Linked service name: Provide a name for your linked service.
• Host: Enter the host name or IP address of your MySQL server.
• Port: Specify the port number (default is 3306).
• Database: Enter the name of the database you want to connect to.
• Authentication Type: Choose the authentication method (Username/Password).
• Username: Provide the MySQL username.
• Password: Provide the MySQL password.

I hope this is helpful! Do not hesitate to let me know if you have any other questions. Please remember to "Accept Answer" if answer helped, so that others in the community facing similar issues can easily find the solution.

Best Regards,

Sina Salam