Cannot connect to mariadb database from VB.Net app with credentials that work from Python on a different system

I have a couple of databases running under mariadb on a Synology NAS, which are being updated and read without problems using the standard mariadb connector across my local network by Python scripts running on a Raspberry Pi. I am trying to access the databases from a Visual Basic Forms app running on my Windows 11 system. Here is the failing code, which is attached to a button on an otherwise blank form:

Imports MySql.Data.MySqlClient
Imports Microsoft.VisualBasic.ApplicationServices
Imports Microsoft.Win32


Public Class Form1
    Dim sqlConn As New MySqlConnection
    Dim sqlCmd As New MySqlCommand
    Dim sqlRd As MySqlDataReader
    Dim sqlDt As New DataTable
    Dim DtA As New MySqlDataAdapter

    Dim server As String = "192.168.1.24"
    Dim username As String = "<user name>"
    Dim password As String = "<password>"
    Dim database As String = "data"

    Private Sub btnConnTest_Click(sender As Object, e As EventArgs) Handles btnConnTest.Click
        sqlConn.ConnectionString = "server=" + server + ";user id=" + username + ";password=" + password
        sqlConn.Open()
        sqlCmd.Connection = sqlConn
        sqlCmd.CommandText = "SELECT * FROM data.Min5Readings LIMIT 10"
        sqlRd = sqlCmd.ExecuteReader
        sqlDt.Load(sqlRd)
        sqlRd.Close()
        sqlConn.Close()
    End Sub
End Class

I have checked and double checked that the credentials I am using are identical to those which the Pi code is using without problems, but I get an immediate 'Access denied' exception when the code tries to open the connection. [Edit]The corresponding (working) Python code is:

import mariadb

conn_mdb = mariadb.connect(
    host="192.168.1.24",
    port=3306,
    user="<User>",
    password="<Password>")
conn_mdb.autocommit=True
cur_mdb=conn_mdb.cursor()
....

[/Edit] Any thoughts?

The following isn't necessarily an answer to your issue, but is some code that you can use for testing:

VS 2022:

• Open VS 2022

• Click

• Click File

• Select New

• Select Project

• For filter, choose:

  

• Select Windows Forms App (.NET Framework)

• Click Next

• Enter desired project name (ex: MariaDBTest) and select desired location.

• Click Next

• For Framework, select .NET Framework 4.8

• Click Create

Open Solution Explorer:

• In VS menu, click View
• Select Solution Explorer

Open Properties Window

• In VS menu, click View
• Select Properties Window

Install/Download NuGet package: MySqlConnector (v2.4.0)

• In Solution Explorer, expand <project name>
• Right-click <project name> and select Manage NuGet Packages...
• Click Browse tab
• In the search box, type: MySqlConnector
• Select MySqlConnector
• Select desired version (ex: 2.4.0), and click Install
• If prompted Visual Studio is about to make changes to this solution. Click OK to proceed with the changes listed below..., click OK
• If a License Acceptance prompt appears, click the appropriate button.

---

Application Configuration File (Optional): The database connection string can be stored in an application configuration file.

If App.config doesn't already exist in your project, then add an Application Configuration File to your project (name: App.config)

• In VS menu, click Project
• Select Add New Item...
• Select Application Configuration File (name: App.config)
• Click Add

App.config:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <connectionStrings>
        <!-- see https://mysqlconnector.net/connection-options/  -->
        <add name="MariaDBConnectionString" connectionString="Server=192.168.1.24;Port=3306;Database=data;Uid=Test2;Pwd=password123;" />
    </connectionStrings>
    <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.8" />
    </startup>
</configuration>

Add Reference: (System.Configuration)

• In Visual Studio menu, click Project

• Select Add Reference...

• On left, click Assemblies

• Check System.Configuration

• Click OK.

Add Imports Statement

• Imports System.Configuration

---

Pre-requisites:

• Database Name: data

• Table Name: Min5Readings

*Note: See Identifier Case-sensitivity.

---

Add Imports Statement

• Imports MySqlConnector

'see https://mysqlconnector.net/connection-options/
'retrieve connection string from Application Manifest File (App.config)
Private _connectionStr As String = ConfigurationManager.ConnectionStrings("MariaDBConnectionString").ConnectionString
'Private _connectionStr As String = "Server=192.168.1.24;Port=3306;Database=data;Uid=Test2;Pwd=password123;"
'Private _connectionStr As String = "Server=192.168.1.24;Port=3306;Database=data;UserID=Test2;Pwd=password123;"

Choose from one of the following methods below to retrieve your data.

Option 1:

Private Function GetDataUsingDataAdapter() As DataTable
    System.Diagnostics.Debug.WriteLine($"_connectionStr: {_connectionStr}")

    Using da As MySqlDataAdapter = New MySqlDataAdapter("Select * from Min5Readings", _connectionStr)
        Dim dt As DataTable = New DataTable()
        Dim numRows As Integer = da.Fill(dt)
        System.Diagnostics.Debug.WriteLine($"numRows: {numRows}")
        Return dt
    End Using
End Function

Option 2:

Private Function GetDataUsingDataAdapter() As DataTable
    System.Diagnostics.Debug.WriteLine($"_connectionStr: {_connectionStr}")

    Using con As MySqlConnection = New MySqlConnection(_connectionStr)
        'open
        con.Open()

        Using da As MySqlDataAdapter = New MySqlDataAdapter("Select * from Min5Readings", con)
            Dim dt As DataTable = New DataTable()
            Dim numRows As Integer = da.Fill(dt)
            System.Diagnostics.Debug.WriteLine($"numRows: {numRows}")
            Return dt
        End Using
    End Using
End Function

Option 3:

Private Function GetDataUsingDataAdapter() As DataTable
    System.Diagnostics.Debug.WriteLine($"_connectionStr: {_connectionStr}")

    Using con As MySqlConnection = New MySqlConnection(_connectionStr)
        'open
        con.Open()

        Using cmd As MySqlCommand = New MySqlCommand("Select * from Min5Readings", con)
            'ToDo: if necessary, add parameters and parameter values

            Using da As MySqlDataAdapter = New MySqlDataAdapter(cmd)
                Dim dt As DataTable = New DataTable()
                Dim numRows As Integer = da.Fill(dt)
                System.Diagnostics.Debug.WriteLine($"numRows: {numRows}")
                Return dt
            End Using
        End Using
    End Using
End Function

Option 4 (named parameters):

*Note: The code below assumes that the database table contains the following columns: Id, Name, Description and the data type for the Id column is Integer (or Int32).

Private Function GetDataUsingDataAdapter(id As Integer) As DataTable
    Using con As MySqlConnection = New MySqlConnection(_connectionStr)
        'open
        con.Open()

        Using cmd As MySqlCommand = New MySqlCommand("Select Id, Name, Description from Min5Readings Where Id = @id", con)
            'ToDo: if necessary, add parameters and parameter values

            cmd.Parameters.Add("@id", MySqlDbType.Int32).Value = id

            Using da As MySqlDataAdapter = New MySqlDataAdapter(cmd)
                Dim dt As DataTable = New DataTable()
                Dim numRows As Integer = da.Fill(dt)
                System.Diagnostics.Debug.WriteLine($"numRows: {numRows}")
                Return dt
            End Using
        End Using
    End Using
End Function

Option 5:

Private Function GetDataUsingDataReader() As DataTable
    System.Diagnostics.Debug.WriteLine($"_connectionStr: {_connectionStr}")

    Using con As MySqlConnection = New MySqlConnection(_connectionStr)
        'open
        con.Open()

        Using cmd As MySqlCommand = New MySqlCommand("Select * from Min5Readings", con)
            'ToDo: if necessary, add parameters and parameter values

            Using dr As MySqlDataReader = cmd.ExecuteReader()
                If dr.HasRows Then
                    Dim dt As DataTable = New DataTable()
                    dt.Load(dr)
                    System.Diagnostics.Debug.WriteLine($"dt.Rows.Count: {dt.Rows.Count}")
                    Return dt
                Else
                    'System.Diagnostics.Debug.WriteLine("Info: No data found.")
                    'Return New DataTable()
                    Throw New Exception("No data found.")
                End If
            End Using
        End Using
    End Using

    Return Nothing
End Function

Resources:

• ADO.NET Overview

• MySqlConnector for ADO.NET

• MariaDB Connector/NET Guide

• MySqlConnector Connection String Options

• Identifier Case-sensitivity

• Concatenation Operators in Visual Basic

• Strings.Chr()

• ASCII Table

• Interpolated strings

• Using Statement