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

Question

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?

CodeCaster · Accepted Answer · 2020-09-25 12:03:16Z

4

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 Sep 25, 2020 at 12:03

CodeCaster

153k24 gold badges237 silver badges287 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nimantha · Accepted Answer · 2023-11-18 15:50:31Z

2

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

edited Nov 18, 2023 at 15:50

Nimantha

6,5276 gold badges32 silver badges78 bronze badges

answered Sep 25, 2020 at 9:56

cuong nguyen manh

3063 silver badges13 bronze badges

2 Comments

Oscar Mateu Over a year ago

I use windows authentication for users, but the connection to the database has to be using this user and password, nothing else.

cuong nguyen manh Over a year ago

It not related even You use windows authentication for SQL server. But I see it is not your problem

Nimantha · Accepted Answer · 2023-11-18 15:50:14Z

If you’re using a connection string with SQL Server Authentication (a User ID and Password present in the connection string) and you’re getting the error referenced in this thread (Login failed for user 'DOMAIN\MACHINENAME$'), be sure to remove the OnConfiguring method containing the original connection string you used to generate your model classes from your database context class (if you used entity framework’s database first approach). In my case, I used integrated security to scaffold (https://www.entityframeworktutorial.net/efcore/create-model-for-existing-database-in-ef-core.aspx#google_vignette) the database model POCOs, but I later then changed the connection string in appsettings.json to use SQL Server Authentication.

It appeared that the appsettings.json connection string entries were being ignored, which they were because my dbContext.cs class still had the OnConfiguring method using the connection string I used to generate the model (the integrated security value).

Basically, no matter how you’re connecting to your database, make sure your database context class does not have an OnConfiguring method using a connection string that will override what is in the appsettings.json or web.config files!

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related