Unable to login to SQL Server using connection string-CodePudding

I am trying to connect my .NET Core application to SQL Server using a connection string. However it is failing and I get this error:

login failed for user

This is the connection string I am using

"ConnectionStrings": {
    "DefaultSQLConnection": "Server=DESKTOP-User\\MSSQLSERVER;Database=VillaDatabase;TrustServerCertificate=True;"
}

Below is the line of code I am using in my Program.cs file:

builder.Services.AddDbContext<ApplicationDbContext>(option =>
{       
     option.UseSqlServer(builder.Configuration.GetConnectionString("DefaultSQLConnection"));
});

I am currently using .NET Core 7.

Can anyone please tell me what could be the fix for this?

CodePudding user response：

We do in our application as:

Add connection string in appsetting.json

"connectionString": "data source=dbserver;initial catalog=dbname;user id=dbuserid;password=dbpassword;MultipleActiveResultSets=True;Encrypt=True;Connection Timeout=60;",

Read in c# code as

    var configuration = new ConfigurationBuilder()
                        //.SetBasePath(Directory.GetParent(Directory.GetCurrentDirectory())   "/ThriivDental.DAL").AddJsonFile("config.json", false)
                        .SetBasePath(Directory.GetCurrentDirectory()   "/").AddJsonFile("appsettings.json", false)
                        .AddJsonFile($"appsettings.{SessionModel.Environmentname}.json", optional: true, reloadOnChange: true)
                        .Build();
    string StaticConnectionString = configuration.GetSection("connectionString").Value;

Make sure the proper path of appsetting.json you get in above code. For your info, its added at project level (you can find near about startup.cs file)

CodePudding user response：

Can anyone please tell me what could be the fix for this.

In your Server Name which is DESKTOP-User\\MSSQLSERVER you should use your

Note: First, try to login in SSMS using user name and password then use the same in your connection string.

Please refer to following code snippet.

Program.cs:

var connectionString = builder.Configuration.GetConnectionString("DefaultSQLConnection");
builder.Services.AddDbContext<ApplicationDbContext>(x => x.UseSqlServer(connectionString));

Appsetting.js When Login As User:

"ConnectionStrings": {
    "DefaultSQLConnection": "Server=YourServerName;Database=YourDatabaseName; User Id=User;password=Password; MultipleActiveResultSets=true"
  },

Appsetting.js When Use Windows Authentication:

"ConnectionStrings": {
        "DefaultSQLConnection": "Server=YourServerName;Database=YourDatabaseName; Trusted_Connection=True;MultipleActiveResultSets=true"
      },

Note: If you want to access database as an perticular user in that scenario include these two parameter User Id=User;password=Password;. In addition, if you would use perticular user in that case yon can remove Trusted_Connection=True but for windown authentication you could it to true.

If you need further details you could refer to our official document