locked
Deploy Project Not Connecting to ConnectionString DB RRS feed

  • Question

  • User-1900345072 posted

    My application contains two db contexts, ApplicationDbContext (for Identity stuff) and DbContext (for all else).  My conn string specifies a SQL Server database and the DbContext functions correctly, but when I try to log in (ApplicationDbContext) the site throws a SQL connection error and indicates it cannot find SQL "Express" which is what I use locally.

    The connection string does not specify a SQL Express instance (see below).  Any ideas as to where else a reference to my local db/SQL Express might live?

    <connectionStrings>
    	<add name="myConnection" connectionString="Data Source=myServer;Initial Catalog=myDB;User ID=me;Password=myPassword" providerName="System.Data.SqlClient" />
    </connectionStrings>

    Error message:

    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:
    
    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.
    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.
    Sql Server Express must be installed on the machine.
    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) +92
       System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +285
       System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover, SqlAuthenticationMethod authType) +372
       System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover) +172
       System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout) +849
       System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance) +320
       System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken) +591
       System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) +5699747
       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 oldConnection, DbConnectionInternal& connection) +507
       System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) +154
       System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) +21
       System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry) +90
       System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry) +166
       System.Data.SqlClient.SqlConnection.Open() +96
       System.Web.Management.SqlServices.GetSqlConnection(String server, String user, String password, Boolean trusted, String connectionString) +75
    
    [HttpException (0x80004005): Unable to connect to SQL Server database.]
       System.Web.Management.SqlServices.GetSqlConnection(String server, String user, String password, Boolean trusted, String connectionString) +125
       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) +29
       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.6.81.0

    Monday, September 21, 2015 8:32 PM

Answers

  • User-1900345072 posted

    I think it's solved...knock on wood. Cool

    Out of all the time I've spent on this it appears to have come down to the code below for web.config I found in a google search.  Apparently this will shut down any attempt to create or connect to any db not specified in the config.  I don't know if it will hold but so far it's working and I can sleep tonight.

    <appSettings>
        <add key="enableSimpleMembership" value="false"/>
    </appSettings>

    Thank you for your replies to this thread.  I hope I can return the favor by helping  someone else.

    ~Mike

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 25, 2015 9:38 PM

All replies

  • User753101303 posted

    Hi,

    Have you done something so that the ApplicationDbContext uses this connection string? Could it be that it just uses the default connection string?

    Tuesday, September 22, 2015 4:59 AM
  • User-1900345072 posted

    Hi,

    Have you done something so that the ApplicationDbContext uses this connection string? Could it be that it just uses the default connection string?

    I think you're right, I'm afraid it is not calling the ApplicationDbContext string at all but I cannot figure out where the problem is.

    This app was working fine on my desktop as well as in a server pre-production/publish environment until I cloned this repo on my laptop when I changed jobs back in June.  Ever since then it (the cloned version) has not been right.  

    So something changed but the webconfig is the same, the migration configuration.cs files look good, the startup.cs looks good.  I'm at a complete loss.  Where else in the code would it fire off that context?  Publish settings?  Build settings?  It seems I've looked everywhere.

    Thanks!

    Mike

    Tuesday, September 22, 2015 9:22 AM
  • User-1900345072 posted

    PatriceSc

    Hi,

    Have you done something so that the ApplicationDbContext uses this connection string? Could it be that it just uses the default connection string?

    Yes, the Configuration.cs file for ApplicationDbcontext names the "myConnection" connection string, which is the same one DbContext uses.  They have previously shared the same connection string and it has worked.

    Do you think this requires 2 conn strings?

    Thanks,

    Mike

    Tuesday, September 22, 2015 9:41 AM
  • User753101303 posted

    No, my point is rather that as you noticed it seems the error message is unrelated to this particular connection string.So for now it looks like that despite using the "myConnection" there is something somewhere that uses another connection string.

    What if you trying to show yourContext.Database.Connection.ConnectinString (if I remember correctly). Does it show the expected string or another one? Try also to check the statck trace to make 100% from where it happens.

    Do you name the connection string each time or is it done centrally. In the former case maybe you forgot to provide the connectioon Stream somewhere??

    Tuesday, September 22, 2015 9:52 AM
  • User-1900345072 posted

    No, my point is rather that as you noticed it seems the error message is unrelated to this particular connection string.So for now it looks like that despite using the "myConnection" there is something somewhere that uses another connection string.

    What if you trying to show yourContext.Database.Connection.ConnectinString (if I remember correctly). Does it show the expected string or another one? Try also to check the statck trace to make 100% from where it happens.

    Do you name the connection string each time or is it done centrally. In the former case maybe you forgot to provide the connectioon Stream somewhere??

    The connection string should be named only in the web.config as far as I can remember.  How would I show the string as you mentioned...using PM console?

    Also I wonder if a clean or rebuild might help as there are files in the bin and obj folder that may be from the old desktop system I used before.

    I am about to be unavailable for a while so I will post anything I find later...thank you for your advice and input!

    ~Mike

    Tuesday, September 22, 2015 11:15 AM
  • User-1900345072 posted

    Just to update you on the situation as promised...I began looking at this issue again last night and noticed there were some nuget packages that needed updating.  So I did that thinking it may help and now there are several more areas that are broken.

    One area is the ApplicationDbContext is now not recognized at all by intellisense so that could be a tip off as to where the problem lies.  BUT there are other classes that now aren't being picked up by intellisense (that were before) so it could just be my project blew up when I did a mass nuget update.  It won't even build now.

    Anyway, I'm not sure where this stands but I will keep posting as I'm able to work on this.  Thanks again,

    Mike

    Wednesday, September 23, 2015 9:19 AM
  • User-1900345072 posted

    My solution is now functioning properly again locally as far as the nuget updates and references.

    Back to the original problem, I noticed that data is pulling from ApplicationDbContext because I have user data (names, addresses, etc) being listed in a view result.  The problem seems to be login sequence.  

    If I enter invalid login, ie: a bad password, it will flag me as incorrect login data.  It's only when it gets into the authentication details of a legitimate user that the error throws.  So somewhere in the validation as it is returning the model it seems to be looking for a local SQLExpress instance instead of the db specified in the conn string.  Any ideas on how to dig into where it's getting it's connection info from in the Identity 2.0 pipeline?

    Mike

    Thursday, September 24, 2015 10:47 AM
  • User753101303 posted

    So it would look at the right db to validate credentials and then something else would look at another db to check for user details?

    The stack trace starts at System.Web.DataAccess.SqlConnectionHelper.CreateMdfFile? Could it be some kind of provider (such as a profile provider, a session provider or whatever)  that is activated in your web.config file? Or maybe at some point you altered your machine.config file?

    Thursday, September 24, 2015 1:10 PM
  • User-1900345072 posted

    So it would look at the right db to validate credentials and then something else would look at another db to check for user details?

    The stack trace starts at System.Web.DataAccess.SqlConnectionHelper.CreateMdfFile? Could it be some kind of provider (such as a profile provider, a session provider or whatever)  that is activated in your web.config file? Or maybe at some point you altered your machine.config file?

    Yes, it appears to be looking for a different db for identity login.

    As of tonight, I've found out why it works locally.  When you build and run, then try to login it works.  But it's tricky, in checking back in my project the system creates the aspnetuser.mdf db in the app_data folder.  So somewhere in the code EF (I guess?) is causing the app to build this db or check for this db at login time, causing the error.  It works locally because my system will allow it to create this db.  I have gone over and over and over this code and I cannot find where this could be.

    where would I find the machine.config file?

    Friday, September 25, 2015 8:52 PM
  • User-1900345072 posted

    You were right on the money...check out the stack trace below in bold.  So the conn string in web.config is correct, now to track down this part.

    [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

    Friday, September 25, 2015 9:19 PM
  • User-1900345072 posted

    I think it's solved...knock on wood. Cool

    Out of all the time I've spent on this it appears to have come down to the code below for web.config I found in a google search.  Apparently this will shut down any attempt to create or connect to any db not specified in the config.  I don't know if it will hold but so far it's working and I can sleep tonight.

    <appSettings>
        <add key="enableSimpleMembership" value="false"/>
    </appSettings>

    Thank you for your replies to this thread.  I hope I can return the favor by helping  someone else.

    ~Mike

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 25, 2015 9:38 PM