I want to use a separate/ named schema in an Azure WebSite app to implement a multi-tenancy in separate schemas pattern with Azure SQL. My app runs correctly in Visual Studio but not on Azure. I searched StackOverflow for similar issues, and found: This answer suggests it can't be done: Multi-tenant application on Windows Azure. This question does not apply directly to named schemas: Azure SQL database working when ran locally but not when published to Azure.
For the MVC4 app I am developing I configured the Azure SQL database to use a named schema and modified the Connection String login name and password. When I tried to register the first user it threw the error message "Invalid object name 'UserProfile'" from Azure SQL. I checked the stack trace and everything looked reasonable so I reviewed the code for the SimpleMembershipProvider and did not see anything that should break when using a named schema.
As a Proof of Concept, I opened Visual Studio 2012 and created a new MVC4 app with .NET Framework 4.5 using the Internet Application template, which of course implements the Simple Membership Provider. I opened the Azure console and created a new web site with new database, then published the app to Azure. I ran the app and clicked the Register link to add the first user. This was successful, and I was able to logout, login, etc.
I then modified the PoC app's Azure SQL configuration by creating a new named schema, login and user. I added the db_owner role for the new user in the new database. I altered the user's default schema and transferred the tables created by the initial run into the named schema. I confirmed that the user's default schema is correct and the tables are in the named schema. I changed the user name and password in the Connection String section of the Publish dialog and again published the app from Visual Studio. I opened the website in Chrome and clicked the Register link. I entered a user name and password and got the error message "Invalid object name 'UserProfile'". I got the same error when atttempting to login with the user created in the first run.
I am looking for ideas about what to check next. I can work around this issue, but if I missed anything simple I would rather fix things the efficient way. Any ideas?
It seems I found a bug in Azure WebSites that causes the Connection String to not be updated by publishing the web site.
There is a connection string on the Azure Management portal's Configure page for the web site. This connection string is used to connect to the web site's database. It initially contains the User Id and Password used to create the database. Entering a different User ID and Password in the Visual Studio web site project (in Web.config or in the Publish Web dialog) has no effect on the connection string in Azure. This caused a problem in my specific situation, but would also be an issue for anyone who does not use the sa-like server-level user for accessing the database from the web site.
The workaround I found for this issue is to directly modify the Connection String in Azure on the web site's Configure page, instead of relying on effectiveness of a change in the Visual Studio project. This resolved the problem and now the named schema is working correctly.