I have had two web app installations now where sometime after installation users were not able to login. Users see the message “The user name or password provided is incorrect.” This occurs even when the user name and password are correct. Behind the scenes, the service log file contains “SQLDBException… 16302… InsertSessionRecord…”
Upon investigation the system database installation script seems to have skipped the part where it adds some new columns to the WebServiceAccess table. An XEvent Profiler session revealed the stored procedure WebServiceAccess_Cleanup failed because the column “UserRecID” was missing from the table. Running WebApps2.01_Sys.sql again added that and some other columns, resolving the issue.
This is strange for two reasons. First, because the stored procedure is defined after the table definition changed in the script, and it ran without an error during the initial installation. Second, because it was possible to login during installation, but that changed later when users started trying to test the system. It’s as if the table definition reverted back without the new version of the stored procedure changing.
Hopefully this will help jog my memory the next time this happens, and may be helpful to someone else as well.
Hi Delmer, Nice article! I’m not sure why it would seemingly revert, but I have fixed this issue by running sp_refreshview vs_WebServiceAccess on the APP db; that view is a ‘select * from SYSTEMDB..webservicesaccess) which needs to be updated to reflect changes made to the underlying table. It could potentially be the order in which the APP and SYS script are run? APP view gets created before the SYS table is updated to add new columns and then doesn’t reference the newly added columns? That doesn’t explain why it disappears though??? That’s an odd one….Marc
I ran into this issue again today. The customer had refreshed test databases from production over the weekend. The issue may have recurred due to an action they took while doing that. The resolution in the original post fixed it, although the message in the logs didn’t mention “InsertSessionRecord”. In this case running “sp_refreshview vs_WebServiceAccess” didn’t help. Running the full WebApps2.01_Sys.sql script did.
This allowed administrators to login, but for users it was also necessary to Grant Execute on WSL_ProjectAttention and GetEmployeeData to the Trusted Web Service account.
Thank you so much!