Named schema doesn't work in Azure SQL
I want to use a separate / named schema in an Azure WebSite application to implement a multi-tenant lease in a separate schema template with Azure SQL. My app works correctly in Visual Studio but not Azure. I searched StackOverflow for similar problems and found: This answer assumes this is not possible: Multi-user application on Windows Azure . This question is not directly related to named schemas: Azure SQL Database works when run locally, but not when published to Azure .
In an MVC4 application I am developing, I configured an Azure SQL Database to use a named schema and changed the name and password for the connection string. 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 looked through the code for SimpleMembershipProvider and didn't 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 application with .NET Framework 4.5 using the Internet Application Template, which Simple Membership Provider implements of course. I opened the Azure console and created a new website with a new database and then published the app to Azure. I launched the application and clicked the Register link to add the first user. It was successful and I was able to log out, log back in, etc.
Then I changed the Azure SQL configuration of the PoC application, creating a new named schema, login and user. I added the db_owner role for the new user in the new database. I changed the default schema for the user and passed the tables created on first run to the named schema. I have verified that the default user schema is correct and the tables are in the named schema. I changed the username and password in the Connection String section of the Publish dialog and republished the application from Visual Studio. I opened the website in Chrome and clicked the Register link. I entered my username and password and got the error "Invalid object name" UserProfile ". I got the same error when trying to log in with the user created in the first run.
I'm looking for ideas on what to check next. I can work around this problem, but if I missed something simple I would rather fix the situation in an efficient way. Any ideas?
source to share
I seem to have encountered a bug in Azure WebSites that causes the connection string to not update when publishing the website.
On the Azure Management page, the Setup page for the website has a connection string. This connection string is used to connect to the website database. It first contains the user ID and password used to create the database. Entering a different user ID and password in a Visual Studio website project (in Web.config or in the Publish to Web dialog box) does not affect the connection string in Azure. This caused a problem in my particular situation, but would also be a problem for someone not using the sa-like server user to access the database from the website.
The workaround I found for this problem is to directly change the connection string in Azure on the Configure page on the website, instead of relying on the efficiency of changing the Visual Studio project. This resolved the problem and the named schema now works correctly.
source to share