Re: Problem deploying forms authorization

Dear Steven,

Thank you for your prompt response. Your assumptions are generally correct.
Situation that works:
Development environement : Visual Studio hosting the application and SQL Server Express on the local machine with the database information:

Situation that has problems:
Production environment: IIS hosts the application and SQL Server 2005 is on the same machine as the production environment (domain controller).

Here is the code that does my authorization and authentication:
Public Function AuthenticateUser(ByVal uname As String, ByVal pword As String) As Boolean
Try
Dim myds As New Authenicateds
Dim myta As New AuthenicatedsTableAdapters.AuthenticatTableTableAdapter
myta.Fill(myds.AuthenticatTable, uname, pword)
If myds.AuthenticatTable.Rows.Count > 0 Then
Return True
Else
Return False
End If

Catch ex As Exception
Return False
'Return ex.Message
End Try


End Function
Public Function GetUserRole(ByVal uname() As String) As Boolean

Try

Dim myds As New Authenicateds
Dim myta As New AuthenicatedsTableAdapters.RolesTableAdapter
myta.Fill(myds.Roles, uname(0))
If myds.Roles.Rows.Count > 0 Then
Dim myrow As Authenicateds.RolesRow
For Each myrow In myds.Roles.Rows
Roles.AddUsersToRole(uname, myrow.Role)

Next

Else
Roles.AddUsersToRole(uname, "User")

End If

Catch ex As Exception
Return False

End Try


End Function

''This is the login page code calling from a page with a login control


Protected Sub Login1_Authenticate(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.AuthenticateEventArgs) Handles Login1.Authenticate
Dim uname As String = Me.Login1.UserName
Dim mystr As String
Dim pword As String = Me.Login1.Password
Dim mycomp As New Handle()
Dim unamea() As String


Try

e.Authenticated = mycomp.AuthenticateUser(uname, pword)
If e.Authenticated Then
Dim myname(0) As String
myname(0) = uname
mycomp.GetUserRole(myname)


End If
If Not e.Authenticated Then

Response.Write("Try again")
End If
Catch ex As Exception
Response.Write(ex.Message)
End Try

End Sub

I'm referring to the production environment scenerio:

The queries in the authentication subroutines
myta.Fill(myds.AuthenticatTable, uname, pword)
myta.Fill(myds.Roles, uname(0))

are executed on the server by the user defined in the impersonate attribute of the web.config. The application, however, appears to hang after calling these queries and won't open the page its directed to.

If I eliminate the authenication and go directly to the redirected page,
which is the heart of the appication. it opens fine and queries the database.

I cannot find any calls to another server by mistake.

Could the authentication class be looking for something I didn't define? or need some other permissions?

Haim

Steven Cheng[MSFT] wrote:
Hi Haim,

From your description, I understand you're using ASP.NET forms based authentication, and will use a custom SQL Server database to authenticate the username/password credentials. However, you found that it start report connection error after you move the database to a production database server ,correct?

Regarding on this issue, as you mentioned that "this error is in spite of the fact that my queries called in the code have ran properly on sql server.", so you can make sure that the ASP.NET application has correctly connected to the SQL Server database and executed query on it, correct?

Since there are many things that could be related to the problem here, I think we'd better perform some tests to isolate the issue:

** Currently your ASP.NET application is still on the development machine and connect to the remote SQL Server on production machine, correct?

** If you've made sure that the query has been executed on the remote SQL Server 2005 instance, is there any other code in your ASP.NET application that will visit a remote SQL Server machine which may return such exception?

** In the SQL profiler trace message, have you seen the expected security identity from the ASP.NET application(as you've used <impersonate userName= .. password=...> to force impersonate)?

** You can create a console application which use the same security account to execute some SQL scripts against that remote SQL database to see whether it works or will report the same error.

Anyway, if will be much helpful if we can recreate the symptom under some simplified condition.

If there is anything else I missed or you have any other finding, please feel free to post here.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead



==================================================

Get notification to my posts through email? Please refer to http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.



Note: The MSDN Managed Newsgroup support offering is for non-urgent issues where an initial response from the community or a Microsoft Support Engineer within 1 business day is acceptable. Please note that each follow up response may take approximately 2 business days as the support professional working with you may need further investigation to reach the most efficient resolution. The offering is not appropriate for situations that require urgent, real-time or phone-based interactions or complex project analysis and dump analysis issues. Issues of this nature are best handled working with a dedicated Microsoft Support Engineer by contacting Microsoft Customer Support Services (CSS) at http://msdn.microsoft.com/subscriptions/support/default.aspx.

==================================================


This posting is provided "AS IS" with no warranties, and confers no rights.

.

Relevant Pages

  • Error Trap Connection to SQL server
    ... I am trying to establish links from Ms Access to tables in a SQL server ... the database is attached ... Dim rs1 As DAO.Recordset ... Dim strLinkName As String ...
    (microsoft.public.access.security)
  • Help with Error Trapping Connection to SQL server
    ... I am trying to establish links from Ms Access to tables in a SQL server ... the database is attached ... Dim rs1 As DAO.Recordset ... Dim strLinkName As String ...
    (microsoft.public.access.modulesdaovba)
  • Re: using VBA to insert records from access database to sql server
    ... Brendan and Tim, ... insert records from Access table to SQL server table with one error which I ... Dim oConn As ADODB.Connection ... Dim strInsert As String ...
    (microsoft.public.access.modulesdaovba)
  • Re: Help with MS Access Error Trap
    ... your access code and is not a SQL Server issue per se. ... > Dim rs1 As DAO.Recordset ... > Dim strLinkName As String ... > ' Now remove previous link info ...
    (microsoft.public.sqlserver.programming)
  • Re: Programmatically changing a SQL view in a ADP
    ... Public Sub TestCreateView() ... Dim cn As ADODB.Connection ... Dim rst As ADODB.Recordset ... to alter the 'Select' statement of a SQL Server view from within a ADP ...
    (microsoft.public.access.modulesdaovba)