SQL Network Interfaces, error: 50 - Local Database Runtime error occurred. Cannot create an automatic instance

Question

I am trying to build an ASP.NET MVC 5 Web Application which has a MyDatabase.mdf file in the App_Data folder. I have SQL Server 2014 Express installed with a LocalDb instance. I can edit the database tables using the Server Explorer, however when I debug the application and go to a page where the database is needed 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.

So I looked in the Event Viewer under Application and only see one Warning over and over again.

The directory specified for caching compressed content C:\Users\User1\AppData\Local\Temp\iisexpress\IIS Temporary Compressed Files\Clr4IntegratedAppPool is invalid. Static compression is being disabled.

So I tried rebooting the server, still no go. Same error 50 as before.

I have created an class under Models where I have a class called Post.

namespace MyApplication.Models
{
    public class Post
    {
        public int Id { get; set; }
        public string Title { get; set; }
        public string Content { get; set; }
    }

    public class MyDatabase : DbContext
    {
        public DbSet<Post> Posts { get; set; }
    }
}

I also have a Controller setup to list the posts from MyDatabase.

namespace MyApplication.Controllers
{
    public class PostsController : Controller
    {
        private MyDatabase db = new MyDatabase();

        // GET: Posts
        public ActionResult Index()
        {
            return View(db.Posts.ToList());
        }
    }

In my web.config file the connection string looks like this...

<connectionStrings>
    <add name="DefaultConnection" 
         connectionString="Data Source=(LocalDB)\v12.0;AttachDbFilename=|DataDirectory|\MyDatabase.mdf;Integrated Security=True" 
         providerName="System.Data.SqlClient" />
</connectionStrings>

I've tried the suggestion posted here but it didn't work. Also tried this.

I also notice that the MyDatabase instance gets disconnected after I start running the application. If I refresh the database using Server Explorer in Visual Studio I can view the tables.

How is it that I can connect to the database and edit it within Visual Studio 2013 but when I debug the application it cannot connect to the database?

Are you sure that, `SQL Server (SQLEXPRESS)` service is running . — Arindam Nayak, Oct 08 '14 at 05:30
Yes, the SQLEXPRESS instance is running when I check using SQL Server Configuration Manager — Jonathan Kittell, Oct 08 '14 at 05:34
Are you able to connect using Management studio, there is an article - http://dyball.wordpress.com/2014/04/28/sql-2014-localdb-error-cannot-connect-to-locaidbv12-o/, this might help~ — Arindam Nayak, Oct 08 '14 at 05:35

score 130 · Accepted Answer · edited May 23 '17 at 11:47

Breaking Changes to LocalDB: Applies to SQL 2014; take a look over this article and try to use (localdb)\mssqllocaldb as server name to connect to the LocalDB automatic instance, for example:

<connectionStrings>
  <add name="ProductsContext" connectionString="Data Source=(localdb)\mssqllocaldb; 
  ...

The article also mentions the use of 2012 SSMS to connect to the 2014 LocalDB. Which leads me to believe that you might have multiple versions of SQL installed - which leads me to point out this SO answer that suggests changing the default name of your LocalDB "instance" to avoid other version mismatch issues that might arise going forward; mentioned not as source of issue, but to raise awareness of potential clashes that multiple SQL version installed on a single dev machine might lead to ... and something to get in the habit of in order to avoid some.

Another thing worth mentioning - if you've gotten your instance in an unusable state due to tinkering with it to try and fix this problem, then it might be worth starting over - uninstall, reinstall - then try using the mssqllocaldb value instead of v12.0 and see if that corrects your issue.

any other solution please because it doesn't work for me – Ahmad Mar 04 '18 at 16:52 — Ahmad, Mar 04 '18 at 16:52

score 43 · Answer 2 · answered Dec 17 '14 at 10:49

43

Running this:

sqllocaldb create "v12.0"

From cmd prompt solved this for me...

answered Dec 17 '14 at 10:49

Alex

37,502
51
204
332

2

what does this actually do? – Simon_Weaver Mar 11 '16 at 00:59
1

Creates a local SQL database with the name "v12.0". – Falcon Momot Apr 09 '16 at 22:37
4

4 Hours of my life trying to fix this problem and this command solve it in a second. It creates a new LocalDB instance with a specified name and version. If the [version-number] parameter is omitted, it defaults to the SqlLocalDB build version. -s starts the new LocalDB instance after it is created. More information here: https://msdn.microsoft.com/en-us/library/hh247716.aspx – Exel Gamboa Dec 13 '16 at 16:39
FYI, after I ran this cmd statement I edited my Web.config file's connectionString Data Source=(LocalDb)\V12.0; from Data Source=(LocalDb)\MSSQLLocalDB; – David Alan Condit Apr 03 '18 at 18:02
This fixed it for me, but after a repair of VS, re-install of my extensions which then forced a restart or my machine, which is all I think it needed after the creation of the instance – Antony Scott Jan 07 '21 at 13:19

score 36 · Answer 3 · answered Sep 04 '15 at 13:52

36

I usually fix this errore following this msdn blog post Using LocalDB with Full IIS

This requires editing applicationHost.config file which is usually located in C:\Windows\System32\inetsrv\config. Following the instructions from KB 2547655 we should enable both flags for Application Pool ASP.NET v4.0, like this:

<add name="ASP.NET v4.0" autoStart="true" managedRuntimeVersion="v4.0"     managedPipelineMode="Integrated">
    <processModel identityType="ApplicationPoolIdentity" loadUserProfile="true" setProfileEnvironment="true" />
</add>

answered Sep 04 '15 at 13:52

Matteo

1,654
17
24

17

Note that if you want to edit this file make sure you use a 64 bit text editor like notepad. If you use a 32 bit one like Notepad++ it will automatically edit a different copy of the file in SysWOW64 instead. Hours of my life I won't get back. – Tyler Mar 25 '16 at 16:10
1

@Tyler I cannot thank you enough for pointing out this. I wish I had read your comment a little earlier. Spent 3 days changing everything. THIS DID THE TRICK ! – Pirate X Jul 19 '16 at 07:26
1

OMG! THanks Tyler. I struggled with the same thing and you saved me from wasting another 2 hrs! – 130nk3r5 Jul 28 '16 at 10:33
1

Reference this https://blogs.msdn.microsoft.com/sqlexpress/2011/12/08/using-localdb-with-full-iis-part-1-user-profile/ – David May 09 '17 at 12:52
Where in the file does this go?? – Ciaran Gallagher Sep 29 '17 at 20:27
Thank you ! I was trying for so many hours , all i needed to do is turn setProfileEnvironment="true" in that file and it worked! – Dimitris Thomas Nov 21 '20 at 21:47

Transformer · Answer 4 · 2020-10-08T16:20:41.420

To begin - there are 4 issues that could be causing the common LocalDb SqlExpress Sql Server connectivity errors SQL Network Interfaces, error: 50 - Local Database Runtime error occurred, before you begin you need to rename the v11 or v12 to (localdb)\mssqllocaldb

Possible Issues

You don't have the services running
You don't have the firelwall ports here configured
Your install has and issue/corrupt (the steps below help give you a nice clean start)
You did not rename the V11 or 12 to mssqllocaldb

 \\ rename the conn string from v12.0 to MSSQLLocalDB -like so-> 
 `<connectionStrings>
      <add name="ProductsContext" connectionString="Data Source= (localdb)\mssqllocaldb; 
      ...`

I found that the simplest is to do the below - I have attached the pics and steps for help.

First verify which instance you have installed, you can do this by checking the registry& by running cmd

 1. `cmd> Sqllocaldb.exe i` 
 2. `cmd> Sqllocaldb.exe s "whicheverVersionYouWantFromListBefore"` 
if this step fails, you can delete with option `d` cmd> Sqllocaldb.exe d "someDb"
 3. `cmd> Sqllocaldb.exe c "createSomeNewDbIfyouWantDb"` 
 4. `cmd> Sqllocaldb.exe start "createSomeNewDbIfyouWantDb"`

ADVANCED Trouble Shooting Registry configurations

Edit 1, from requests & comments: Here are the Registry path for all versions, in a generic format to track down the registry

Paths

// SQL SERVER RECENT VERSIONS
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\(instance-name)

// OLD SQL SERVER
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLServer
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer
// SQL SERVER 6.0 and above.

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\MSDTC
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SQLExecutive
// SQL SERVER 7.0 and above

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SQLServerAgent
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server 7
HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServ65

Searching

SELECT registry_key, value_name, value_data  
FROM sys.dm_server_registry  
WHERE registry_key LIKE N'%SQLAgent%';

or Run this in SSMS Sql Management Studio, it will give a full list of all installs you have on the server

DECLARE     @SQL VARCHAR(MAX)
SET         @SQL = 'DECLARE @returnValue NVARCHAR(100)'
SELECT @SQL = @SQL + CHAR(13) + 'EXEC   master.dbo.xp_regread

 @rootkey      = N''HKEY_LOCAL_MACHINE'',
 @key          = N''SOFTWARE\Microsoft\Microsoft SQL Server\' + RegPath + '\MSSQLServer'',
 @value_name   = N''DefaultData'',
 @value        = @returnValue OUTPUT; 

 UPDATE #tempInstanceNames SET DefaultDataPath = @returnValue WHERE RegPath = ''' + RegPath + '''' + CHAR(13) FROM #tempInstanceNames 

 -- now, with these results, you can search the reg for the values inside reg
 EXEC (@SQL)
 SELECT      InstanceName, RegPath, DefaultDataPath
 FROM        #tempInstanceNames

Trouble Shooting Network configurations

SELECT registry_key, value_name, value_data  
FROM sys.dm_server_registry  
WHERE registry_key LIKE N'%SuperSocketNetLib%';

removing and adding the DB worked for. Using VS Community 2017 and the MVA course on MVC — Jeroen, Mar 23 '17 at 20:07
@AdamPlocher in the _reg picture_ above you can see the `corresponding software SQL server versions` this can be orphaned in some cases. So if you chose to redo the install, its best to check if those keys were removed and reinstall. If thats not what you're looking for help me understand your question better and I will try to help you. I spent a lot of time troubleshooting this — Transformer, Jun 14 '17 at 18:13
@transformer neither the screenshot or your answer show what path to check in the registry. The screenshot shows a Microsoft SQL Server key in the registry, but it doesn't show what the parent keys are. Your answer says "First verify which instance you have installed, you can do this by checking the registry", but doesn't say where in the registry to check. What registry path is it? Thx — Adam Plocher, Jun 17 '17 at 11:52
@AdamPlocher - a quick google search shows the path. -- `HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\YourInstanceName\MSSQLServer\CurrentVersion` Or -- `HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\SOMEOTHER_Instance\MSSQLServer\CurrentVersion` -- For e.g. in SQL 2008, you can enumerate the list of instances at `HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL` — Transformer, Jun 18 '17 at 16:13
the link for the line "You don't have the services running" seems to be broken — geekzster, Apr 02 '19 at 13:40

score 20 · Answer 5 · edited Dec 11 '20 at 10:29

20

An instance might be corrupted or not updated properly.

Try these Commands:

C:\>sqllocaldb stop MSSQLLocalDB
LocalDB instance "MSSQLLocalDB" stopped.
 
C:\>sqllocaldb delete MSSQLLocalDB
LocalDB instance "MSSQLLocalDB" deleted.
 
C:\>sqllocaldb create MSSQLLocalDB
LocalDB instance "MSSQLLocalDB" created with version 13.0.1601.5.
 
C:\>sqllocaldb start MSSQLLocalDB
LocalDB instance "MSSQLLocalDB" started.

edited Dec 11 '20 at 10:29

Wai Ha Lee

8,598
83
57
92

answered Jul 20 '17 at 10:57

Musab

1,067
12
12

This advice seems to replicate that given by @transformer? – Caius Jard Jul 20 '17 at 11:16
this works first uninstall old localdb – Geomorillo Jan 13 '22 at 16:31

score 8 · Answer 6 · answered Jun 09 '16 at 11:21

8

maybe this error came because this version of Sql Server is not installed

connectionString="Data Source=(LocalDB)\v12.0;....

and you don't have to install it

the fastest fix is to change it to any installed version you have

in my case I change it from v12.0 to MSSQLLocalDB

answered Jun 09 '16 at 11:21

Basheer AL-MOMANI

14,473
9
96
92

It Creates issue in my case im using MssqllocalDb but it shows error :/ – Ahmad Mar 04 '18 at 13:59

score 5 · Answer 7 · answered Apr 19 '16 at 09:48

5

Final Solution for this problem is below :

First make changes in applicationHost config file. replace below string setProfileEnvironment="false" TO setProfileEnvironment="true"
In your database connection string add below attribute : Integrated Security = SSPI

answered Apr 19 '16 at 09:48

Dalip Choudhary

546
5
18

Path to ApplicationHost: C:\Windows\System32\inetsrv\config\applicationHost.config. This post made it clear to me: https://social.msdn.microsoft.com/Forums/vstudio/en-US/6986fc37-870b-466c-8306-7712966071b0/50-local-database-runtime-error-occurred-cannot-create-an-automatic-instance-see-the-windows?forum=lightswitch – Mamdouh Sep 22 '20 at 19:06

score 4 · Answer 8 · answered May 21 '16 at 13:04

4

I ran into the same problem. My fix was changing <parameter value="v12.0" /> to <parameter value="mssqllocaldb" /> into the "app.config" file.

answered May 21 '16 at 13:04

Kos

567
4
15

score 3 · Answer 9 · answered Jul 28 '16 at 10:34

All PLEASE note what Tyler said

Note that if you want to edit this file make sure you use a 64 bit text editor like notepad. If you use a 32 bit one like Notepad++ it will automatically edit a different copy of the file in SysWOW64 instead. Hours of my life I won't get back

score 2 · Answer 10 · answered Mar 08 '18 at 16:18

2

In my case, we had several projects in one solution and had selected a different start project than in the package manager console when running the "Update-Database" Command with Code-First Migrations. Make sure to select the proper start project.

answered Mar 08 '18 at 16:18

Michael Staples

537
7
13

Sagar Shinde · Answer 11 · 2019-11-14T08:21:39.533

2

I have solved above problem Applying below steps

And after you made thses changes, do following changes in your web.config

 <add name="DefaultConnection" connectionString="Data Source=(LocalDb)\v12.0;AttachDbFilename=|DataDirectory|\aspnet-Real-Time-Commenting-20170927122714.mdf;Initial Catalog=aspnet-Real-Time-Commenting-20170927122714;Integrated Security=true" providerName="System.Data.SqlClient" />

edited Nov 14 '19 at 08:21

answered Sep 24 '18 at 08:57

Sagar Shinde

150
10

Check your connection string as well – Sagar Shinde Oct 04 '18 at 14:10

score 1 · Answer 12 · edited Sep 19 '22 at 19:01

1

My issue was that i had multiple versions of MS SQL express installed. I went to installation folder C:\Program Files\Microsoft SQL Server where i found 3 versions of it. I deleted 2 folders, and left only MSSQL13.SQLEXPRESS which solved the problem.

edited Sep 19 '22 at 19:01

FoxDeploy

12,569
2
33
48

answered Jun 27 '20 at 10:46

Nenad Birešev

377
2
10

SQL Network Interfaces, error: 50 - Local Database Runtime error occurred. Cannot create an automatic instance

12 Answers12

Linked

Related