SQL Server connection Error Number:2,State:0,Class:20

Question

When trying to connect to a local SQL server (MAMP) I'm getting this exception:

Exception thrown: 'System.Data.SqlClient.SqlException' in System.Data.dll System.Data.SqlClient.SqlException (0x80131904): 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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) ---> System.ComponentModel.Win32Exception (0x80004005): The system cannot find the file specified at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry) at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry) at System.Data.SqlClient.SqlConnection.Open() at login_page.DatabaseClass.dbRead(String sqlQuery) in C:\Users******\DatabaseClass.cs:line 35 ClientConnectionId:00000000-0000-0000-0000-000000000000 Error Number:2,State:0,Class:20

This is the class I'm using to perform a SELECT SQL function

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace login_page
{
    class DatabaseClass
    {
        public void dbSignIn(String username, String password)
        {
            dbRead("SELECT * FROM user_credentials WHERE username = '" + username + "' AND password = '" + password + "'");
        }

        public void dbRegisterUser()
        {
            dbRead("SQL READ TO DATABASE");
            //dbWrite("SQL WRITE TO DATABASE")
        }

        private void dbRead(String sqlQuery)
        {
            SqlDataReader dataReader;
            SqlCommand command;

            // *** CONNECT TO DATABASE
            Console.WriteLine("** Database Connection: Connecting to database");

            SqlConnection dbConnection = new SqlConnection("User Id=root;" + "Password=root;" + "Server=localhost;" + "Trusted_Connection=true;" + "Database=dbmentum;" + "Connection Timeout=10;");
            try
            {
                dbConnection.Open();
                Console.WriteLine("** Database Connection: Connected to database server");

                // *** READ FROM DATABASE
                command = new SqlCommand(sqlQuery, dbConnection);
                dataReader = command.ExecuteReader();

                while (dataReader.Read())
                {
                    Console.WriteLine(dataReader[0].ToString());    
                    Console.WriteLine(dataReader[1].ToString());
                }

                dataReader.Close();
                command.Dispose();
                dbConnection.Close();
            }
            catch (SqlException e)
            {
                Console.WriteLine(e.ToString());
                MessageBox.Show(e.Message, "Mentum - Database Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            /*
            // CLOSE DATABASE
            try
            {
                dbConnection.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            } */
        }
    }
}

All appropriate ports are enabled and database details are correct.

Is there any special reason behind concatenation in connection string? — Prashant Pimpale, Jul 07 '18 at 15:36
N the error is : 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. — Prashant Pimpale, Jul 07 '18 at 15:38
The string is correct, so if I don't concatenate how shall I proceed with it? — user2520212, Jul 07 '18 at 15:48
Related posts - [How do I fix the error 'Named Pipes Provider, error 40 - Could not open a connection to' SQL Server'?](https://stackoverflow.com/q/9945409/465053) & [Unable to connect to SQL Server instance remotely](https://stackoverflow.com/q/6987709/465053) — RBT, Mar 06 '20 at 00:11
As Prashant Pimpale pointed out the error you are observing is related to network connection. And if it's the case, that you use an MS SQL Server client to connect to a MySQL Database, the reason is obvious. MSSQL default port is 1433, MySQL default port is 3306. So the client will try to connect to port 1433, but there is no service listening. — Ali NajafZadeh, Aug 03 '21 at 17:03

derpirscher · Accepted Answer · 2018-07-07T15:55:09.557

4

You mentioned MAMP which is in my understanding a MySql server. Nonetheless, you are using SqlConnection and SqlCommand which is for connecting to an MS SQL Server. For MySql you need a MySqlConnection, MySqlCommand and so on.

As Prashant Pimpale pointed out the error you are observing is related to network connection. And if it's the case, that you use an MS SQL Server client to connect to a MySQL Database, the reason is obvious. MSSQL default port is 1433, MySQL default port is 3306. So the client will try to connect to port 1433, but there is no service listening. Thus, no connection can be established ...

edited Jul 07 '18 at 15:55

answered Jul 07 '18 at 15:47

derpirscher

14,418
3
18
35

your argument makes a lot of sense but I am getting a syntax error when I use MySqlConnection for example, I also tried to include the library `using System.Data.SqlClient;`, then I tried to change the port on MAMP server to 1134 and now it seems to budge and stop to the next line `System.InvalidOperationException: 'Internal connection fatal error. Error state: 18' ` – user2520212 Jul 07 '18 at 17:24
`System.Data.SqlClient` is only for connecting to MS SQL Server. This is a completely differnt protocol than for MySql Server. You must use the correct library. If you want to connect to MySql you **MUST USE** the MySql connector. See https://dev.mysql.com/doc/connector-net/en/ for documentation. MySql connector is not built in to .NET but you must download an external library and add the references to your project in order to use `MySql.Data` – derpirscher Jul 07 '18 at 19:44

score 2 · Answer 2 · edited Mar 06 '20 at 00:12

According to the error message the database instance provided in the connecting string is either wrong, not reachable or is not configured to allow remote connections (but you are using localhost, so this is not the cause)

MAMP seems to use MySQL as a database. Please refer to MySQL NET Developer Guide to find out, how to connect to MySQL. I also recommend to perform a research on SQL injection as your method dbSignIn is a good target for this type of attack.

score 1 · Answer 3 · answered Apr 29 '23 at 08:13

1

You must add Encrypt=False to your Connection String

answered Apr 29 '23 at 08:13

Huseyn Hemidov

11
1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community May 01 '23 at 16:30

SQL Server connection Error Number:2,State:0,Class:20

3 Answers3