0

I have made an application which uses a LocalDB, to refer to some predefined values as part of some mathematics that it does. I have created an Installer project so that when the application gets installed, a copy of the DB is sent to [ProgramData][ProductName], which is mapped as the pathway for the ConnectionString.

When I install it and run the application on my computer (out of Visual Studio), it runs exactly as I want it too - and there are no errors, warnings or messages in the code. However when I installed it on to someones else's computer (so they can play with it and point out any errors that they come across), this is where the problem comes about.

On their computer, whenever the code refers to the LocalDB they get the below Exception Message:

"A network-related or instance specific error occurred while trying to establish a connection to SQL Server" System.Data.SqlClient.SqlException (0x80131904). The error goes on to say that the LocalDB instance does not exist.

I've double checked the ProgramData folder, and can confirm that the LocalDB did get copied there during the install - and I have confirmed that the code is looking at the correct folder when trying to connect to it - I just can't figure out what is happening.

I have checked that the user has read/write access to the folder, and is a LocalAdmin on their computer.

Is anyone able to help guide me to a solution with this, please?

ConnectionString is below, if it helps:

AppDomain.CurrentDomain.SetData("DataDirectory", Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.CommonApplicationData), "Application_Name"));
ConnectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True";
jarlh
  • 42,561
  • 8
  • 45
  • 63
Dinoduck94
  • 7
  • 1
  • 6
  • Is LocalDB actually installed? Simply copying a database file is not sufficient -- the LocalDB engine needs to actually be there, and that's its own install. Can the user run `sqllocaldb info`? What does `sqllocaldb versions` produce? – Jeroen Mostert Sep 17 '19 at 09:57
  • Hi Jeroen, when I type 'sqllocaldb info' into the cmd prompt it returns 'v11'; and 'sqllocaldb versions' returns 'Microsoft SQL Server 2012'. I believe the LocalDB engine is installed too as part of the pre-requisites when installing. – Dinoduck94 Sep 17 '19 at 10:10
  • But, per the result, there is no `MSSQLLocalDB` instance, as that instance is normally created by Visual Studio (to give you a "default" instance independent of the exact version). Change your connection string to `(LocalDB)\v11;...` or use `sqllocaldb create MSSQLLocalDB` (or do both, with a new, dedicated instance for your application). – Jeroen Mostert Sep 17 '19 at 10:12
  • Thanks for shedding some light on that - I ran the 'sqllocaldb create MSSQLLocalDB' on the command prompt and it ran successfully but unfortunately I got a different error this time. It states: 'The database cannot be opened because it is version 852. This server supports versions 706 and earlier.' Any idea? Thanks for your help – Dinoduck94 Sep 17 '19 at 10:47
  • Per [this](https://sqlserverbuilds.blogspot.com/2014/01/sql-server-internal-database-versions.html), your database file was created with SQL Server 2016 (which is likely the version of LocalDB that came with your VIsual Studio, or one you upgraded later). A SQL Server 2012 LocalDB instance won't be able to open that. Either package SQL Server 2016 LocalDB with your application (if there is an installer for that, I seem to recall they're harder to come by) or (re)-create your database in an older instance. – Jeroen Mostert Sep 17 '19 at 10:49
  • If you have a server with the mdb file attached there is no need to include the attachment in the connection string. Leave the attach out. You probably don't have access to the MDF file anyway. Just connect to the server on the remote machine and make sure the database credentials allows the user access to the database. – jdweng Sep 17 '19 at 10:57

1 Answers1

0

You actually can connect to a localdb instance from other machines. It's a perfectly valid configuration. (Although, if you're really running it like a service, you'd be better off with SQL Server Express Edition instead).

First, you need to make sure that the localdb instance is fired up. That can be your application, or it can be via the SqlLocalDB Utility. See here: https://learn.microsoft.com/en-us/sql/tools/sqllocaldb-utility?view=sql-server-2017

Second, you need to make sure that TCP is enabled. Details of setting up a shared instance are described here: https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/sql-server-express-localdb?view=sql-server-2017

Hope that helps.

Greg Low
  • 1,526
  • 1
  • 4
  • 4