CODEWITHSUNDEEP
mysqltcppowerbi
Aditya Banerjee
Aditya Banerjee

Reputation: 9

Unable to Connect to MySQL local server 3306 in Power BI

I was about to start a Project using MySQL and Power BI for creating a dashboard but I faced a severe challenge. When I was going to connect MySQL database in Power BI, I faced an error like:

Microsoft SQL: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - An established connection was aborted by the software in your host machine.)"

I am not able to find any solution for this over the internet.

Upvotes: 0

Views: 2576

Answers (1)

Ashish Amar
Ashish Amar

Reputation: 11

Ok, so I had the exact same error and I got that resolved. So you'll need to ensure that the MySQL connector net is installed. And I made the mistake of importing the JAR file instead.

DO NOT DO THAT. Download and Install the msi package (If you're using windows) from this link. Connector Net Download Link.

Once that's installed.

  1. Close Power BI if it's opened. (Basically restart Power BI).
  2. Start the SQL Server. I'm using XAMPP. Check this screenshot of the XAMPP Window with the MySQL server started.
  3. Open Power BI and click on "Get Data" option. That will open up a pop up window asking you to choose all the different data sources.
  4. Now comes the 2nd important point. Ensure you select "MySQL Database". Check this image for the MySQL Data source.
  5. Click on "connect", and in the server field type in the IP address and the port number. and also specify the database name. Check this image for the input fields.
  6. Click "ok" and if all goes well, you should see all the tables under the database in the "Navigator" window. I have selected 2 tables called "city" and "sdetails" under the database "school".

And from here, you can choose to "Load" or "Transform Data" and get on with visualizing it.

Note :

1. If power BI throws up an error window asking for credentials choose "Database Credentials". By default "Windows Credentials" would be selected.

2. Here, provide the username and password for your SQL Connection. The same credentials which you would use in your code based connections.

3. For XAMPP the default username would be "root" and the password would be an empty field.

4. Save the database credentials and try connecting to the database again and it should work.

Upvotes: 1

Related Questions