Unable to connect to remote host

Question

I'm having an issue when connecting to a remote host. I am able to connect to my local server with a copy of the database.

I'm trying to connect to the XenForo DB on my web host and get some information. All is working on localhost.

private static MySqlConnection _connection = 
    new MySqlConnection("Server=ip; database=ls-v_forum; UID=ls-v_forum; password=pass");

public static int? FetchUserId(string emailoruser)
{
    MySqlCommand userCommand = new MySqlCommand("SELECT * FROM xf_user WHERE username='" + emailoruser + "'", _connection);
    MySqlCommand emailCommand = new MySqlCommand("SELECT * FROM xf_user WHERE email='" + emailoruser + "'", _connection);

    _connection.OpenAsync();
}

That's the code and it's throwing this error

Connection must be valid and open. at MySql.Data.MySqlClient.ExceptionInterceptor.Throw(Exception exception) at MySql.Data.MySqlClient.MySqlCommand.CheckState() at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)

EDIT

public int? FetchUserId(string emailoruser)
    {
        using (var _connection = new MySqlConnection("server=ip; database=ls-v_forum; UID=ls-v_forum; password=pass"))
        {
            MySqlCommand userCommand = new MySqlCommand("SELECT * FROM xf_user WHERE username='" + emailoruser + "'", _connection);
            MySqlCommand emailCommand = new MySqlCommand("SELECT * FROM xf_user WHERE email='" + emailoruser + "'", _connection);

            _connection.Open();


            MySqlDataReader userReader = userCommand.ExecuteReader();

            int? userId = null;

            while (userReader.Read())
            {
                userId = userReader.GetInt32("user_id");
            }

            userReader.Close();

            if (userId == null || userId == 0)
            {
                MySqlDataReader emailReader = emailCommand.ExecuteReader();

                while (emailReader.Read())
                {
                    userId = emailReader.GetInt32("user_id");
                }

                emailReader.Close();
            }

            _connection.Close();

            return userId;
        }
    }

MySql.Data.MySqlClient.MySqlException (0x80004005): Unable to connect to any of the specified MySQL hosts. at MySql.Data.MySqlClient.NativeDriver.Open() at MySql.Data.MySqlClient.Driver.Open() at MySql.Data.MySqlClient.Driver.Create(MySqlConnectionStringBuilder settings) at MySql.Data.MySqlClient.MySqlPool.CreateNewPooledConnection() at MySql.Data.MySqlClient.MySqlPool.GetPooledConnection() at MySql.Data.MySqlClient.MySqlPool.TryToGetDriver() at MySql.Data.MySqlClient.MySqlPool.GetConnection() at MySql.Data.MySqlClient.MySqlConnection.Open()

Answer 1

I didn't attempt to troubleshoot your connection command, but the following works for me when connecting to a SQL DB on a remote machine

You can provide the machine name even if it is the local machine, so the code below will work if the program is running on the same machine as the database or if the program is running on one machine and the database is on another, so long as the two machines are networked AND the account you're running the program under has access to the machine and instance and database.

Please note in example below, the "default" instance name (MSSQLSERVER) was used when SQL was installed. When the DB instance name is the default name, then you must not provide an instance name explicitly (you'll get an error if you do). The only time you provide an instance name explicitly is when it is not the default instance name. The code below can handle either scenario (by setting dbInstanceName variable to "" or an instance name, e.g. "\SQLEXPRESS"). See S.O. SQL Server: How to find all localdb instance names. When it doubt, try an empty instance name and a name you believe to be the instance name to see what works.

string databaseMachineName = "machine_name";
string databaseInstanceName = ""; 
string dbName = "database_name";

using (SqlConnection sqlConnection = new SqlConnection("Data Source=" + databaseMachineName + databaseInstanceName + "; Initial Catalog=" + dbName + "; Integrated Security=True;Connection Timeout=10"))
{
   .
   .
   .
}

Answer 2

I'm having an issue when connecting to a remote host.

Not necessarily. According to the error, the issue isn't that you can't connect. It's that you're trying to use a connection that isn't connected:

Connection must be valid and open.

Specifically where you execute a command:

MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)

Which isn't in the code you're showing. However, there are a couple of fundamental mistakes that are in the code you're showing which would easily lead to an error like this:

1. Using a static shared connection object.

This is a famously bad idea. We've probably all tried it, and we've probably all run into issues exactly like this one. The underlying system is pretty efficient at creating/pooling/using/disposing database connections. Don't try to optimize for it. Instead, you should create/use/dispose your connections in as small a scope as possible. For example:

using (var  connection = new MySqlConnection(SOME_CONNECTION_STRING))
{
    var userCommand = new MySqlCommand(SOME_COMMAND_STRING);
    // use the command, get the data you need from it
}
// leave the context of the database and return to business logic, UI, etc.

This is because keeping complex things like database connections synchronized is hard, and keeping connections open is expensive. Let the underlying system open/pool/close connections.

2. Not awaiting an async operation.

What would happen here?:

connection.OpenAsync();
userCommand.ExecuteNonQuery();

An error. Because the code didn't await the asynchronous operation, so the connection isn't open when you're trying to use it. Either don't use the asynchronous operation:

connection.Open();
userCommand.ExecuteNonQuery();

or await it:

await connection.OpenAsync();
userCommand.ExecuteNonQuery();

(And obviously make the containing method async, and its callers should await it, etc.) But definitely don't try to use a connection before it's had a chance to open.

3. (Unrelated, but still important) Your code is vulnerable to SQL injection.

SQL injection happens right here:

"SELECT * FROM xf_user WHERE username='" + emailoruser + "'"

Where did emailoruser come from? Was it user input? Was it a value pulled from data which was previously provided by a user? How trustworthy is it? What this string-concatenation approach does is allow any user to execute any SQL code they want on your database. Instead, use query parameters and treat user input as values instead of as executable code.