Search code examples
asp.netasp.net-mvcentity-framework-coreentity-framework-migrations

Cannot connect to a AWS MySql Database from ASP.NET Core web app with Entity Framework Core on a Mac

I am following a tutorial on how to setup a backend server for a Unity game with ASP.NET Core and Entity Framework Core. I develop on a Mac M1.

I have created migrations for the initial database setup. I have created a MySQL database in AWS and have entered the db connection credentials as a connection string in appsettings.json. At the point where I have to run dotnet ef database update to create the database tables I have defined in my model classes I get this error:

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: 35 - An internal exception was caught)

This is the last part of the call stack

---> System.Net.Sockets.SocketException (00000005, 0xFFFDFFFF): nodename nor servname provided, or not known
   at System.Net.Dns.GetHostEntryOrAddressesCore(String hostName, Boolean justAddresses, AddressFamily addressFamily, Nullable`1 startingTimestamp)
   at System.Net.Dns.GetHostAddresses(String hostNameOrAddress, AddressFamily family)
   at Microsoft.Data.SqlClient.SNI.SNICommon.GetDnsIpAddresses(String serverName)
   at Microsoft.Data.SqlClient.SNI.SNITCPHandle.Connect(String serverName, Int32 port, TimeSpan timeout, Boolean isInfiniteTimeout, SqlConnectionIPAddressPreference ipPreference, String cachedFQDN, SQLDNSInfo& pendingDNSInfo)
   at Microsoft.Data.SqlClient.SNI.SNITCPHandle..ctor(String serverName, Int32 port, Int64 timerExpire, Boolean parallel, SqlConnectionIPAddressPreference ipPreference, String cachedFQDN, SQLDNSInfo& pendingDNSInfo, Boolean tlsFirst, String hostNameInCertificate, String serverCertificateFilename)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnectionString connectionOptions, Boolean withFailover)

This is my connection string in my appsettings.json:

"GameDb-1":"Server=MyEndpoint.cheyadf0khl8.eu-central-1.rds.amazonaws.com:3306; Database=game_db;User=myUser,Password=myPassword;"

By now I have tried several permutations of the connection string, basically everything I could find in the documentation but nothing worked in my case.

Curiously I can connect with the very same credentials from MySqlWorkbench which means that the db is configured for allowing remote connections from my machine.

screenshot of a credentials mask for a database connection in MySQLWorkbench, showing the used credentials

screenshot of the Server Status in MySQLWorkbench, showing an active connection to my AWS database

I have setup a local SQL Server to check if it might be an AWS specific problem but I have the same error when connecting to a locally hosted database.

Any idea what I could try to be able to connect?

I am on

MacOS 14.7 (Sonoma) Arm64
.NET SDK 8.0.303
Microsoft.EntityFrameworkCore.SqlServer 8.0.8
Solution

  • With a little help of ChatGPT I was able to find the cause of my issue. The tutorial I was following was implying a SQLServer setup, while I was using a MySQL setup. I had to change

    builder.Services.AddDbContext<GameDbContext>(
    options => options.UseSQLServer(builder.Configuration.GetConnectionString("GameDb-1")));

    to

    builder.Services.AddDbContext<GameDbContext>(
    options => options.UseMySQL(builder.Configuration.GetConnectionString("GameDb-1")));

    Adjust the connection string to a MySql format

    "GameDb-1": "Server=MyEndpoint.cheyadf0khl8.eu-central-1.rds.amazonaws.com;Port=3306;Database=game_db;Uid=myUser;Pwd=myPassword;"

    and also remove the Microsoft.EntityFrameworkCore.SQLServer nuget package and replace it with MySql.EntityFrameworkCore package.

    I deleted the existing migrations and created new ones with the new setup. Now I was able to connect to my database and create the model tables.