sql-serverasp.net-coreiiswindows-authentication

Can't connect to SQL Server with Windows authentication with my ASP.NET Core application (IIS server)


I have a problem when I deploy my ASP.NET Core 3.1 application to IIS.

When I provide Windows credentials in the appsettings.json file, I always get this error:

Login failed with DOMAIN\USERNAME$

Should I update the connection string in my web.config or update my IIS Server in IIS Manager ?

appsettings.json:

{
    "AppSettings": {
        "Secret": "9HGXuFLr5PeSjhBBgvMM8CYCQkCcfFgWgQFQvX9M6ahsHC87ESH3cVajpnfTHEBJ"
    },
    "Logging": {
        "LogLevel": {
            "Default": "Debug",
            "System": "Information",
            "Microsoft": "Information"
        }
    },
    "AllowedHosts": "*",
    "ConnectionStrings": {
        "L4LAuthentification_TEST01DataBase": "Server=SRVSQL-01-TST\\INSTANCE1;Database=L4LAuthentification_TEST01;Integrated Security=True;",
        "L4Authentification_RECET01DataBase": "Server=SRVSQL-01-TST\\INSTANCE1;Database=L4Authentification_RECET01;Integrated Security=True;",
        "DevInfoCDataBase": "Server=SRVSQL-01-TST\\INSTANCE1;Database=DevInfoC;Integrated Security=True;",
        "InfocentreRecet01DataBase": "Server=SRVSQL-01-TST\\INSTANCE1;Database=InfocentreRecet01;Integrated Security=True;",
        "L4LGestionFLux_TEST01DataBase": "Server=SRVSQL-01-TST\\INSTANCE1;Database=L4LGestionFLux_TEST01;Integrated Security=True;",
        "L4GestionFlux_RECET01DataBase": "Server=SRVSQL-01-TST\\INSTANCE1;Database=L4GestionFlux_RECET01;Integrated Security=True;",
        "L4EdiFluxInDataBase": "Server=SRVSQL-01-TST\\INSTANCE1;Database=L4EdiFluxIn;Integrated Security=True;",
    }
}

Solution

  • Anytime you use "Integrated Security=True", you need to know which identity (aka principal) your code is running. And that IIdentity (running your process in IIS) has to have credentials to the SqlServer. –

    Most likely, your IIS runs your Application as "DOMAIN\USERNAME$". And (correspondingly) the account DOMAIN\USERNAME$ does not have credentials to your ms-sql-server.

    More than likely, you will create a more isolated "service account" that you will use to run your IIS Application AND that has permissions to your Ms-Sql-Server.