Web App won't Connect to SQL Server

Dear Experts,
I will do my best to keep this question brief. I created a Web app in VS2008 (.NET 2.0) connecting to SQL 2005 Express db. Everything works fine (of course).

Installed Web app on production ServerA. Created a new db named "ABC" in SS 2000 on production ServerB.

In db "ABC" on ServerB, I  created a user "abc" with password "abc" and permitted public; db_owner; db_datareader; and db_datawriter.

From the Web server (ServerA), I am using the following connection string:  connectionString="Data Source=serverb\serverbsql;Initial Catalog=ABC.mdf;User ID=abc;Password=abc;"

When I run the script from the web app, cn.open() does not throw an error. However, as soon as I try to run the ExecuteReader() command, it throws the following error:  ExecuteReader requires an open and available Connection. The connection's current state is closed.

Web server (ServerA) has SQL Server 2005 Express installed on it. Again, ServerB has SQL Server 2000.

The only thing that changed from development to production was DB went from local SQL Server 2005 Express to Remote SQL Server 2000. We were able to connect to the database from a workstation using ODBC (from MS Access).

I am a novice at installing web apps and databases on SQL Server. Any help or suggestions would be greatly appreciated.
Thanks.

Here is the code from the login page that is throwing the error:
Imports System.Data.SqlClient
 
Partial Class _Default
    Inherits System.Web.UI.Page
 
    Protected Sub cmdLogin_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdLogin.Click
 
        'If authenticated, show menu. Administrators see Admin menu option. 
        Dim gsCnStr As String = ConfigurationManager.connectionStrings("CAFData").connectionString
        Dim cn As New SqlConnection(gsCnStr)
        Dim sUser As String = txtUserName.Text
        Dim sql As String = "SELECT USPassword, USRole FROM Users WHERE USLoginName = '" & sUser & "'"
        Dim cmdSelect As New SqlCommand(sql, cn)
 
	Dim fHasConnection as Boolean = True
        Try
           'Try to open the connection
           cn.Open()
        Catch ex As Exception
	     Dim sErrMsg As String = ex.Message
        End Try
 
            'Get the login pw
            Dim reader As SqlDataReader
            reader = cmdSelect.ExecuteReader()
            If reader.Read() Then
                If txtPassword.Text <> reader("USPassword") Then
                    'Login failed
                    reader.Close()
                    Dim objClientAlert As New ClientAlert
                    objClientAlert.CreateClientAlert("User name or password incorrrect. Please try again.", Me)
                    txtUserName.Focus()
                Else
                    'Login succeeded. Capture the users role.
                    Dim intRole As Integer = 0
                    intRole = reader("USRole")
                    reader.Close()
                    'Authorize the user.
                Web.Security.FormsAuthentication.SetAuthCookie(sUser, False)
                    'Load the user's role into a temporary cookie.
                    Dim userCookie As New HttpCookie("UserInfo")
                    userCookie("Role") = intRole
                    Response.Cookies.Add(userCookie)
                    'Need to capture the EmpID from the Employees table using the LoginName.
                    sql = "SELECT EMEmpID FROM Employees WHERE EMLoginName = '" & sUser & "'"
                    Dim cmdSelectEmpID As New SqlCommand(sql, cn)
                    reader = cmdSelectEmpID.ExecuteReader()
                    If reader.Read() Then
                        Dim intEmpID As Integer = 0
                        intEmpID = reader("EMEmpID")
                        reader.Close()
                        'Load the user's EmpID into a temporary cookie.
                        userCookie("EmpID") = intEmpID
                        Response.Cookies.Add(userCookie)
                    End If
                    'Check for password change
                    If txtPassword.Text = "password" Then
                        'Force pw change
                        Response.Redirect("~/PasswordChange.aspx")
                    End If
                    Select Case intRole
                        Case 1, 3, 5, 7
                            Response.Redirect("MenuMain.aspx")
                        Case 4, 6
                            Response.Redirect("~/Administration/MenuAdmin.aspx")
                        Case Else
                            Response.Redirect("~/Administration/MenuAdmin.aspx")
                    End Select
                End If
            Else
                'Login failed
                reader.Close()
                Dim objClientAlert As New ClientAlert
                objClientAlert.CreateClientAlert("User name or password incorrrect. Please try again.", Me)
                txtUserName.Focus()
            End If
            If User.Identity.IsAuthenticated = False Then
                'Not
                'Server.Transfer("default.aspx")
            Else
                'Authed!
            End If
 
    End Sub
End Class
 
...and here's the connection string in the web.config:
	<connectionStrings>
 <add name="CAFData" connectionString="Data Source=serverb\serverbsql;Initial Catalog=ABC.mdf;User ID=abc;Password=abc;"
   providerName="System.Data.SqlClient" />
 </connectionStrings>

Open in new window

MCallasAsked:
Who is Participating?
 
RPCITConnect With a Mentor Commented:
I'm almost positive that you don't want the .mdb in the connection string.  ... also.. change "User ID" to "User"


<add name="CAFData" connectionString="Server=serverb\serverbsql;Database=CAFData;User=abc;Password=abc;"
   providerName="System.Data.SqlClient" />

In enterprise manager, right click on the connection and click properties.  The value for "name" needs to be in the "server" section.

0
 
RPCITCommented:
try your connection string like this...

<add name="CAFData" connectionString="Server=serverb\serverbsql;Database=ABC;User=abc;Password=abc;"
   providerName="System.Data.SqlClient" />
0
 
hehdaddyCommented:
Can you try moving your cn.Open() out of the Try block? I am interested in seeing the results.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
MCallasAuthor Commented:
Getting the same exact error:  ExecuteReader requires an open and available Connection. The connection's current state is closed.
	<connectionStrings>
  <add name="CAFData" connectionString="Server=serverb\serverbsql;Database=CAFDT.mdf;User=abc;Password=abc;"
   providerName="System.Data.SqlClient" />
 </connectionStrings>

Open in new window

0
 
MCallasAuthor Commented:
hehdaddy,
Making progress. Here is the error I am getting now:
Cannot open database requested in login 'CAFDT.mdf'. Login fails.
Login failed for user 'abc'.
0
 
RPCITCommented:
which executereader is it erroring on?
0
 
MCallasAuthor Commented:
RPCIT,
Now that I move the cn.open() out of the Try block as suggested by hehdaddy, it throws the following error:  Cannot open database requested in login 'CAFDT.mdf'. Login fails.
Login failed for user 'abc'.
0
 
RPCITCommented:
in my example of the connection string.. I pulled out the .mdf  try ;Database=CAFDT;

another question...  with "server=serverb\serverbsql"  .. is serverb the machine name, and serverbsql an instance name?
0
 
MCallasAuthor Commented:
RPCIT,
When I remove the .mdf, I get an error reading "User name or password incorrect."

From my limited knowledge, serverb is the server name (serverb.DomainName) and serverbsql, I thought, was the name of the SQL Server. Don't really know if it is an instance name or not.
0
 
RPCITCommented:
have you checked to make sure that your user is explicitly granted writes to the specifit database in question?
0
 
MCallasAuthor Commented:
RPCIT,
To the best of my knowledge. In Enterprise Manager, I have the following under Security:
Logins Name:  abc
Logins Type:  Standard
Logins Server Access:  Permit
Logins Default Database:  CAFDT
Logins Default Language:  English
This login password is "abc"
Server Roles:  None

Under Databases --> CAFDT --> Users
Login name:  abc
User name: abc
Database role membership:
public
db_owner
0
 
RPCITCommented:
what tool are you using to manage this server?  SSMS?  If so.. in the object explorer (usually on the left side of the screen), right click on the connection and click register.. on the first screen there will be a field called "Server Name".. that needs to be in the "Server=serverb\serverbsql" section of the connection string.  

Also.. try to connect to the DB through SSMS as the ABC user you created.  You may get a better.  Also could you paste the latest version of your connection string that you are using?
0
 
RPCITCommented:
unfinished sentence: ...  "You may get a better.  "  should read "You may get a better error message."

they should give us like 2 mintues to edit the messages.
0
 
MCallasAuthor Commented:
RPCIT,
Understood on the editing time. <g>

I'm using Enterprise Manager, if that's what you mean. For what it's worth, I was able to connect to the database from SQL Query Analyzer using abc/abc on Friday. However, today, it doesn't challenge me for a name or password.
	<connectionStrings>
  <add name="CAFData" connectionString="Server=serverb\serverbsql;Database=CAFData.mdb;User ID=abc;Password=abc;"
   providerName="System.Data.SqlClient" />
 </connectionStrings>

Open in new window

0
 
MCallasAuthor Commented:
RPCIT,
I am getting the following error message:
Cannot open database requested in login 'CAFDT.mdb'. Login fails.
Login failed for user 'abc'.
0
 
MCallasAuthor Commented:
RPCIT, thank you very much. I can't tell you how much I appreciate your assistance. Have a great day!
0
 
MCallasAuthor Commented:
RPCIT,
Bingo! Those minor changes in the connection string did the trick! (dropping the .mdb <-- [I've got Access on my brain!] and changing User ID to User).

The Server section in the connection string did properly reflect the Name on connection properties in SQL EM.

Thanks a million for hanging in. As always, I appreciate everyone who gives their valuable time at Experts Exchange helping out the masses.
-M
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.