Problems with Membership.FindUsersByName() in SQL Azure

Question

Hi,

I am migrating a website to Windows Azure and also migrating SQL Server to SQL Azure.

I found that the Membership function FindUsersByName() does not work as in the on premises server.  It works ok if I pass a normal string to the function (like "bob" will return all username that contains "bob" like bobby or johnbob).  However, if I pass the string "a%" to the function (which is supposed to return all username begin with 'a'), it only works in the old server but not in the Azure environment.

I check with msdn library and it says that the old provider, SqlMembershipProvider, performs its search using a LIKE clause.  But I use 'System.Web.Providers.DefaultMembershipProvider' in the Azure site.  I guess these two providers implement this differently.

Does anyone know how to retrieve only those username begin with letter 'a' (or other letters) when using the 'System.Web.Providers.DefaultMembershipProvider'?  Since I have over 20 thousands members, I don't want to retrieve all members then do the filtering afterwards.  I want to only retrieve those with 'a' only in the first place.

Thank you.

---

- brian

Answer 1

Hi,

As far as i know, DefaultMembershipProvider default connection string is DefaultConnection that use SQL Server Express as the default database instance, have you change it to SQL Azure? Because local SQL Server is not the recommended way in cloud environment, so I think the suggestion is use SqlMembershipProvider.

Hope this helps.

---

Please mark the replies as answers if they help or unmark if not. If you have any feedback about my replies, please contact [e-mail] Microsoft One Code Framework

Answer 2

As said in my post, I am able to retrieve the member list from my sql azure database without problem.  I only have problem using the function FindUsersByName().

FYI, I have correctly set up the DefaultConnection as follow:

<add name="DefaultConnection" connectionString="Server=tcp:cd3xxxxxxx.database.windows.net,1433;Database=XxxxxxXxxxxx;User ID=XxxxxXxxxx@cd3xxxxxxx;Password=xxxxxxxxxx;Trusted_Connection=False;Encrypt=True;MultipleActiveResultSets=True" providerName="System.Data.SqlClient" />

Thank you.

---

- brian