Hi, we are using FBA Pack with SP2013 Foundation. Solutions was working all the time. But now, several users are unable to log in:
The server could not sign you in. Make sure your user name and password are correct, and then try again.
Reset password without success. tried to deactivate the user and got the following error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
This error matches the users that are not able to log in. Thought about duplicates in the database, but the user is unique.
Any ideas?
Kind regards,
Florian
That is pretty strange that only some people are not able to login and you get the error that the subquery returned more than 1 value. Can't say i've seen that error before. Like you did, I was going to suggest checking for duplicates in the database -
though that should not be allowed. What if you delete the user from the FBA Pack user management page and then recreate the user - does it work then? If you're still having errors after trying that, i'd check the sharepoint log at the point somebody is trying
to login and see if the error in the log suggests a cause. Other thing to do would be to run SQL Server Profiler against the membership database to see what the db is being queried for - and then you could run the same queries against the database and see
what is causing the error.
Hi Chris, thanks for the quick reply. I already recreated one of the users and got the same behaviour as before. Unfortunately there is nothing in the ULS log (even with everything set to verbose). I found an entry in the event viewer regarding STS and
invalid user/password, in fact the same message as on the login screen.
I think I will try it with the SQL Profiler.
I found the subquery in TRIGGER [dbo].[UDA_UpdateLastLogin] ON [dbo].[aspnet_Users]:
Select Userinfo.tp_ID from WSS_Content.dbo.UserInfo Userinfo where Userinfo.tp_Login = @FBAUSER+@LoweredUsername
Here we have some duplicates due to several site collections in the same database. Seems to be normal that SharePoint keeps users per site collection in UserInfo table. Can't believe I am the first admin with that issue.
Actually, the reason you're the first admin with this issue is that you're the only admin with this issue :). The standard aspnetdb sql server membership database actually has no triggers on it. So somebody has modified the database and added the triggers
(and of course possibly made other changes as well) - which i'm sure are what has caused the duplicates. I have not seen duplicates in an aspnetdb database before.
The duplicates are not in the aspnetdb, they are in the WSS_Content table UserInfo. There you get an entry for each user in each site collection. Just the trigger is in the aspnetdb (table aspnet_Users). I was able to reproduce this on a second system.
And I could confirm that this is also the issue why the login fails. As far as I know the systems have no modifications in the database, but I will check it again.
Edit: Sorry Chris, the trigger is a custom dev :-P Best example why you should keep your fingers from SharePoint databases, even if it's just read-only