locked
DB Connection Not Working After Published RRS feed

  • Question

  • User-179998394 posted

    I have developed a web app for work and it is working in my development environment. (VS 2019, .NET 4.6.1, Web Forms, some Bootstrap and SQL Server Express)

    Basically the app is a login form that authenticates the user, then an entry form that saves user entered data to a DB.

    As I mentioned, when I run it from VS it works fine using the local sql server instance on my development machine. Now that it's completed, I published it to the IIS server that will be hosting the app.

    The problem is when I try to use the app on the host server after publishing it, I get the login form to come up but when I enter the credentials and attempt to 'login' it fails to connect to the DB instance on the host server preventing it from authenticating. I've recreated the same DB instance on the host server. I'm running the same version of SQL Server Express (2017) and I created the database using the backup and restore method from my development DB. All should be the same.

    The host environment is IIS v8 running on Windows Server 2016 and .NET support for 4.6 and SQL Server Express 2017.

    Here is the error I receive when I attempt to login: (as for the 'user' name, due to privacy issues with my employer I have to keep the name redacted, if it's critical to the solution I can provide it in a private communication)

    Cannot open database "ATMain" requested by the login. The login failed.
    Login failed for user 'XXXX\2-XXXX-WEBAPP$'.

    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.

    Exception Details: System.Data.SqlClient.SqlException: Cannot open database "ATMain" requested by the login. The login failed.
    Login failed for user 'XXXX\2-XXXX-WEBAPP$'.

    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): Cannot open database "ATMain" requested by the login. The login failed.
    Login failed for user 'XXXX\2-XXXX-WEBAPP$'.]
    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, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager) +1524
    System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) +467
    System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) +70
    System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) +940
    System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) +111
    System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) +1567
    System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) +118
    System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) +268
    System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) +315
    System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry) +128
    System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry) +265
    System.Data.SqlClient.SqlConnection.Open() +133
    ActivityTracker.Login.LoginBtn_Click(Object sender, EventArgs e) in C:\Users\elhale\source\repos\ActivityTracker\ActivityTracker\Login.aspx.cs:25
    System.Web.UI.WebControls.Button.OnClick(EventArgs e) +11596844
    System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +274
    System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1964

    It seems clear it's not connecting to the DB, but I don't understand why. While I see the 'user' in the error message, I don't get that information when I run it in my dev environment since it works and doesn't give me an error. As a result I don't know what 'user' it's working with on that machine, but it must be machine specific since the 'user' in the error message clearly is.

    Can anyone help explain why it's not connecting as compared to my dev environment and what I can do to fix it? Any help would be greatly appreciated.

    Thursday, February 6, 2020 6:23 PM

Answers

  • User475983607 posted

    The login found in the connection string is incorrect.

    Cannot open database "ATMain" requested by the login. The login failed.
    Login failed for user 'XXXX\2-XXXX-WEBAPP$'.

    It looks like you are using Windows authentication.  Keep in mind, the application runs under your credentials on your development machine.  On the remote host it runs under the application pool identity.  Perhaps contact your SQL admin for assistance.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 6, 2020 7:53 PM
  • User753101303 posted

    Hi,

    See https://docs.microsoft.com/en-us/iis/manage/configuring-security/application-pool-identities#accessing-the-network

    In short when using the default account under which IIS runs an app, network resources are accessed using the computer account. A short answer is to just grant access to this account fro your database :

    https://blogs.msdn.microsoft.com/ericparvin/2015/04/14/how-to-add-the-applicationpoolidentity-to-a-sql-server-login/

    Not mentionned but you may have also to add this account to the db_datareader and db_datawriter role. Note that it means that ALL web sites installed on this server could possibly access this database.

    Another option is to configured a dedicated domain account and grant access to this account.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 6, 2020 8:42 PM

All replies

  • User475983607 posted

    The login found in the connection string is incorrect.

    Cannot open database "ATMain" requested by the login. The login failed.
    Login failed for user 'XXXX\2-XXXX-WEBAPP$'.

    It looks like you are using Windows authentication.  Keep in mind, the application runs under your credentials on your development machine.  On the remote host it runs under the application pool identity.  Perhaps contact your SQL admin for assistance.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 6, 2020 7:53 PM
  • User753101303 posted

    Hi,

    See https://docs.microsoft.com/en-us/iis/manage/configuring-security/application-pool-identities#accessing-the-network

    In short when using the default account under which IIS runs an app, network resources are accessed using the computer account. A short answer is to just grant access to this account fro your database :

    https://blogs.msdn.microsoft.com/ericparvin/2015/04/14/how-to-add-the-applicationpoolidentity-to-a-sql-server-login/

    Not mentionned but you may have also to add this account to the db_datareader and db_datawriter role. Note that it means that ALL web sites installed on this server could possibly access this database.

    Another option is to configured a dedicated domain account and grant access to this account.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 6, 2020 8:42 PM
  • User-179998394 posted

    mgebhard, yes, I am using windows authentication. I will look at the application pool identity, PatriceSc also posted some info regarding application pool identity that I will look at and work with. Your info is much appreciated, thank you! I will reply as to the outcome once I work on it.

    Friday, February 7, 2020 3:40 PM
  • User-179998394 posted

    PatriceSc, thank you for the links, I am looking through them now. I really appreciate the information, I will let you know how it turns out. Thank you again.

    Friday, February 7, 2020 3:42 PM
  • User-179998394 posted

    Gentlemen,

    I have gone through the documentation you provided and made some changes hoping to fix the authentication problem, but to no avail.

    It does seem clear the problem is user/login/permission problem with sql server. I will follow up with researching sql server admin in an attempt to get the login to work. If you have any other ideas, please let me know.

    eldon

    Monday, February 10, 2020 4:12 PM