Unable to connect to local database SQL

Question

I have a problem with my local database made with SQL Server (Local DB). I can connect to the database on my computer but if I try to another computer, I get this error message:

I want a local database to store data, I don't need a server to manage the database.

This is my connection string:

`<connectionStrings>
    <add name="stocksDB" connectionString="Data Source= (LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\myDB.mdf;Integrated Security=True;" providerName="System.Data.SqlClient"/>
  </connectionStrings>`

I have included the "SQL Server 2012 Express LocalDB" in prerequisites.

What did I do wrong?

LocalDB is a ... local database. It is not intended to be shared between machines like a server solution. — Steve, Apr 28 '18 at 18:40
It's my first experience with database on .NET, I would have a local database that works only on the computer where my software is running. I use it to store data. What should I do so? Thanks — Fabrizio402, Apr 28 '18 at 18:44
Then you simply install the MDF in the same path on your second machine. In your context this is the path pointed by |DataDirectory|. Usually (unless changed by code) this directory is the same directory where your executable is in a desktop app or the APP_DATA folder in a web project. Remember that the database process need read/write permissions on this file and on the folder. This has consequences on a desktop app where the executable folder is usually write protected. — Steve, Apr 28 '18 at 18:47
thank you, the MDF file is on the same directory where my exe file is. It works when I test it on my computer launching the exe file outside VisualStudio but not on my other computer even with all permissions. I will try on the my friend's computer too. — Fabrizio402, Apr 28 '18 at 18:56
I don't need my computer as server or a server at all. I need an easy database that works locally, I don't understand why it doesn't work — Fabrizio402, Apr 28 '18 at 20:28

VA systems engineer · Answer 1 · 2018-04-29T10:19:07.990

If you have two computers (lets say their machine names are "pc_a" and "pc_b" that are networked together and the program is running on computer "pc_a" and the database resides on computer "pc_b", then your connect string needs to include the machine name for computer "pc_b".

You can provide the machine name even if it is the local machine, so the code below will work if the program is running on the same machine as the database or if the program is running on one machine and the database is on another, so long as the two machines are networked AND the account you're running the program under has access to the machine and instance and database.

Please note in example below, the "default" instance name (MSSQLSERVER) was used when SQL was installed. When the DB instance name is the default name, then you must not provide an instance name explicitly (you'll get the error you showed if you do). The only time you provide an instance name explicitly is when it it not the default instance name. The code below can handle either scenario (by setting dbInstanceName variable to "" or an instance name, e.g. "\SQLEXPRESS"). See S.O. SQL Server: How to find all localdb instance names. When it doubt, try an empty instance name and a name you believe to be the instance name to see what works.

string databaseMachineName = "pc_b";
string databaseInstanceName = ""; 
string dbName = "stocksDb";

using (SqlConnection sqlConnection = new SqlConnection("Data Source=" + databaseMachineName + databaseInstanceName + "; Initial Catalog=" + dbName + "; Integrated Security=True;Connection Timeout=10"))
{
   .
   .
   .
}

I don't need a Server, I need a local database that runs on each computer where my software is running — Fabrizio402, Apr 28 '18 at 20:25

score 0 · Accepted Answer · answered Apr 28 '18 at 21:18

Solved! The problem was the wrong SQL Server version on the other computer. On my main computer I have SQL Server 2014 and on the other one the 2012 version so the "database instance name" was different. Thanks to @Nova Sys Eng for the input!

Now I changed my connection string: First of all I used a code to retrieve all the SQL server instances installed on the computer as explained on the link posted by Nova Sys Eng.

   var instances = GetLocalDBInstances();
   var connString= string.Format("Data Source= (LocalDB)\\{0};AttachDbFilename=|DataDirectory|\\myDB.mdf;Integrated Security=True;",instances[0]);

 internal static List<string> GetLocalDBInstances()
        {
            // Start the child process.
            Process p = new Process();
            // Redirect the output stream of the child process.
            p.StartInfo.UseShellExecute = false;
            p.StartInfo.RedirectStandardOutput = true;
            p.StartInfo.FileName = "cmd.exe";
            p.StartInfo.Arguments = "/C sqllocaldb info";
            p.StartInfo.CreateNoWindow = true;
            p.StartInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Hidden;
            p.Start();
            // Do not wait for the child process to exit before
            // reading to the end of its redirected stream.
            // p.WaitForExit();
            // Read the output stream first and then wait.
            string sOutput = p.StandardOutput.ReadToEnd();
            p.WaitForExit();

            //If LocalDb is not installed then it will return that 'sqllocaldb' is not recognized as an internal or external command operable program or batch file.
            if (sOutput == null || sOutput.Trim().Length == 0 || sOutput.Contains("not recognized"))
                return null;
            string[] instances = sOutput.Split(new string[] { Environment.NewLine }, StringSplitOptions.None);
            List<string> lstInstances = new List<string>();
            foreach (var item in instances)
            {
                if (item.Trim().Length > 0)
                    lstInstances.Add(item);
            }
            return lstInstances;
        }

Unable to connect to local database SQL

2 Answers2