Login Failed for user...

[bruce]

The information you given is not specific enough for us to help.

Please provide

1) exact error message or url that cause error
2) code snippet
3) what database are you connecting to


[b]quote:Originally posted by KPayne

I am receiving a 'Login failed for user ......." exception in my application. It happens after I've already connected to the server with the same connection string (I use a connection string in web.config) and performed some queries. All of the data operations run from a Web Service and all functions in the web service use the same connection object.

I have confirmed that all transactions are committed or rolled back before the routine that triggers the error starts. I have confirmed that there are no open DataReaders or other open connections to the database when the routine starts. There are no programming errors in the code or stored procedure as I have tested it thoroughly in the development environment.

I cannot pinpoint the exact location of the error because I am simply catching the exception and reporting the error message back to the user and I cannot reproduce this error on my development machine. The production database is identical to the development database - I had just finished copying the development database over the production one.

My question is, why would I receive this error when I've already opened and closed the connection numerous times and am simply attempting to perform some more operations through the same connection?

I would appreciate a response ASAP. The project is on hold until I can solve this error.
</blockquote id="quote"></font id="quote">

[KPayne]

I am receiving a 'Login failed for user ......." exception in my application. It happens after I've already connected to the server with the same connection string (I use a connection string in web.config) and performed some queries. All of the data operations run from a Web Service and all functions in the web service use the same connection object.

I have confirmed that all transactions are committed or rolled back before the routine that triggers the error starts. I have confirmed that there are no open DataReaders or other open connections to the database when the routine starts. There are no programming errors in the code or stored procedure as I have tested it thoroughly in the development environment.

I cannot pinpoint the exact location of the error because I am simply catching the exception and reporting the error message back to the user and I cannot reproduce this error on my development machine. The production database is identical to the development database - I had just finished copying the development database over the production one.

My question is, why would I receive this error when I've already opened and closed the connection numerous times and am simply attempting to perform some more operations through the same connection?

I would appreciate a response ASAP. The project is on hold until I can solve this error.

[KPayne]

The exact error message is: Login failed for user '**********'. I have replaced the username with asterisks.

The function that triggers the error is:

<WebMethod(Description:="Updates a record in the Lead table")> _
Public Function UpdateLead( _
ByVal LeadID As Integer, _
ByVal LeadTypeID As Integer, _
ByVal PromotionID As Integer, _
ByVal SourceID As Integer, _
ByVal Name As String, _
ByVal Address As String, _
ByVal City As String, _
ByVal State As String, _
ByVal PostalCode As String, _
ByVal Country As String, _
ByVal Phone As String, _
ByVal Fax As String, _
ByVal Phone2 As String, _
ByVal Email As String, _
ByVal LeadData As String, _
ByVal ValidateOnly As Boolean) As DataOperationResult

Dim LeadDataXmlDocument As New XmlDocument()
Dim Result As New DataOperationResult()

TMSConnection.Open()
Dim Transaction As SqlTransaction = TMSConnection.BeginTransaction()

Try
' Format textbox data
State.ToUpper()
Dim PhoneFormatter As New FormatPhone()

If Phone.Length > 0 Then
PhoneFormatter.Convert(Phone)
Phone = PhoneFormatter.Phone
End If

If Fax.Length > 0 Then
PhoneFormatter.Convert(Fax)
Fax = PhoneFormatter.Phone
End If

If Phone2.Length > 0 Then
PhoneFormatter.Convert(Phone2)
Phone2 = PhoneFormatter.Phone
End If


' Validate the non-foreign key fields

' Name is required
If Name = String.Empty Then
Result.ResultFlag = -1
Result.DORValidationSteps.Add(New DORValidationStep(-1, "A Name is required", "A Name entry is required."))
End If

' Address & City, State & Country or Address & PostalCode & Country, phone, phone2 or email required
If Not (Address <> "" And ((City <> "" And State <> "" And Country <> "") Or (PostalCode <> "" And Country <> "")) Or Phone <> "" Or Phone2 <> "" Or Email <> "") Then
Result.ResultFlag = -1
Result.DORValidationSteps.Add(New DORValidationStep(-2, "A complete Address (Address & City, State & Country or Address & PostalCode & Country), Phone, Phone2 or Email is required.", "A complete Address, Phone Number or Email is required."))
End If

' Validate the foreign keys

Dim LeadTypeHelper As New SqlDataAdapterHelper(Transaction, "LeadTypeCheckID", "LeadType", "LeadTypeID")
If LeadTypeID <> LeadTypeHelper.ExecuteScalar(Transaction, "@LeadTypeID", LeadTypeID) Then
Result.ResultFlag = -1
Result.DORValidationSteps.Add(New DORValidationStep(-3, "The LeadTypeID must match a record in the LeadType table.", "A CONTACT TYPE entry is requried (Person or Entity/Business)."))
End If

Dim PromotionHelper As New SqlDataAdapterHelper(Transaction, "PromotionCheckID", "Promotion", "PromotionID")
If PromotionID <> PromotionHelper.ExecuteScalar(Transaction, "@PromotionID", PromotionID) Then
Result.ResultFlag = -1
Result.DORValidationSteps.Add(New DORValidationStep(-4, "The PromotionID must match a record in the Promotion table.", "An internal error occurred while processing your form. Please call to receive immediate assistance."))
End If

Dim SourceHelper As New SqlDataAdapterHelper(Transaction, "SourceCheckID", "Source", "SourceID")
If SourceID <> SourceHelper.ExecuteScalar(Transaction, "@SourceID", SourceID) Then
Result.ResultFlag = -1
Result.DORValidationSteps.Add(New DORValidationStep(-5, "The SourceID must match a record in the Source table.", "An internal error occurred while processing your form. Please call to receive immediate assistance."))
End If

Dim CountryHelper As New SqlDataAdapterHelper(Transaction, "CountryCheckAbbreviation", "Country", "CountryID")
If Country <> CountryHelper.ExecuteScalar(Transaction, "@Abbreviation", Country) Then
Result.ResultFlag = -1
Result.DORValidationSteps.Add(New DORValidationStep(-6, "The Country must match a record in the Country table.", "An internal error occurred while processing your form. Please call to receive immediate assistance."))
End If

' Validate the attached XML document (LeadData)
' Only validate if LeadData was included in the parameters
If (Not LeadData Is Nothing) And LeadData.Length > 0 Then
LeadDataXmlDocument.LoadXml(LeadData)
End If

If ValidateOnly Then
If Result.ResultFlag = 0 Then
Result.ResultMessage = "The data for the Lead is valid."
Else
Result.ResultMessage = "The data for the Lead is *NOT* valid. Consult the DORValidationSteps and DORTables nodes for specific reasons why the data did not pass the validation."
End If
Else
If Result.ResultFlag = 0 Then
Dim LeadHelper As New SqlDataAdapterHelper(Transaction, "LeadUpdate", "Lead", "LeadID")
Result.DORTables.Add(New DORTable("Lead"))
With Result.DORTables("Lead")
.PrimaryKeyName = "LeadID"
.PrimaryKeyValueInt = LeadHelper.ExecuteScalar(Transaction, "@LeadTypeID", LeadTypeID, _
"@PromotionID", PromotionID, _
"@SourceID", SourceID, _
"@Name", Name, _
"@Address", Address, _
"@City", City, _
"@State", State, _
"@PostalCode", PostalCode, _
"@Country", Country, _
"@Phone", Phone, _
"@Fax", Fax, _
"@Phone2", Phone2, _
"@Email", Email, _
"@LeadData", LeadDataXmlDocument.OuterXml, _
"@LeadID", LeadID)

.ResultMessage = "Lead updated successfully"
End With

Transaction.Commit()
With Result
.ResultFlag = 0
.ResultMessage = "Receipt updated successfully"
.ReturnData = .DORTables("Lead").PrimaryKeyValueInt
End With
End If
End If
Catch e As Exception
Transaction.Rollback()
With Result
.ResultFlag = -1
.ResultMessage = "An error occurred while attempting to update the Lead table. Check the ReturnData for details."
.ReturnData = e.Message
End With

Finally
TMSConnection.Close()
End Try

Return Result

End Function

I cannot provide a smaller code snippet because I cannot remotely debug the application.

The database I am connecting to is DB_18884.

The connection string from my web.config is: "Data Source=mssql01;Initial Catalog=DB_18884;User ID=********;Password=********"


As I wrote in my initial post, this error is frustrating because I am unable to trace through the code to get a better idea of exactly where it is failing. Everything works fine on my development computer. Additionally, the connection string is used for many other routines. They all function correctly.

When I commented out the Try..Catch block and I got a generic 500 error.

Keith Payne
Technical Marketing Solutions

[bruce]

OK.

This is the first thing you do. Disable Friendly error in IE. Here's how to do that

http://kb.discountasp.net/article.aspx?id=10052

This will tell you the exact error.

See if the real error resolves your problem.

[b]quote:Originally posted by KPayne

The exact error message is: Login failed for user '**********'. I have replaced the username with asterisks.

The function that triggers the error is:

<WebMethod(Description:="Updates a record in the Lead table")> _
Public Function UpdateLead( _
ByVal LeadID As Integer, _
ByVal LeadTypeID As Integer, _
ByVal PromotionID As Integer, _
ByVal SourceID As Integer, _
ByVal Name As String, _
ByVal Address As String, _
ByVal City As String, _
ByVal State As String, _
ByVal PostalCode As String, _
ByVal Country As String, _
ByVal Phone As String, _
ByVal Fax As String, _
ByVal Phone2 As String, _
ByVal Email As String, _
ByVal LeadData As String, _
ByVal ValidateOnly As Boolean) As DataOperationResult

Dim LeadDataXmlDocument As New XmlDocument()
Dim Result As New DataOperationResult()

TMSConnection.Open()
Dim Transaction As SqlTransaction = TMSConnection.BeginTransaction()

Try
' Format textbox data
State.ToUpper()
Dim PhoneFormatter As New FormatPhone()

If Phone.Length > 0 Then
PhoneFormatter.Convert(Phone)
Phone = PhoneFormatter.Phone
End If

If Fax.Length > 0 Then
PhoneFormatter.Convert(Fax)
Fax = PhoneFormatter.Phone
End If

If Phone2.Length > 0 Then
PhoneFormatter.Convert(Phone2)
Phone2 = PhoneFormatter.Phone
End If


' Validate the non-foreign key fields

' Name is required
If Name = String.Empty Then
Result.ResultFlag = -1
Result.DORValidationSteps.Add(New DORValidationStep(-1, "A Name is required", "A Name entry is required."))
End If

' Address & City, State & Country or Address & PostalCode & Country, phone, phone2 or email required
If Not (Address <> "" And ((City <> "" And State <> "" And Country <> "") Or (PostalCode <> "" And Country <> "")) Or Phone <> "" Or Phone2 <> "" Or Email <> "") Then
Result.ResultFlag = -1
Result.DORValidationSteps.Add(New DORValidationStep(-2, "A complete Address (Address & City, State & Country or Address & PostalCode & Country), Phone, Phone2 or Email is required.", "A complete Address, Phone Number or Email is required."))
End If

' Validate the foreign keys

Dim LeadTypeHelper As New SqlDataAdapterHelper(Transaction, "LeadTypeCheckID", "LeadType", "LeadTypeID")
If LeadTypeID <> LeadTypeHelper.ExecuteScalar(Transaction, "@LeadTypeID", LeadTypeID) Then
Result.ResultFlag = -1
Result.DORValidationSteps.Add(New DORValidationStep(-3, "The LeadTypeID must match a record in the LeadType table.", "A CONTACT TYPE entry is requried (Person or Entity/Business)."))
End If

Dim PromotionHelper As New SqlDataAdapterHelper(Transaction, "PromotionCheckID", "Promotion", "PromotionID")
If PromotionID <> PromotionHelper.ExecuteScalar(Transaction, "@PromotionID", PromotionID) Then
Result.ResultFlag = -1
Result.DORValidationSteps.Add(New DORValidationStep(-4, "The PromotionID must match a record in the Promotion table.", "An internal error occurred while processing your form. Please call to receive immediate assistance."))
End If

Dim SourceHelper As New SqlDataAdapterHelper(Transaction, "SourceCheckID", "Source", "SourceID")
If SourceID <> SourceHelper.ExecuteScalar(Transaction, "@SourceID", SourceID) Then
Result.ResultFlag = -1
Result.DORValidationSteps.Add(New DORValidationStep(-5, "The SourceID must match a record in the Source table.", "An internal error occurred while processing your form. Please call to receive immediate assistance."))
End If

Dim CountryHelper As New SqlDataAdapterHelper(Transaction, "CountryCheckAbbreviation", "Country", "CountryID")
If Country <> CountryHelper.ExecuteScalar(Transaction, "@Abbreviation", Country) Then
Result.ResultFlag = -1
Result.DORValidationSteps.Add(New DORValidationStep(-6, "The Country must match a record in the Country table.", "An internal error occurred while processing your form. Please call to receive immediate assistance."))
End If

' Validate the attached XML document (LeadData)
' Only validate if LeadData was included in the parameters
If (Not LeadData Is Nothing) And LeadData.Length > 0 Then
LeadDataXmlDocument.LoadXml(LeadData)
End If

If ValidateOnly Then
If Result.ResultFlag = 0 Then
Result.ResultMessage = "The data for the Lead is valid."
Else
Result.ResultMessage = "The data for the Lead is *NOT* valid. Consult the DORValidationSteps and DORTables nodes for specific reasons why the data did not pass the validation."
End If
Else
If Result.ResultFlag = 0 Then
Dim LeadHelper As New SqlDataAdapterHelper(Transaction, "LeadUpdate", "Lead", "LeadID")
Result.DORTables.Add(New DORTable("Lead"))
With Result.DORTables("Lead")
.PrimaryKeyName = "LeadID"
.PrimaryKeyValueInt = LeadHelper.ExecuteScalar(Transaction, "@LeadTypeID", LeadTypeID, _
"@PromotionID", PromotionID, _
"@SourceID", SourceID, _
"@Name", Name, _
"@Address", Address, _
"@City", City, _
"@State", State, _
"@PostalCode", PostalCode, _
"@Country", Country, _
"@Phone", Phone, _
"@Fax", Fax, _
"@Phone2", Phone2, _
"@Email", Email, _
"@LeadData", LeadDataXmlDocument.OuterXml, _
"@LeadID", LeadID)

.ResultMessage = "Lead updated successfully"
End With

Transaction.Commit()
With Result
.ResultFlag = 0
.ResultMessage = "Receipt updated successfully"
.ReturnData = .DORTables("Lead").PrimaryKeyValueInt
End With
End If
End If
Catch e As Exception
Transaction.Rollback()
With Result
.ResultFlag = -1
.ResultMessage = "An error occurred while attempting to update the Lead table. Check the ReturnData for details."
.ReturnData = e.Message
End With

Finally
TMSConnection.Close()
End Try

Return Result

End Function

I cannot provide a smaller code snippet because I cannot remotely debug the application.

The database I am connecting to is DB_18884.

The connection string from my web.config is: "Data Source=mssql01;Initial Catalog=DB_18884;User ID=********;Password=********"


As I wrote in my initial post, this error is frustrating because I am unable to trace through the code to get a better idea of exactly where it is failing. Everything works fine on my development computer. Additionally, the connection string is used for many other routines. They all function correctly.

When I commented out the Try..Catch block and I got a generic 500 error.

Keith Payne
Technical Marketing Solutions
</blockquote id="quote"></font id="quote">

[KPayne]

Bruce,

Following your suggestion I was able to isolate and correct the error. It turns out that SqlBuilder.DeriveParameters refuses to participate in a transaction. The reason that I was not able to see the error on my development machine was because I used the Microsoft Data Application Block, which caches the parameters. It never called DeriveParameters.

Thank you for your help, Bruce.

Keith Payne
Technical Marketing Solutions