none
SQL Express database file auto-creation error but web-config changed to Azure SQL-server

    Question

  • I have an app that is based on the new MVC4 template with SImple Membership provider.

    I've deployed the app to an Azure website and changed the connection string from a local express-database to an Azure SQL database.

    I use my Google Account to log on and It works perfect for a while. If I leave the browser on a page and come back later the Membership Provider somhow want's to create an express database even though in my web.config I'm pointing to the Azure SQL database which works fine if I log out and logon again.

    Is there a setting or something or is it just not ment to be used with other databases than local sql express?

    Thanks in advance!

    /Tomas


    Tomas Ekenman www.luxuryspa.se

    Friday, October 12, 2012 2:44 PM

Answers

  • I finally found a solution: http://stackoverflow.com/questions/13030074/why-initializesimplemembershipattribute-in-mvc-4-app 

    Put the MembershipproviderFilter on each controller or move it to appstart.


    Tomas Ekenman www.luxuryspa.se

    • Marked as answer by ekenman Tuesday, March 19, 2013 5:21 AM
    Friday, March 15, 2013 5:34 AM

All replies

  • Hi ekenman,

    Thank you for your question. 
    I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated. 
    Thank you for your understanding and support.

    Maggie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

    Tuesday, October 16, 2012 2:56 AM
    Moderator
  • Thank you Maggie! I appreciate your effort.

    Tomas Ekenman www.luxuryspa.se

    Tuesday, October 16, 2012 4:58 AM
  • Hey Tomas,

    Can you let us know exactly what message you are seeing?  I may need to get someone from our Azure team to help us with this, but I'd like to have a little more to go on.  If you are browsing, I would expect you simply to be redirected to a login page if maybe your authorization cookie had expired - I can't really imagine a scenerio where a user who was no longer logged in could possibly be asked to create a new database.  This sounds more like something that might happen from Visual Studio itself.  Can you give us all the detail you can on your issue?


    Michael Graham

    Tuesday, October 16, 2012 2:22 PM
  • I think I found the problem when I was preparing the answer to you.

    The last thing in webconfig is this:

    <entityFramework>
        <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
          <parameters>
            <parameter value="v11.0" />
          </parameters>
        </defaultConnectionFactory>
      </entityFramework>

    This will with most certanty solve the problem. I can't test it until this week end because I'm out travelling and was in a hurry when I checked in my last changes so my project isn't buildning in TFS at the moment.


    Tomas Ekenman www.luxuryspa.se

    Wednesday, October 17, 2012 4:51 AM
  • No, I removed that part and still get the same error. It also appears when I run locally in my Visual Studio connected to the same Azure DB. Here is the Error Message and web.config:

    Server Error in '/' Application.

    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

    SQLExpress database file auto-creation error: 

    The connection string specifies a local Sql Server Express instance using a database location within the application's App_Data directory. The provider attempted to automatically create the application services database because the provider determined that the database does not exist. The following configuration requirements are necessary to successfully check for existence of the application services database and automatically create the application services database:

    1. If the application is running on either Windows 7 or Windows Server 2008R2, special configuration steps are necessary to enable automatic creation of the provider database. Additional information is available at: http://go.microsoft.com/fwlink/?LinkId=160102. If the application's App_Data directory does not already exist, the web server account must have read and write access to the application's directory. This is necessary because the web server account will automatically create the App_Data directory if it does not already exist.
    2. If the application's App_Data directory already exists, the web server account only requires read and write access to the application's App_Data directory. This is necessary because the web server account will attempt to verify that the Sql Server Express database already exists within the application's App_Data directory. Revoking read access on the App_Data directory from the web server account will prevent the provider from correctly determining if the Sql Server Express database already exists. This will cause an error when the provider attempts to create a duplicate of an already existing database. Write access is required because the web server account's credentials are used when creating the new database.
    3. Sql Server Express must be installed on the machine.
    4. The process identity for the web server account must have a local user profile. See the readme document for details on how to create a local user profile for both machine and domain accounts.


    Source Error: 
    An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

    Stack Trace: 
    [SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)]
       System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +5295167
       System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +242
       System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover) +5307115
       System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover) +145
       System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout) +920
       System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance) +307
       System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions) +434
       System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) +5309659
       System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions) +38
       System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) +5311874
       System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) +143
       System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry) +83
       System.Data.SqlClient.SqlConnection.Open() +96
       System.Web.Management.SqlServices.GetSqlConnection(String server, String user, String password, Boolean trusted, String connectionString) +76
    
    [HttpException (0x80004005): Unable to connect to SQL Server database.]
       System.Web.Management.SqlServices.GetSqlConnection(String server, String user, String password, Boolean trusted, String connectionString) +131
       System.Web.Management.SqlServices.SetupApplicationServices(String server, String user, String password, Boolean trusted, String connectionString, String database, String dbFileName, SqlFeatures features, Boolean install) +89
       System.Web.Management.SqlServices.Install(String database, String dbFileName, String connectionString) +27
       System.Web.DataAccess.SqlConnectionHelper.CreateMdfFile(String fullFileName, String dataDir, String connectionString) +386
    


    Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.17929

    and my web.config:

    <?xml version="1.0" encoding="utf-8"?>
    <configuration>
      <configSections>
        <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler,Log4net" />
      </configSections>
      <connectionStrings>
        <add name="Luxury_SpaEntities" connectionString="metadata=res://*/LuxurySpa.csdl|res://*/LuxurySpa.ssdl|res://*/LuxurySpa.msl;provider=System.Data.SqlClient;provider connection string='data source= myserver.database.windows.net;initial catalog=&quot;mydatabase&quot;;persist security info=True;user id=myuser@myserver.database.windows.net;password=mypassword;MultipleActiveResultSets=True;App=EntityFramework'" providerName="System.Data.EntityClient" />
        <add name="DefaultConnection" connectionString="Data Source=myserver.database.windows.net;initial catalog=&quot;mydatabase&quot;;persist security info=True;user id=myuser@myserver.database.windows.net;password=mypassword;MultipleActiveResultSets=True;App=EntityFramework" providerName="System.Data.SqlClient" />
      </connectionStrings>
      <appSettings>
        <add key="log4net.Internal.Debug" value="true" />
        <add key="webpages:Version" value="2.0.0.0" />
        <add key="webpages:Enabled" value="false" />
        <add key="PreserveLoginUrl" value="true" />
        <add key="ClientValidationEnabled" value="true" />
        <add key="UnobtrusiveJavaScriptEnabled" value="true" />
      </appSettings>
      <system.web>
        <customErrors mode="Off" />
        <compilation debug="true" targetFramework="4.0" />
        <authentication mode="Forms">
          <forms loginUrl="~/Account/Login" timeout="2880" />
        </authentication>
        <pages>
          <namespaces>
            <add namespace="System.Web.Helpers" />
            <add namespace="System.Web.Mvc" />
            <add namespace="System.Web.Mvc.Ajax" />
            <add namespace="System.Web.Mvc.Html" />
            <add namespace="System.Web.Optimization" />
            <add namespace="System.Web.Routing" />
            <add namespace="System.Web.WebPages" />
            <add namespace="LuxurySpa.BookingClient.Helpers" />
          </namespaces>
        </pages>
      </system.web>
      <system.webServer>
        <validation validateIntegratedModeConfiguration="false" />
        <modules runAllManagedModulesForAllRequests="true" />
        <handlers>
          <remove name="ExtensionlessUrlHandler-ISAPI-4.0_32bit" />
          <remove name="ExtensionlessUrlHandler-ISAPI-4.0_64bit" />
          <remove name="ExtensionlessUrlHandler-Integrated-4.0" />
          <add name="ExtensionlessUrlHandler-ISAPI-4.0_32bit" path="*." verb="GET,HEAD,POST,DEBUG,PUT,DELETE,PATCH,OPTIONS" modules="IsapiModule" scriptProcessor="%windir%\Microsoft.NET\Framework\v4.0.30319\aspnet_isapi.dll" preCondition="classicMode,runtimeVersionv4.0,bitness32" responseBufferLimit="0" />
          <add name="ExtensionlessUrlHandler-ISAPI-4.0_64bit" path="*." verb="GET,HEAD,POST,DEBUG,PUT,DELETE,PATCH,OPTIONS" modules="IsapiModule" scriptProcessor="%windir%\Microsoft.NET\Framework64\v4.0.30319\aspnet_isapi.dll" preCondition="classicMode,runtimeVersionv4.0,bitness64" responseBufferLimit="0" />
          <add name="ExtensionlessUrlHandler-Integrated-4.0" path="*." verb="GET,HEAD,POST,DEBUG,PUT,DELETE,PATCH,OPTIONS" type="System.Web.Handlers.TransferRequestHandler" preCondition="integratedMode,runtimeVersionv4.0" />
        </handlers>
      </system.webServer>
      <runtime>
        <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1"> 
          <dependentAssembly>
            <assemblyIdentity name="System.Web.Helpers" publicKeyToken="31bf3856ad364e35" />
            <bindingRedirect oldVersion="1.0.0.0-2.0.0.0" newVersion="2.0.0.0" />
          </dependentAssembly>
          <dependentAssembly>
            <assemblyIdentity name="System.Web.Mvc" publicKeyToken="31bf3856ad364e35" />
            <bindingRedirect oldVersion="0.0.0.0-4.0.0.0" newVersion="4.0.0.0" />
          </dependentAssembly>
          <dependentAssembly>
            <assemblyIdentity name="System.Web.WebPages" publicKeyToken="31bf3856ad364e35" />
            <bindingRedirect oldVersion="1.0.0.0-2.0.0.0" newVersion="2.0.0.0" />
          </dependentAssembly>
          <dependentAssembly>
            <assemblyIdentity name="Newtonsoft.Json" publicKeyToken="30ad4fe6b2a6aeed" culture="neutral" />
            <bindingRedirect oldVersion="0.0.0.0-4.5.0.0" newVersion="4.5.0.0" />
          </dependentAssembly>
        </assemblyBinding>
      </runtime>
      <log4net>
        Just a bunch of log4net configuration
      </log4net>
    </configuration>
    


    Tomas Ekenman www.luxuryspa.se

    Friday, October 19, 2012 4:55 AM
  • Can you try the troubleshooting steps in this article:  http://blogs.msdn.com/b/sqlazure/archive/2010/03/22/9982979.aspx

    Let me know if this helps.


    Michael Graham

    Monday, October 29, 2012 5:12 PM
  • The problem is also on Azure when I have deployed, so it is not possible to follow the article.

    I have no problems connecting to the same SQL Azure database through other applications that does not use the simple membership provider. 


    Tomas Ekenman www.luxuryspa.se

    Tuesday, October 30, 2012 4:33 AM
  • I don't see the MembershipProvider configured in your web.config.  Is there another more global web.config that configures that?


    Michael Graham, MSFT

    Wednesday, October 31, 2012 4:04 PM
  • No. It is from the Standard template and it does not contain a MembershipProvider configuration in the web.config when you create  a project. 

    Perhaps you could help me changing the web.config in the standard template so it works with Azure instead of an express. My solution works for a while until I get a time out.

    Best Regards

    Tomas


    Tomas Ekenman www.luxuryspa.se

    Wednesday, October 31, 2012 4:53 PM
  • http://social.msdn.microsoft.com/Forums/en-US/windowsazuretroubleshooting/thread/9e517b78-14bd-4e43-a154-99dc514c1306

    There are similar problems here. But I dont have a role Manager.

    Is there a solution for this?


    Tomas Ekenman www.luxuryspa.se

    Sunday, January 27, 2013 10:13 PM
  • I finally found a solution: http://stackoverflow.com/questions/13030074/why-initializesimplemembershipattribute-in-mvc-4-app 

    Put the MembershipproviderFilter on each controller or move it to appstart.


    Tomas Ekenman www.luxuryspa.se

    • Marked as answer by ekenman Tuesday, March 19, 2013 5:21 AM
    Friday, March 15, 2013 5:34 AM