locked
SQL Server Access fails on IIS but works on IIS Express RRS feed

  • Question

  • User262353770 posted

    Hello,

    I'm having a strange situation in my development workstation (Windows 10 Pro v1607). I've been working for a number of months on a big WebForms 4.6 appplication with EF 6.1.3. Before deploying on the production server (Windows 2012 Server) I always test the application on my local IIS, after having finished testing on IIS Express. The dbms is a SQL Server 2012 Express on a separate machine in my LAN.  Everything works fine on IIS Express. On IIS I get an "access denied" exception when I try to log-in.

    The web application uses Owin and AspNet Identity based on the same SQL Server database. The following are the NuGet packages referenced by the application

    Antlr {3.5.0.2}
    AspNet.ScriptManager.bootstrap {3.3.6}
    AspNet.ScriptManager.jQuery {1.10.2}
    bootstrap {3.3.6}
    EntityFramework {6.1.3}
    jQuery {1.10.2}
    Microsoft.AspNet.FriendlyUrls {1.0.2}
    Microsoft.AspNet.FriendlyUrls.Core {1.0.2}
    Microsoft.AspNet.Identity.Core {2.2.1}
    Microsoft.AspNet.Identity.EntityFramework {2.2.1}
    Microsoft.AspNet.Identity.Owin {2.2.1}
    Microsoft.AspNet.Providers.Core {2.0.0}
    Microsoft.AspNet.ScriptManager.MSAjax {5.0.0}
    Microsoft.AspNet.ScriptManager.WebForms {5.0.0}
    Microsoft.AspNet.Web.Optimization {1.1.3}
    Microsoft.AspNet.Web.Optimization.WebForms {1.1.3}
    Microsoft.CodeDom.Providers.DotNetCompilerPlatform {1.0.1}
    Microsoft.Net.Compilers {1.3.2}
    Microsoft.Owin {3.0.1}
    Microsoft.Owin.Host.SystemWeb {3.0.1}
    Microsoft.Owin.Security {3.0.1}
    Microsoft.Owin.Security.Cookies {3.0.1}
    Microsoft.Owin.Security.Facebook {3.0.1}
    Microsoft.Owin.Security.Google {3.0.1}
    Microsoft.Owin.Security.MicrosoftAccount {3.0.1}
    Microsoft.Owin.Security.OAuth {3.0.1}
    Microsoft.Owin.Security.Twitter {3.0.1}
    Microsoft.Web.Infrastructure {1.0.0.0}
    Modernizr {2.8.3}
    Newtonsoft.Json {9.0.1}
    Owin {1.0}
    Respond {1.4.2}
    WebGrease {1.6.0}

    These are my connection strings. I had to use two, one for ASP.NET Identity and the other for EF.

    <add name="identityConString" connectionString="Data Source=dbserverip\dbinstance;Initial Catalog=dbname;Persist Security Info=True;MultipleActiveResultSets=True;User ID=dbuser;Password=dbpass" providerName="System.Data.SqlClient" />
    <add name="efConString" connectionString="metadata=res://*/IpiDataModel.csdl|res://*/IpiDataModel.ssdl|res://*/IpiDataModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=dbserverip\dbinstance;initial catalog=dbname;persist security info=True;user id=dbuser;password=dbpass;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

    On Login the server executes

    var manager = Context.GetOwinContext().GetUserManager<ApplicationUserManager>();
    var user = manager.FindByName(Email.Text);

    The exception is thrown on the 2d line.

    The strange things are

    1. If I change the connection strings to use my local SQL 2012 Express server on my pc with the same credentials everything works well.
    2. Everything still works well on the production server (thankfully)
    3. Everything works well if I use IIS Express on my local PC
    4. When I use my local IIS I get a "denied access" exception.
    5. Everything used to work fine on my local IIS up to a few weeks ago...

    I have tried changing the application pool on my IIS for the application. I have tried having it run under ApplicationPoolIdentity, under Network service, etc. The results have always been the same.

    A possible explanation could be a Windows 10 glitch introduced by an update.

    Any ideas on how to fix this?

    Thank you.

    This is the exception (the language is Italian, sorry)

    [Win32Exception (0x80004005): Accesso negato]
    
    [SqlException (0x80131904): Si è verificato un errore di rete o specifico dell'istanza mentre si cercava di stabilire una connessione con SQL Server. Il server non è stato trovato o non è accessibile. Verificare che il nome dell'istanza sia corretto e che SQL Server sia configurato in modo da consentire connessioni remote. (provider: Named Pipes Provider, error: 40 - Impossibile aprire una connessione a SQL Server)]
       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) +1418
       System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) +470
       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) +945
       System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) +114
       System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) +1637
       System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) +117
       System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) +267
       System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) +318
       System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry) +132
       System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry) +246
       System.Data.SqlClient.SqlConnection.Open() +122
       System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch(TTarget target, Action`2 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed) +104
       System.Data.Entity.Infrastructure.Interception.DbConnectionDispatcher.Open(DbConnection connection, DbInterceptionContext interceptionContext) +509
       System.Data.Entity.SqlServer.<>c__DisplayClass33.<UsingConnection>b__32() +567
       System.Data.Entity.SqlServer.<>c__DisplayClass1.<Execute>b__0() +15
       System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute(Func`1 operation) +238
       System.Data.Entity.SqlServer.SqlProviderServices.UsingMasterConnection(DbConnection sqlConnection, Action`1 act) +916
       System.Data.Entity.SqlServer.SqlProviderServices.CreateDatabaseFromScript(Nullable`1 commandTimeout, DbConnection sqlConnection, String createDatabaseScript) +117
       System.Data.Entity.SqlServer.SqlProviderServices.DbCreateDatabase(DbConnection connection, Nullable`1 commandTimeout, StoreItemCollection storeItemCollection) +212
       System.Data.Entity.Migrations.Utilities.DatabaseCreator.Create(DbConnection connection) +135
       System.Data.Entity.Migrations.DbMigrator.EnsureDatabaseExists(Action mustSucceedToKeepDatabase) +175
       System.Data.Entity.Migrations.DbMigrator.Update(String targetMigration) +116
       System.Data.Entity.Internal.DatabaseCreator.CreateDatabase(InternalContext internalContext, Func`3 createMigrator, ObjectContext objectContext) +121
       System.Data.Entity.Database.Create(DatabaseExistenceState existenceState) +293
       System.Data.Entity.CreateDatabaseIfNotExists`1.InitializeDatabase(TContext context) +187
       System.Data.Entity.Internal.InternalContext.PerformInitializationAction(Action action) +72
       System.Data.Entity.Internal.InternalContext.PerformDatabaseInitialization() +483
       System.Data.Entity.Internal.RetryAction`1.PerformAction(TInput input) +177
       System.Data.Entity.Internal.LazyInternalContext.InitializeDatabaseAction(Action`1 action) +274
       System.Data.Entity.Internal.InternalContext.GetEntitySetAndBaseTypeForType(Type entityType) +38
       System.Data.Entity.Internal.Linq.InternalSet`1.Initialize() +77
       System.Data.Entity.Internal.Linq.InternalSet`1.get_InternalContext() +21
       System.Data.Entity.Infrastructure.DbQuery`1.System.Linq.IQueryable.get_Provider() +59
       System.Data.Entity.QueryableExtensions.FirstOrDefaultAsync(IQueryable`1 source, Expression`1 predicate, CancellationToken cancellationToken) +208
       System.Data.Entity.QueryableExtensions.FirstOrDefaultAsync(IQueryable`1 source, Expression`1 predicate) +172
       Microsoft.AspNet.Identity.EntityFramework.<GetUserAggregateAsync>d__6c.MoveNext() +503
       System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +14139120
       System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +62
       Microsoft.AspNet.Identity.AsyncHelper.RunSync(Func`1 func) +348
       IPI.ComProjectMan.Web.Account.Login.LogIn(Object sender, EventArgs e) in F:\Progetti\dNET\Teksmar\IPI\solution\IPI.ComProjectMan.Web\Login.aspx.cs:98
       System.Web.UI.WebControls.Button.OnClick(EventArgs e) +11802193
       System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +150
       System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1735
    


    Informazioni di versione: Versione di Microsoft .NET Framework:4.0.30319; Versione di ASP.NET:4.6.1586.0

    Friday, September 2, 2016 8:31 AM

Answers

  • User753101303 posted

    Hi,

    My understanding is that IIS+local SQL Server Express works while IIS+remote SQL Server Express doesn't work? The remote SQL Server Express has been enabled for non local access? Could it be a firewall issue? (the IIS site uses your PC IP while IIS Express uses localhost). For now it seems (I translated the error message using Google Translate) that SQL Server is not reached at all rather than just an authentication issue.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 2, 2016 9:18 AM

All replies

  • User753101303 posted

    Hi,

    My understanding is that IIS+local SQL Server Express works while IIS+remote SQL Server Express doesn't work? The remote SQL Server Express has been enabled for non local access? Could it be a firewall issue? (the IIS site uses your PC IP while IIS Express uses localhost). For now it seems (I translated the error message using Google Translate) that SQL Server is not reached at all rather than just an authentication issue.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 2, 2016 9:18 AM
  • User262353770 posted

    It turned out that it was a firewall issue of the DB server... The problem was solved after adding an inbound rule on the server's windows firewall to allow connections to the program "%ProgramFiles%\Microsoft SQL Server\MSSQL11.SQL2012EXP\MSSQL\Binn\sqlservr.exe".

    I don't really understand why IIS Express was allowed access though. Nevermind.

    Thank you.

    Friday, September 2, 2016 9:42 AM