2
votes

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.

2
Is there any special reason behind concatenation in connection string?Prashant Pimpale
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
Confirm the connection string right or wrong!Prashant Pimpale
Define your connection string in web config like:<connectionStrings> <add name="CONSTRING" connectionString="Data Source=SQL-PC;Initial Catalog=DATABASE;Integrated Security=True"/> </connectionStrings>Prashant Pimpale
The string is correct, so if I don't concatenate how shall I proceed with it?user2520212

2 Answers

4
votes

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

2
votes

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.