?
Solved

Web App won't Connect to SQL Server

Posted on 2009-05-01
17
Medium Priority
?
370 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:MCallas
  • 9
  • 7
17 Comments
 
LVL 6

Expert Comment

by:RPCIT
ID: 24284030
try your connection string like this...

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

Expert Comment

by:hehdaddy
ID: 24284057
Can you try moving your cn.Open() out of the Try block? I am interested in seeing the results.
0
 

Author Comment

by:MCallas
ID: 24284072
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:MCallas
ID: 24284112
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
 
LVL 6

Expert Comment

by:RPCIT
ID: 24284129
which executereader is it erroring on?
0
 

Author Comment

by:MCallas
ID: 24284159
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
 
LVL 6

Expert Comment

by:RPCIT
ID: 24284186
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
 

Author Comment

by:MCallas
ID: 24284213
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
 
LVL 6

Expert Comment

by:RPCIT
ID: 24288064
have you checked to make sure that your user is explicitly granted writes to the specifit database in question?
0
 

Author Comment

by:MCallas
ID: 24288326
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
 
LVL 6

Expert Comment

by:RPCIT
ID: 24296687
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
 
LVL 6

Expert Comment

by:RPCIT
ID: 24296697
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
 

Author Comment

by:MCallas
ID: 24296932
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
 

Author Comment

by:MCallas
ID: 24296966
RPCIT,
I am getting the following error message:
Cannot open database requested in login 'CAFDT.mdb'. Login fails.
Login failed for user 'abc'.
0
 
LVL 6

Accepted Solution

by:
RPCIT earned 2000 total points
ID: 24297016
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
 

Author Closing Comment

by:MCallas
ID: 31577093
RPCIT, thank you very much. I can't tell you how much I appreciate your assistance. Have a great day!
0
 

Author Comment

by:MCallas
ID: 24297328
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question