ASP.NET Core MVC MySQL Server Connection

Question

I'm having issues trying to establish a connection to a server I've setup using Windows Server 2019 in ASP.NET Core MVC 3.1.

The only things I've installed on the server is the MySQL suite and IIS. This server is also being hosted on my computer locally using VirtualBox and is setup using a Bridged Adapter.

The ASP.NET Core MVC project was created using the default MVC option in Visual Studio. No third-party services are included here.

In ASP.NET Core, the connection string appears as follows:

"ConnectionStrings": {
     "ApplicationContext": "Server=<server IP/host name>;Initial Catalog=testdatabase;User ID=testuser;Password=testpassword;"
}

Upon running the project using the above Connection String, the following errors appear in my browser:

Win32Exception: The system cannot find the file specified. Unknown location

SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.) Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, uint waitForMultipleObjectsTimeout, bool allowCreate, bool onlyOneCheckConnection, DbConnectionOptions userOptions, out DbConnectionInternal connection)

However, I cannot for the life of me figure out why this doesn't work. I've looked around the internet quite a bit but don't know enough about how ASP.NET interacts with a MySQL server, and whether or not I am using incorrect parameters. I've tried using additional parameters like Trusted_Connection=True/False; and MultipleActiveResultSets=True/False; in hopes to produce any different results, but still nothing (I specifically mention these two because they come by default).

To test whether the server was the culprit, I've replicated the connection string above using PHP v7 with PDO.

<?php
$server = "<server IP/host name>";
$port = 3306;
$db = "testdatabase";
$username = "testuser";
$password = "testpassword";

try {
    $conn = new PDO("mysql:host=$server;port=$port;dbname=$db", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected";
} catch (PDOException $err) {
    echo $err;
}

if ($conn) {
    $query = "SELECT * FROM testTable";
    $result = $conn->query($query);
} else {
    echo "Query failed";
}

foreach ($entity as $result) {
    echo "<br />" . $result["Id"];
}
?>

I can confirm that this PHP code works 100%.

Additionally, I've also tested establishing a server connection using Azure MySQL and by using the generated Connection String that is created in Azure. This also works 100%.

This leads me to believe that my Windows Server is the culprit and that it could be related to one or more specific services being disabled/blocked. But at the same time, if the PHP code could establish a connection and grab data, surely it can't be caused by the Connection String code??

I think the issue could be related to the logic in my Controller code.

If anyone could provide some insight, I would greatly appreciate it. It could just be something super-duper simple.

Answer 1

I'll leave this for anyone passing by.

Tl;dr: Code in 'Startup.cs' was the driven factor causing this issue, namely the section involved with adding a database context to the application. Also important to ensure Ubuntu server is not blocked by Firewall policies as well.

Default method (using Microsoft SQL):

services.AddDbContext<DataContext>(options => options.UseSqlServer(Configuration.GetConnectionString("DataContext")));

Correct method (using Pomelo):

services.AddDbContext<DataContext>(options => options.UseMySql(Configuration.GetConnectionString("DataContext")));

Notice the difference in '.UseSqlServer()' vs '.UseMySql()'. This was because in my Ubuntu server, it was setup with MySQL as the driver and NOT an MSSQL driver. The effect of trying to use the "default method" was causing all kinds of issues in trying to establish a backend connection.

Install reference: https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql

Section below does not directly relate to the solution, but does provide insight as to how I troubleshooted this issue.

To verify this, I also had to check other factors within the Ubuntu server itself. Namely, enabling Port 3306 through the Firewall, changing the "bind-address" parameter in the "mysql.cnf" file, and of course, checking the actual version the SQL driver itself that was being used.

Enabling Port 3306: https://www.digitalocean.com/community/tutorials/how-to-allow-remote-access-to-mysql

Changing the "bind-address" parameter: https://www.cyberciti.biz/tips/how-do-i-enable-remote-access-to-mysql-database-server.html

Checking SQL driver version: https://phoenixnap.com/kb/how-to-check-mysql-version

Upon applying these changes (alongside using Pomelo's ".UseMySQL()" method), I ran my application and lo and behold, the page that involves using a database connection did not produce any errors. It simply required me to "Apply Migrations" as if I were working with a Local DB and have forgotten to update the database.

Answer 2

Since you're getting a SqlException, you must be trying to connect (to MySQL Server) with System.Data.SqlClient or Microsoft.Data.SqlClient. This isn't supported; those are only for Microsoft SQL Server.

Install MySqlConnector and use the MySqlConnection class to establish a connection. See https://mysqlconnector.net/connection-options/ for the full connection string reference, but the most simple connection string will be "Server=<server IP/host name>;Database=testdatabase;User ID=testuser;Password=testpassword;".