C#•6mo ago

✅ Microsoft sql unable to connect. What I'm missing?

Hey! Does anybody know what I'm missing as I'm trying to connect to mysql? I get InvalidOperationExpection on SqlConnection.Open() function call. (System.InvalidOperationException: 'Internal connection fatal error.) Here is the ConnectionString: Data Source = "127.0.0.1, 3307"; Initial Catalog = mysql_db; User ID = root; Password = root Ip, port, db, username and password is correct as I was able to login as root use using MySQL Workbench software. GetBuilder function:

       internal SqlConnectionStringBuilder GetBuilder()
        {
            return new SqlConnectionStringBuilder
            {
                DataSource = Server,
                UserID = Username,
                Password = Password,
                InitialCatalog = Database
            };
        }

       internal SqlConnectionStringBuilder GetBuilder()
        {
            return new SqlConnectionStringBuilder
            {
                DataSource = Server,
                UserID = Username,
                Password = Password,
                InitialCatalog = Database
            };
        }

GetConnection function

        internal SqlConnection GetConnection(SqlConnectionStringBuilder builder = null)
        {
            SqlConnection connection = null;
            try
            {
                connection = builder == null ? new SqlConnection(GetBuilder().ConnectionString)
                                             : new SqlConnection(builder.ConnectionString);
            }
            catch (Exception err)
            {
                Console.WriteLine($"ERROR::Db_Connection::GetConnection:: {err}");
            }
            return connection;
        }

        internal SqlConnection GetConnection(SqlConnectionStringBuilder builder = null)
        {
            SqlConnection connection = null;
            try
            {
                connection = builder == null ? new SqlConnection(GetBuilder().ConnectionString)
                                             : new SqlConnection(builder.ConnectionString);
            }
            catch (Exception err)
            {
                Console.WriteLine($"ERROR::Db_Connection::GetConnection:: {err}");
            }
            return connection;
        }

SendQuery function calls GetConnection after connection.Open is called which gives me the error.

                SqlConnection connection = GetConnection();
                connection.Open();

                SqlConnection connection = GetConnection();
                connection.Open();

25 Replies

Pobiega•6mo ago

SqlConnection can't be used with MySQL

JanekOP•6mo ago

Wait, really?

Pobiega•6mo ago

you will need to get a mysql connector library yes all sql servers have their own protocol SqlConnection only works with MSSQL (Microsoft SQL Server) and Express

JanekOP•6mo ago

I see. I was wondering why the hell my code is not working. This explains everything. Thank you!

Pobiega•6mo ago

https://www.nuget.org/packages/MySqlConnector/ should be what you need

MySqlConnector 2.3.7

A truly async MySQL ADO.NET provider, supporting MySQL Server, MariaDB, Amazon Aurora, Azure Database for MySQL, Google Cloud SQL, and more.

JanekOP•6mo ago

@Pobiega Do you know why it tries to connect to 172.18.0.0.1 instead of 127.0.0.1?

JanekOP•6mo ago

Connection string on top, and on Error it says 172.18.0.1

Unknown User•6mo ago

Message Not Public

JanekOP•6mo ago

But I don't really understand why it changes ip address to private from local.. This is something totally new to me

Unknown User•6mo ago

Message Not Public

JanekOP•6mo ago

But I don't get why access is denied. I have full access on MySql Workbench with same username

Unknown User•6mo ago

Message Not Public

JanekOP•6mo ago

password ? I used docker to set up mysql

services:
  mysql:
    container_name: mysql_db
    restart: always
    image: mysql:latest
    environment:
      MYSQL_ROOT_PASSWORD: 'root'
      MYSQL_USER: 'admin'
      MYSQL_PASS: 'admin'
    volumes:
      - mysql_db_data:/var/lib/mysql
    ports:
      - "3307:3306"
volumes:
  mysql_db_data:

services:
  mysql:
    container_name: mysql_db
    restart: always
    image: mysql:latest
    environment:
      MYSQL_ROOT_PASSWORD: 'root'
      MYSQL_USER: 'admin'
      MYSQL_PASS: 'admin'
    volumes:
      - mysql_db_data:/var/lib/mysql
    ports:
      - "3307:3306"
volumes:
  mysql_db_data:

        internal string Server { get; private set; } = "127.0.0.1, 3307";
        internal string Username { get; private set; } = "root";
        internal string Password { get; private set; } = "root";
        internal string Database { get; private set; } = "mysql_db";

        internal string Server { get; private set; } = "127.0.0.1, 3307";
        internal string Username { get; private set; } = "root";
        internal string Password { get; private set; } = "root";
        internal string Database { get; private set; } = "mysql_db";

I can't really see what part is wrong..

Unknown User•6mo ago

Message Not Public

JanekOP•6mo ago

ahh, I totally forgot that it can be different on different sql.. I used ; before (https://www.connectionstrings.com/sql-server-2008/) and after that I used ,

SQL Server 2008 connection strings - ConnectionStrings.com

Connection strings for SQL Server 2008. Connect using Microsoft.Data.SqlClient, SqlConnection, SQLNCLI11 OLEDB, SQLNCLI10 OLEDB, ODBC Driver 17 for SQL Server.

Pobiega•6mo ago

MSSQL uses the comma, but mysql seems to prefer Port in general, avoid mysql - its not a very alternative these days

JanekOP•6mo ago

I tried to get MSSQL to work with docker but no success, so I moved to mysql.

Pobiega•6mo ago

curious i run mssql on docker myself and it works very well

JanekOP•6mo ago

I'm new to this all, so I probably failed to write working docker-compose.yml. I also tried to google and felt like I found decent answers, but no success

Pobiega•6mo ago

sqlserver:
  image: mcr.microsoft.com/mssql/server
  hostname: "sqlserver"
  container_name: mssql
  restart: "always"
  environment:
    ACCEPT_EULA: "Y"
    MSSQL_SA_PASSWORD: "aaaAAA!!!"
    MSSQL_PID: Developer
  ports:
    - "1433:1433"
  volumes:
    - "C:/Docker/mssql/data:/var/opt/mssql/data"
    - "C:/Docker/mssql/backup:/var/opt/mssql/backup"
    - "C:/Docker/mssql/log:/var/opt/mssql/log"

sqlserver:
  image: mcr.microsoft.com/mssql/server
  hostname: "sqlserver"
  container_name: mssql
  restart: "always"
  environment:
    ACCEPT_EULA: "Y"
    MSSQL_SA_PASSWORD: "aaaAAA!!!"
    MSSQL_PID: Developer
  ports:
    - "1433:1433"
  volumes:
    - "C:/Docker/mssql/data:/var/opt/mssql/data"
    - "C:/Docker/mssql/backup:/var/opt/mssql/backup"
    - "C:/Docker/mssql/log:/var/opt/mssql/log"

thats my docker-compose or well, the sql service part of it 🙂

JanekOP•6mo ago

I found something similar, but it kept shutting down. Let me try again. Yeah same issue, running for like 10 seconds then shutting down :S

Pobiega•6mo ago

try checking the logs?

JanekOP•6mo ago

ohh, such a stupid error.. SQL Server password policy requirements because it is too short... ~~Edit, seems like that is not all :S~~ Got it to work. Thank you so much @Pobiega !

Unknown User•6mo ago

Message Not Public

MODiX•6mo ago

If you have no further questions, please use /close to mark the forum thread as answered

Gaming

Programming

✅ Microsoft sql unable to connect. What I'm missing?