A network-related or instance-specific error occurred while establishing a connection

[sking]

Hello there,

I have been programming in visual studio for 10 years+. One of my websites loads just fine when I hit the debug button. I can go to the login page and login to the database
I have another site that is not allowing me to login, and it's providing this message which I'm trying to figure out. I have dealt with this in the past, but I believe my previous resolution was to turn on the sql server in the sql configuration manager. My server shows as running.

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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

I have checked my sql server configuration manager and it is ON. Here are my connection strings:

<connectionStrings>
<add name="sqlConnectionString" connectionString="Connect Timeout=8; Initial Catalog=DB_132067_prospect; Data Source=DESKTOP-UKV35UH;integrated security=true;" providerName="System.Data.SqlClient"/>
<add name="sqlConnectionString2" connectionString="Connect Timeout=8; Initial Catalog=DB_132067_prospect; Data Source=DESKTOP-UKV35UH;integrated security=true;" providerName="System.Data.SqlClient"/>
</connectionStrings>


When I start this in debugger, the home page loads, and on the home page there are numbers which are obtained from the database. So the connection does work on the home page and numbers are being pulled from the database. Its the login page this is generating this error. So this means that the above connection string is correct!

Does someone know why this might happen?

[jmcilhinney]

You need to actually debug your code, not just run the project in the debugger. If you are opening a database connection in two places and one is working and the other is not then there must be something different about the two. Set appropriate breakpoints and actually look closely at what is happening in each place to see what the difference is.

Why do you have two connection strings that are the same? Were they different at some point and you changed one of them for testing purposes? Are these two spots in your code that behave differently using different connection strings? If so, what happens if you change the one that doesn't work to use the same as the other spot?

[sking]

You need to actually debug your code, not just run the project in the debugger. If you are opening a database connection in two places and one is working and the other is not then there must be something different about the two. Set appropriate breakpoints and actually look closely at what is happening in each place to see what the difference is.

Why do you have two connection strings that are the same? Were they different at some point and you changed one of them for testing purposes? Are these two spots in your code that behave differently using different connection strings? If so, what happens if you change the one that doesn't work to use the same as the other spot?

Hi there,

Yes, they used to be different strings. I have tried everything. I don't know why it is giving this message. Interestingly I don't get this problem on the live site. I can log right in. The home page loads fine on my local machine and it is connecting to the database and my table adapter queries are working. But when I navigate to my login page and try to login, it is coughing up this message. I tried to debug the login page and I can see that certain items are being stored in the authentication sub. it's supposed to just redirect.
Protected Sub Login1_Authenticate(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.AuthenticateEventArgs) Handles Login1.Authenticate
If IsReCaptchValid() Then
Lblmessage1.ForeColor = Drawing.Color.Green
Lblmessage1.Text = "Captcha Valid!"
Else
lblMessage1.ForeColor = Drawing.Color.Red
lblMessage1.Text = "Valid Captcha Required"
Exit Sub
End If
Dim authenticated As Boolean
Dim authenticated As Boolean
If Login1.RememberMeSet Then
Response.Cookies("UserName").Expires = DateTime.Now.AddDays(30)
Response.Cookies("Password").Expires = DateTime.Now.AddDays(30)
Else
Response.Cookies("UserName").Expires = DateTime.Now.AddDays(-1)
Response.Cookies("Password").Expires = DateTime.Now.AddDays(-1)
End If
Response.Cookies("UserName").Value = Login1.UserName.Trim
Response.Cookies("Password").Value = Login1.Password.Trim

authenticated = Authentication(Login1.UserName, Login1.Password)
If authenticated Then
FormsAuthentication.RedirectFromLoginPage(Login1.UserName, Login1.RememberMeSet)
End If
Session("Check") = authenticated
e.Authenticated = authenticated
End Sub
Protected Function Authentication(ByVal username As String, ByVal password As String) As [Boolean]
Dim correct As Boolean = False
Using con As New SqlConnection(ConfigurationManager.ConnectionStrings("sqlConnectionString").ConnectionString)
Using command As New SqlCommand("GetUserLoginDetails", con)
command.Parameters.Add(New SqlParameter("@Username", SqlDbType.VarChar)).Value = username
command.Parameters.Add(New SqlParameter("@Password", SqlDbType.VarChar)).Value = password
command.CommandType = CommandType.StoredProcedure
con.Open()
If con.State = ConnectionState.Open Then
Using reader As SqlDataReader = command.ExecuteReader()
If reader.Read() Then
If reader("Firstname") IsNot DBNull.Value Then
Session("firstname") = reader("firstname").ToString()
End If
If reader("Lastname") IsNot DBNull.Value Then
Session("lastname") = reader("lastname").ToString()
End If
If reader("Username") IsNot DBNull.Value Then
Session("Username") = reader("Username").ToString()
End If
If reader("UserID") IsNot DBNull.Value Then
Session("UserID") = reader("UserID").ToString()
End If
If reader("UnlockCode") IsNot DBNull.Value Then
Session("path") = reader("UnlockCode").ToString()
End If
If reader("Appcompletedate") IsNot DBNull.Value Then
Session("AppDate") = reader("Appcompletedate").ToString()
End If
correct = True
End If
End Using
Else
End If
End Using
End Using
Return correct
End Function

[jmcilhinney]

Don't post unformatted code snippets. They are too hard to read, especially if they are long.

 Protected Sub Login1_Authenticate(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.AuthenticateEventArgs) Handles Login1.Authenticate
        If IsReCaptchValid() Then
            Lblmessage1.ForeColor = Drawing.Color.Green
            Lblmessage1.Text = "Captcha Valid!"
        Else
            lblMessage1.ForeColor = Drawing.Color.Red
            lblMessage1.Text = "Valid Captcha Required"
            Exit Sub
        End If
        Dim authenticated As Boolean
        Dim authenticated As Boolean
        If Login1.RememberMeSet Then
            Response.Cookies("UserName").Expires = DateTime.Now.AddDays(30)
            Response.Cookies("Password").Expires = DateTime.Now.AddDays(30)
        Else
            Response.Cookies("UserName").Expires = DateTime.Now.AddDays(-1)
            Response.Cookies("Password").Expires = DateTime.Now.AddDays(-1)
        End If
        Response.Cookies("UserName").Value = Login1.UserName.Trim
        Response.Cookies("Password").Value = Login1.Password.Trim

        authenticated = Authentication(Login1.UserName, Login1.Password)
        If authenticated Then
            FormsAuthentication.RedirectFromLoginPage(Login1.UserName, Login1.RememberMeSet)
        End If
        Session("Check") = authenticated
        e.Authenticated = authenticated
    End Sub
    Protected Function Authentication(ByVal username As String, ByVal password As String) As [Boolean]
        Dim correct As Boolean = False
        Using con As New SqlConnection(ConfigurationManager.ConnectionStrings("sqlConnectionString").ConnectionString)
            Using command As New SqlCommand("GetUserLoginDetails", con)
                command.Parameters.Add(New SqlParameter("@Username", SqlDbType.VarChar)).Value = username
                command.Parameters.Add(New SqlParameter("@Password", SqlDbType.VarChar)).Value = password
                command.CommandType = CommandType.StoredProcedure
                con.Open()
                If con.State = ConnectionState.Open Then
                    Using reader As SqlDataReader = command.ExecuteReader()
                        If reader.Read() Then
                            If reader("Firstname") IsNot DBNull.Value Then
                                Session("firstname") = reader("firstname").ToString()
                            End If
                            If reader("Lastname") IsNot DBNull.Value Then
                                Session("lastname") = reader("lastname").ToString()
                            End If
                            If reader("Username") IsNot DBNull.Value Then
                                Session("Username") = reader("Username").ToString()
                            End If
                            If reader("UserID") IsNot DBNull.Value Then
                                Session("UserID") = reader("UserID").ToString()
                            End If
                            If reader("UnlockCode") IsNot DBNull.Value Then
                                Session("path") = reader("UnlockCode").ToString()
                            End If
                            If reader("Appcompletedate") IsNot DBNull.Value Then
                                Session("AppDate") = reader("Appcompletedate").ToString()
                            End If
                            correct = True
                        End If
                    End Using
                Else
                End If
            End Using
        End Using
        Return correct
    End Function

[jmcilhinney]

Did you actually debug your code properly, setting a breakpoint and stepping through the code line by line? Where EXACTLY was the exception thrown? What happens if you actually comment one of the connection strings out in the config file and use the same config value in both locations?

[jmcilhinney]

I have a couple of comments on code style.

Firstly, there's no need to nest Using blocks unless there's code in between them. In your case, the connection and the command can be created with a single Using statement, then you call Open on the connection, then you need a second Using statement to create the data reader.

Secondly, testing that a connection is open after calling Open is pointless. Whewn you call Open, either the connection will be opened or an exception will be thrown.

With these two things in mind:

Using con As New SqlConnection(ConfigurationManager.ConnectionStrings("sqlConnectionString").ConnectionString),
      command As New SqlCommand("GetUserLoginDetails", con)
    '...
 
    con.Open()
 
    Using reader As SqlDataReader = command.ExecuteReader()
        '...
    End Using
End Using