0

I can't connect to my local SQL Server Express instance via Entity Framework. When I try to run the update-database command, I get this error message.

Login failed for user ''. Reason: An attempt to login using SQL authentication failed.
Server is configured for Integrated authentication only.

Error: 18456, Severity: 14, State: 58.

From what I understand, Visual Studio is attempting to log in to SQL Server via a user account even though I've requested that Windows authentication is used in the connection string.

I can still access the server via SSMS.

What I've tried. None of which helped

  • Different variations of the connection string
  • Opened port 1433
  • Created a test UDL file to test the connection.
  • A clean reinstall of SQL Server Express (not sure I managed to clean up all the files)
  • Checked the SQL Server browser is running
  • Checked server instance is running
  • Enabled TCP/IP & named pipes
  • Tried connecting via tcp which works
  • Added Integrated Security=SSPI to connection string
  • Changed server to accept Windows authentication and SQL Server authentication.
  • Restarting PC

This is a new laptop on windows 11, I don't know if that is causing any issues as I've never had an issue with this process on Windows 10.

I seem to have two instances; .\SQLEXPRESS & (localdb)\\MSSQLLocalDB I'm not sure if this is causing some conflict or if this is the intended behaviour.

Here is the connection string, I pulled this from the server explorer within Visual Studio so I am pretty sure this is correct. On top of that, I have tried numerous variations of this to attempt to fix the issue

"DefaultConnection": "Data Source=LAPTOP-51LB4QTQ\\SQLEXPRESS;Initial Catalog=MicroBlog;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"

This is where I get the connection string in my Program.cs file

builder.Services.AddDbContext<BlogContext>(opt => opt.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));
var app = builder.Build();

At this point I'm completely lost, I've read a lot of articles but have not come across any fixes. I'm not a dba just a programmer so have limited knowledge of this side of SQL Server.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Andy Stevens
  • 23
  • 8
  • I would delete this part from the connection string, and check if the error message is getting clearer: `;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False` – Luuk Oct 30 '22 at 15:11
  • or, pick an appropriate connection string from: https://www.connectionstrings.com/sql-server/ – Luuk Oct 30 '22 at 15:16
  • @Luuk I've tried those variations on connectionstrings.com and without the extra fluff on the end of my current one. I only included here it just in case it was useful. Unfortunately no variation of connection string has helped – Andy Stevens Oct 30 '22 at 15:19
  • The following may be helpful: https://stackoverflow.com/a/71199793/10024425 and https://stackoverflow.com/a/70321177/10024425 – Tu deschizi eu inchid Oct 30 '22 at 16:55
  • Find the instance's ERRORLOG file, find some examples of the 18456 event within it and take note of the SQL Login username that it's trying to login with. Then search your entire codebase for that username. Entity Framework's migration code has a tendency to hard-code connection string properties in the C# itself instead of using the default connection string from the .json/.config files. – AlwaysLearning Oct 30 '22 at 21:29
  • @AlwaysLearning unfortunately I can't see anything else in the errors or located nearby other than the *Login failed for user ''.* message above. To me, it looks like it's trying to log in with a blank string. This seems to make sense a little, as I haven't entered a username/password but I haven't asked to use a username/password just windows authentication. – Andy Stevens Oct 30 '22 at 22:09
  • Your connection string is referencing SQLEXPRESS, when you mention "Changed server to accept Windows authentication and SQL Server authentication." what server/database are you referring to? You want to ensure your application connection string is using the same database server name that you are using with SSMS etc. – Steve Py Oct 30 '22 at 22:38
  • @StevePy I'm using the same instance for my connection string and my SSMS "LAPTOP-51LB4QTQ\SQLEXPRESS" – Andy Stevens Oct 31 '22 at 08:57

1 Answers1

0

You may try like below:

In Web.config file:

<connectionStrings>
    <add name="DefaultConnection" connectionString="Data Source=SUBRATATALUKDER;Initial Catalog=MyDB;Integrated Security=True;" providerName="System.Data.SqlClient"/>
</connectionStrings>

In appsettings.json file:

"ConnectionStrings": {
    "DefaultConnection": "Server=SUBRATATALUKDER;Database=MyDB;Trusted_Connection=True;TrustServerCertificate=True;",
}

Note: Server Name = SUBRATATALUKDER

Database Name = MyDB

100% tested.

Sql Server connection string

Subarata Talukder
  • 5,407
  • 2
  • 34
  • 50