Trying to deploy Asp.Net Memebership database to SQL Azure

I've been trying to get the ASP.net member ship proveider to work with the rest of my databse which is hosted in SQL Azure. I've run the appropriate SQL Azure specific scripts against the database to set it up. The scripts are available from Microsoft here: http://archive.msdn.microsoft.com/KB2006191

However, everytime I run my site in debug mode using Visual Studio 10, I get the following error:

An error occurred during the execution of the SQL file 'InstallCommon.sql'. The SQL error number is 5123 and the SqlException message is: CREATE FILE encountered operating system error 5(failed to retrieve text for this error. Reason: 15105) while attempting to open or create the physical file 'C:\USERS\ROBERTO\DOCUMENTS\VISUAL STUDIO 2010\PROJECTS\FLYINGSHAKESTORE\MVCMUSICSTORE\APP_DATA\ASPNETDB_TMP.MDF'. CREATE DATABASE failed. Some file names listed could not be created. Check related errors. Creating the ASPNETDB_74b63e50f61642dc8316048e24c7e499 database...

All other access to the database works perfectly. So its not my connection string.

I think that when the membership provider is first called, ASP.Net is trys to attach a new Memberhship database to SQLAzure (which it can't anyway) be开发者_运维知识库cause it thinks that one does not exist. I've no idea how to turn this behavior off.

Anyone know what I can do?

Thanks.

Roberto

It looks like you are actually still referencing the local file App_Data/ASPNETDB_TMP.MDF instead of the SQL Azure instance - so I suspect that while your ConnectionString for your main data is correct, then you also have a ConnectionString left lying around for the local database too? Check in the membership providers within the web.config

I think by default the asp.net mebership system uses a connectionstring called LocalSqlServer. Set that up in your web.config and you should be good to go.

<configuration>
    <connectionStrings>
      <add name="LocalSqlServer"
         connectionString="Connectionstring..."  
         providerName="Provider..." />
    </connectionStrings>
</configuration>

Thanks Guys.

After some experimentation, this is what I came up with:

<roleManager enabled="true" defaultProvider="AspNetSqlRoleProvider">
        <providers>
            <clear/>
            <add name="AspNetSqlRoleProvider" connectionStringName="..." type="System.Web.Security.SqlRoleProvider, System.Web, Version=4.0.0.0, Culture=neutral...."/>
        </providers>
    </roleManager>
    <membership>
        <providers>
            <clear/>
            <add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider.... connectionStringName=""..../>
        </providers>
    </membership>
    <profile>
        <providers>
            <clear/>
            <add name="AspNetSqlProfileProvider" connectionStringName="" type="System.Web.Profile.SqlProfileProvider..../>
        </providers>
    </profile>

This seems to work perfectly. It clears then re-adds the providers with the correct connection string.

This is dependent on you having run the correct SQL Scripts against the database. You can get them here: http://support.microsoft.com/kb/2006191