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

rbl8hiat  于 2023-05-05  发布在  SQL Server
关注(0)|答案(3)|浏览(332)

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, TaskCompletionSource 1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource 1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource 1 retry, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource 1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource 1 retry) at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource 1 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.

x8goxv8g

x8goxv8g1#

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 ...

hmae6n7t

hmae6n7t2#

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.

jv2fixgn

jv2fixgn3#

You must add Encrypt=False to your Connection String

相关问题