Connect to SQL Server from IIS (ASP.Net Core)

1

I spend hours trying multiple combinations to connect my asp.net core site hosted on an IIS Server to a remote SQL Server using just the SQL user and password without exit. This is my connection string in appsettings.json:

 "ConnectionStrings": {
    "Booking": "Server=SQLServerName;Database=Booking;User Id=XXX;Password=XXX;"
  }
  • I have tried to set Integrated security = false and Trusted connection = false
  • I changed the property Identity in the App pool to LocalSystem, although _I have tried all the options.
  • I can connect to the database from my computer to SQLManager without any issue.
  • In my events viewer, I see the next error: Microsoft.Data.SqlClient.SqlException (0x80131904): Login failed for user 'DOMAIN\NAMEOFTHESERVER$'.
  • SQL Server 2016 in the database server, windows server 2016 in the webserver.
  • I use windows authentication for user authentication, but I need to use a simple user and password to connect to the db.

Any idea about what I'm doing wrong?

Thank you

asp.net-core
iis
asked on Stack Overflow Sep 25, 2020 by Oscar Mateu • edited Sep 25, 2020 by Oscar Mateu

2 Answers

3

SqlException (0x80131904): Login failed for user 'DOMAIN\NAMEOFTHESERVER$'.

This means that the connection string you show is not being used, because this username belongs to NT AUTHORITY\NETWORK SERVICE. So there's another configuration file being used, for example appsettings.development.json, which uses Integrated Security=True, using the application pool's identity.

Change the environment in your web.config to production, so the appsettings.development.json is not used:

<system.webServer>
    <aspNetCore ...>
      <environmentVariables>
        <environmentVariable name="ASPNETCORE_ENVIRONMENT" value="Production" />
answered on Stack Overflow Sep 25, 2020 by CodeCaster
2

May be you miss set permissions in IIS if you have set permissions for sql db for a specific user. Hope to help you.

enter image description here

answered on Stack Overflow Sep 25, 2020 by cuong nguyen manh

User contributions licensed under CC BY-SA 3.0