IIS / SQL Server connection error after adding windows auth-CodePudding

I have a webapplication running using Anonymous Authentication and a connectionstring to a SQL server with a dedicated SQL-account. Connectionstring:

<add name="ConnectionString" connectionString="Data Source=xxxxx;Initial Catalog=xxxxx;User ID=xxxxxx;Password=xxxxxx" providerName="System.Data.SqlClient" />

Everything works fine but after adding access control to the application:

<system.webServer>
    <security>
        <authorization>
            <remove users="*" roles="" verbs="" />
            <add accessType="Allow" roles="DOMAIN\ADGroup" />
        </authorization>
    </security>
</system.webServer>

And change to Windows Authentication in the IIS the pages using a connection to the SQL-server stops working. I havn't changed the connectionstring. Error message:

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) 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) 6318697
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) 245 System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean withFailover) 536
System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject, Boolean withFailover) 283
System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) 6338834
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) 6338792
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) 354
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) 300
System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) 45
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) 6343166
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) 6343479
System.Data.SqlClient.SqlConnection.Open() 258
System.Web.Management.SqlServices.GetSqlConnection(String server, String user, String password, Boolean trusted, String connectionString) 115

[HttpException (0x80004005): Unable to connect to SQL Server database.]
System.Web.Management.SqlServices.GetSqlConnection(String server, String user, String password, Boolean trusted, String connectionString) 4052597
System.Web.Management.SqlServices.SetupApplicationServices(String server, String user, String password, Boolean trusted, String connectionString, String database, String dbFileName, SqlFeatures features, Boolean install) 159
System.Web.DataAccess.SqlConnectionHelper.CreateMdfFile(String fullFileName, String dataDir, String connectionString) 825

Example usage of the connectionstring:

using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
        {
            conn.Open();

Any ideas what could be causing this?

CodePudding user response：

With CreateMdfFile in the stack trace it seems like it's trying to connect to a (LocalDb) instance.

I have a theory. This actually makes sense if you're connecting to a local database file using SQL Express or LocalDb because file access depends on the identity the code is running under. But making a TCP/IP connection to a remote SQL Server, even using the Browser service does not.

And in IIS Anonymous requests run as IUSR by default, and Windows Auth runs as the App Pool Identity, eg DefaultAppPool.

So grant full control of the folder containing the database, and make sure that the database files have ACL inheritence enabled, or seperately grant full control on those to the App Pool Identity.