MS Access on a web farm

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • GNoter
    New Member
    • Feb 2007
    • 9

    MS Access on a web farm

    Is there any way to set up connecting to an MS Access file on a 3rd server (which is on a domain) from a Web Farm (which is not on the domain)?

    Hardware/OS:
    Windows Server 2003
    Web Farm (two web servers which are network load balanced, or NLBs)
    ASP.NET v1.1
    Microsoft Access 2003

    We're seeking to set up an NLB webfarm. One of the web-apps uses a MS Access database.
    Given the NLB (2 boxes), the MS Access database can't reside on either (to remove "single point of failure" scenario).
    The NLBs are not part of the domain.
    We've put the MS Access file on a 3rd server (which is part of the domain).
    We can see files on the 3rd server (such as serving up images and other pages. In the Web.Config, we put this connection string:
    \\Server3\MSAcc ess\DatabaseFil e.mdb

    The folder \\Server3\MSAcc ess| is shared for full control for everyone; guests included (yeah, security hole...)

    We keep getting this following error, to which we've not been successful in resolving:

    The Microsoft Jet database engine cannot open the file '\\Server3\MSAc cess\DatabaseFi le.mdb'. It is already opened exclusively by another user, or you need permission to view its data.

    I did visit Microsoft's website and read through several KB articles, but no success; #253580, #315276 (NTRights Utlitity)


    Is there any way to "mount" \\Server3\MSAcc ess\ shared folder as a specific lettered drive when the sever starts up (vs having to log on)?

    Any insights appreciated.

    Gary Noter
  • MMcCarthy
    Recognized Expert MVP
    • Aug 2006
    • 14387

    #2
    Hi Gary

    I've frequently used Access databases though a citrix farm but unfortunately had nothing to do with setting them up there. I will have a word with some experts who may be able to help and see what can be done.

    Mary

    Comment

    • Motoma
      Recognized Expert Specialist
      • Jan 2007
      • 3236

      #3
      I am entirely unfamiliar with Access; however, I do have a bit of knowledge with databases. What it sounds like you have here, is a permissions conflict. I don't know how you are trying to access the Access database, because you did not post any code to accompany your question, but the error alludes to the idea that your user does not have permissions on the database (not to the file mind you, the database itself). Either that, or someone else has an exclusive lock on the database. Is the file in use by anyone else?

      Comment

      • MMcCarthy
        Recognized Expert MVP
        • Aug 2006
        • 14387

        #4
        Good suggestion Motoma. I assumed the database was already working correctly in a multi user environment.

        In the database go to Tools - options - advanced and check that the default open mode is not set to Exclusive. It should be set to Shared. Also the Default Record Locking should be set to Edited record although this would not be causing the error.

        Mary

        Comment

        • Killer42
          Recognized Expert Expert
          • Oct 2006
          • 8429

          #5
          Originally posted by mmccarthy
          In the database go to Tools - options - advanced and check that the default open mode is not set to Exclusive. It should be set to Shared. ...
          Quick question, mostly to satisfy my curiosity. Does this apply to that specific database, or to Access as a whole? (And does it need to be set per user?)

          Comment

          • MMcCarthy
            Recognized Expert MVP
            • Aug 2006
            • 14387

            #6
            Originally posted by Killer42
            Quick question, mostly to satisfy my curiosity. Does this apply to that specific database, or to Access as a whole? (And does it need to be set per user?)
            Applies to specific database rather than Access or a user. If the database is set to Exclusive then the default open mode of that database will be in exclusive mode and no one else will be able to open it except in read only mode.

            Mary

            Comment

            • GNoter
              New Member
              • Feb 2007
              • 9

              #7
              Motoma, all,

              I did post "some" code, per se.

              I'm using ASP.NET v1.1, using a standard OLEDb connection (referencing the database by path [see code below]).

              From the ASP.NET v1.1 "web.config " file (I'll refer to this as ConnectStringA)
              Code:
               <appSettings>
                  <add key="strConnectAccess" value="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\Server3\MSAccess\DatabaseFile.mdb"/>
              </appSettings>
              actual "connect" code:
              ... I'm pausing here.
              I went to go look at the connection code and there is a direct inline connection string to another database which ap[ears to not be failing:
              \\Server3\C$\MS Access\LogError .mdb
              --> I'll refer to this as ConnectStringB,

              The difference between ConnectStringA and ConnectStringB is that ConnectStringA assumes a shared folder, whereas ConnectStringB goes direct, as it includes the "\C$" element.

              Well, I added the "\C$" to ConnectStringA, (now "\Server3\C$\MS Access\Database File.mdb") and it still fails.


              Yes, I would agree there is some sort of permissions error. The database is set for Shared.

              HEre is my "connect" code (modified to protect a client's database)
              Code:
                  Sub btnlogin_click(ByVal sender As Object, ByVal e As EventArgs)
                      Dim conpw As OleDb.OleDbConnection
                      Dim cmdpw As OleDb.OleDbCommand
                      Dim strsql As String
              
                      Dim dappw As OleDb.OleDbDataAdapter
                      Dim dstpw As DataSet
                      Dim rowpw As DataRow
                      Dim bldpw As OleDb.OleDbCommandBuilder
                      Dim inti As Integer
                      Dim rdrpw As OleDb.OleDbDataReader
              
                      Dim xx As String
                      Dim yy As String
                      Dim strmsg As String
                      Dim strcounties As String
                      Dim lngposition As Long
                      Dim strIsCSR As String
              
                      If txtIsCSR.Checked = "True" Then
                          Session.Contents("IsUser2") = "True"
                          Session.Contents("userName2") = txtUserName.Value
                          Session.Contents("userPass2") = txtPassword.Value
                          Response.Redirect("user2Logon.aspx")
                      End If
              
                      lngposition = InStr(txtPassword.Value, "'")
                      If lngposition > 0 Then
                          Response.Redirect("loginError.aspx")
                      End If
              
              
                      conpw = New OleDb.OleDbConnection(ConfigurationSettings.AppSettings("strConnectAccess"))
                      conpw.Open()
              
              
                      strsql = "SELECT * " & _
                      "FROM [ User] " & _
                      " WHERE ( ([ User]].UserName = '" & txtUserName.Value & "') AND ([ User].password = '" & txtPassword.Value & "') )  "
              
                      cmdpw = New OleDb.OleDbCommand(strsql, conpw)
                      rdrpw = cmdpw.executereader
              
                      xx = 0
                      While rdrpw.read
                          xx = xx + 1
                          strmsg = rdrpw.item("userID") + rdrpw.item("userName")
                          strcounties = strcounties + strmsg
                      End While
              
                      conpw.close()
              
                      If xx > 0 Then
                          Session.Contents("counties") = txtMemberid.Value
                          Response.Redirect("Here.aspx")
                      Else
                          If Len(Trim(Session.Contents("logintrys"))) = 0 Then
                              Session.Contents("logintrys") = 0
                          End If
                          ctry = (Session.Contents("logintrys"))
                          ltrys = CInt(ctry) + 1
                          If ltrys > 6 Then
                              Response.Redirect("lockout.aspx")
                          End If
                          ctry = CStr(ltrys)
                          Session.Contents("logintrys") = ctry
                          Response.Redirect("loginError.aspx")
                      End If
              
                  End Sub
              So, in all, this error [below] is occuring, it does point to "permission s" of some kind. Thing is, how to I "connect" from a webfarm not on a domain to a server (Server3) which is on a domain?
              The Microsoft Jet database engine cannot open the file '\\Server3\C$\M SAccessDBs\Data base.mdb'. It is already opened exclusively by another user, or you need permission to view its data.

              Comment

              • Motoma
                Recognized Expert Specialist
                • Jan 2007
                • 3236

                #8
                Try running the app from the machine the database is on (\\Server3\C$\M SAccessDBs\Data base.mdb should still work from the local machine). Do you still get the error?

                Comment

                • GNoter
                  New Member
                  • Feb 2007
                  • 9

                  #9
                  Motoma,

                  Yes, the database access works just fine when running the application on the same server (no access errors) and using the UCN name
                  ( \\Server3\MSAcc essDB\DatabaseF ile.mdb )

                  Comment

                  • Motoma
                    Recognized Expert Specialist
                    • Jan 2007
                    • 3236

                    #10
                    Originally posted by GNoter
                    Motoma,

                    Yes, the database access works just fine when running the application on the same server (no access errors) and using the UCN name
                    ( \\Server3\MSAcc essDB\DatabaseF ile.mdb )
                    And have you been able to open the database in Access, using the filename \Server3\MSAcce ssDB\DatabaseFi le.mdb?

                    Comment

                    • Killer42
                      Recognized Expert Expert
                      • Oct 2006
                      • 8429

                      #11
                      Originally posted by GNoter
                      So, in all, this error [below] is occuring, it does point to "permission s" of some kind. Thing is, how to I "connect" from a webfarm not on a domain to a server (Server3) which is on a domain?
                      Perhaps this is more to do with the security settings in use on the connection between the webfarm and server, rather than the specific folder permissions.

                      Comment

                      • Killer42
                        Recognized Expert Expert
                        • Oct 2006
                        • 8429

                        #12
                        Originally posted by mmccarthy
                        Applies to specific database rather than Access or a user. If the database is set to Exclusive then the default open mode of that database will be in exclusive mode and no one else will be able to open it except in read only mode.
                        Thanks for that, Mary. However, after browsing around the settings, I would question this response.

                        Other options nearby include "open databases using record-level locking" and "Default file format". Neither of these would make sense for a single database, only for Access as a whole.

                        Comment

                        Working...