0

I have an ASP.NET MVC application and I cannot connect to the local SQL Server database that is hosted on my machine.

I get the following error:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 50 - Local Database Runtime error occurred. Cannot create an automatic instance. See the Windows Application event log for error details. )

I have recently installed SQL Server Express on top of the original SQL Server install; since this has happened, the application can now never get connected to the database. My app is running on the local IIS and under Administrator when I debug through Visual Studio.

Things I have tried:

  • I have tried deleting the local databases in the Microsoft folders and re-creating them
  • I have tried running the "sqllocaldb start" command and it has started
  • I have tried stopping the SQL services and restarting them
  • I have tried running the application pool under a user with access to the DB
  • I have tested the connection into the application by creating a datamodel, which it can do
  • I have tried applying the Everyone group with full control to the instances folder
  • I have tried giving public access to the database table
  • I have tried deleting and re-creating the database

I am getting the following errors in Computer Management under SQLLocalDB15.0:

The specified resource language ID cannot be found in the image file.

Cannot get a local application data path. Most probably a user profile is not loaded. If LocalDB is executed under IIS, make sure that profile loading is enabled for the current user.

My Connection string is as follows:

<add name="Entities" connectionString="metadata=res://*/Models.DataModel.csdl|res://*/Models.DataModel.ssdl|res://*/Models.DataModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=(localdb)\MSSQLLocalDB;initial catalog=DatabaseName;integrated security=True;persist security info=True;multipleactiveresultsets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

I have the following SQL stuffs installed on my machine, I don't know if they are having an effect here:

Programs and Features

GeorgeB
  • 808
  • 1
  • 8
  • 26
  • Are you wanting to connect to your SQL Express instance, or a LocalDB instance? And what edition was your original SQL install? The error indicates you are trying to connect to a LocalDB which is not the same as SQL Express. – squillman Nov 10 '20 at 15:51
  • I am wanting to connect to my LocalDB, I am using the following in my Connection String: Server=(localdb)\MSSQLLocalDB – GeorgeB Nov 10 '20 at 15:55
  • Is the mdf file attached to a server? LocalDb is only meant for cases when the mdf is NOT attached to a server. The Server owns the file and prevents users from directly connecting to the MDF. The error says "not accessible" which CAN mean server owns the file and preventing the conneciton. – jdweng Nov 10 '20 at 15:57
  • @squillman have changed that but still have error – GeorgeB Nov 10 '20 at 15:59
  • @jdweng how can I see if the file is attached? – GeorgeB Nov 10 '20 at 15:59
  • Yeah, sorry... that was wrong. I deleted that comment. – squillman Nov 10 '20 at 16:00
  • Check and see if this question helps: https://stackoverflow.com/questions/10540438/what-is-the-connection-string-for-localdb-for-version-11. – squillman Nov 10 '20 at 16:01
  • Also, can you look in your application event log (through Computer Management) and see if you see any errors there? – squillman Nov 10 '20 at 16:04
  • Any MDF file must be attached to a drive on same machine as the server. It can be c:\ or d:\ (......) as only as drive is on same machine. So you must have a SQL Server on the machine and then connect to the Server using SQL Server Management Studio. The you can use the explorer in SSMS and check the Databases. The Database name and MDF file do not have to be the same so you may need to check the properties of the database to see what MDF file is being used. – jdweng Nov 10 '20 at 16:16
  • @squillman updated my question to include an error from the computer management – GeorgeB Nov 10 '20 at 16:22
  • If mdf file is attached then remove the Attach from the connection string and connect to database through the server. – jdweng Nov 10 '20 at 16:26
  • The MDF is attached but it is not in my connection string – GeorgeB Nov 10 '20 at 16:29
  • Does that event log message correlate to the time that you are trying to connect to the instance? Can you post your entire connection string? – squillman Nov 10 '20 at 16:43
  • @squillman the times do correlate and I have found another error from the computer management that I have added at the same time as the other, the connection string has been added to the question – GeorgeB Nov 10 '20 at 16:50
  • What version of Entity Framework are you using? – Serge Nov 10 '20 at 17:04
  • @Sergey Version 6.4.0 – GeorgeB Nov 10 '20 at 17:17
  • Update on this, I set my IIS to run under local system and the .NET identity stuff is logging in and creating users in A database, but when I look at my local sql and Local DB there are no tables? Where could this database be? SQL Express? – GeorgeB Nov 10 '20 at 17:40
  • Did you try to use MS Sql Server Managemen Studio to connect to your Db using (localdb)\MSSQLLocalDB as server name and windows authentication? – Serge Nov 10 '20 at 17:52
  • @Sergey Yes I connect using management studio – GeorgeB Nov 10 '20 at 18:16
  • Is the server name (localdb)\MSSQLLocalDB and you can see your DatabaseName db? If yes is it attached or created? – Serge Nov 10 '20 at 18:21
  • You need to change the Connection string use following. The Instance name should match the instance in the login window of SSMS : Server=(localdb)\.\MyInstanceShare;Database=myDataBase;Integrated Security=true; Does the SSMS login window show Window Credentials which is same as IntegratedSecurity = true. – jdweng Nov 10 '20 at 18:28
  • SSMS shows the following (localdb)\MSSQLLocalDB (SQL Server 15.0.20000 - MyPcName) but when I go into properties it shows SQL Server Express – GeorgeB Nov 10 '20 at 19:17
  • Added the list of SQL stuff I have installed, maybe the ambiguity is causing a problem – GeorgeB Nov 10 '20 at 19:30

2 Answers2

1

The first thing you have to try is to use MS Sql Server Managemen Studio to connect to your Db using (localdb)\MSSQLLocalDB as server name and windows authentication. If it is empty you can attach db or create new. If it doesn't work you have to try to find your Db using Sql Studio search tools. If you can't find it you have to install your local db again and you will find your db name during the instalation. After this correct your db name accordingly. OR EVEN BETTER try to create your EF from your found db again. EF will automaticaly add the right connection string to your config file. After this you can change the name or move it to another config file.

Serge
  • 40,935
  • 4
  • 18
  • 45
  • What are the SQL search tools? – GeorgeB Nov 10 '20 at 19:33
  • Whern you start the Management Studio in the server name dropdown list select – Serge Nov 10 '20 at 19:36
  • Ok so I can browse and connect to SQL express, but that also is showing no database, there must be a database that the application is using on this machine because the application is saving data and I am able to login using accounts created through them. – GeorgeB Nov 10 '20 at 19:44
  • Each db has an mdf file. If you find this file you can attach it. Or if you have backup file just restor from this file. Or you will have to create the one – Serge Nov 10 '20 at 19:49
  • So I have a database and have a file attached, but it wont work for the issues I have mentioned previously – GeorgeB Nov 10 '20 at 19:59
  • So, what is the name of DB in the management studio and the name of file attached and directory. Is it your default sql directory. – Serge Nov 10 '20 at 20:02
  • The name of the DB and the name of the file attached is the same – GeorgeB Nov 10 '20 at 20:04
  • Just after Integrated Security=SSPI; try to add AttachDBFilename=|DataDirectory|\DbName.mdf – Serge Nov 10 '20 at 20:07
  • But I updated my answer and added some advice to the end. Check it I am sure it will save lots of your time. – Serge Nov 10 '20 at 20:19
  • I tried all of the solutions but still the same error as originally – GeorgeB Nov 10 '20 at 21:22
  • I don't know very much about old EFs since I am using EF core for several years already. But in your case I would create an empty test project and create entity framework files from database using anyEF tools. I remember when the tools creates EF from database , it creates a connection string too and saves it to a config file. – Serge Nov 10 '20 at 21:31
  • Yeah I'm basically gunna re-install 1 version of SQL and hope it works, like I said before, this all worked before I downloaded SQL express 2019 – GeorgeB Nov 10 '20 at 21:35
  • If you can acess it from Management Studio and it runs all your queries it doesn't need any reinstalation. Visual studion also has an option " connect to data base. If you select it you should see all DBs immediately. – Serge Nov 10 '20 at 21:39
  • Just generate EF from DB and use the connection string it generates. And if your EF is in separate project from your Web Application project, you have to move all EF config file stuff from EF project config to your web application config file. – Serge Nov 10 '20 at 21:39
  • Managed to solve it by re-installing SQL and just using SQL server, will post an answer, thanks for the help though! – GeorgeB Nov 10 '20 at 21:57
0

Solved the issue by uninstalling all of the SQL related stuff in my screenshot and re-installing the development SQL Server 2019, then I created the database I had before and connected to it using the data model in EntityFramework, and it worked!

GeorgeB
  • 808
  • 1
  • 8
  • 26