SQL Server 2014 connection string with instance name

Question

My working SQL Server 2014 connection string is:

Data Source=localhost;Initial Catalog=myDb;Integrated Security=True;

I need to install a new instance of SQL Server 2016 on the same server. Therefore I need to modify the existing connection string and to add the instance name.

I was trying (MSSQLSERVER is the instance name):

"Data Source=localhost\MSSQLSERVER;Initial Catalog=myDb;Integrated Security=True;" providerName="System.Data.SqlClient" 

AND:

"Server=localhost/MSSQLSERVER;Database=myDb;User Id=user; Password=password;" providerName="System.Data.SqlClient"

AND more but could not make it work.

The error I am getting is:

The network name cannot be found

Answer 1

If you have SQL Server 2014 as your default instance (with no instance name needed to connect to it - that's the MSSQLSERVER "instance", but that name must not be used in the connection string!), then you must use a separate, different instance name for your SQL Server 2016 installation, e.g. SQL2016.

In that case, your connection string will need to use .\SQL2016 or (local)\SQL2016 or localhost\SQL2016 as the server/instance name (defined by the server= or data source= settings in the connection string).

So your connection string for SQL Server 2016 should be something like:

Data Source=localhost\SQL2016;Initial Catalog=myDb;Integrated Security=True;

You can go to the SQL Server Configuration Manager to see what services are defined and thus what instances are present on your machine:

Look for the SQL Server services - the value in parenthesis is the instance name (where MSSQLSERVER stands for the default instance that doesn't need to be specified as such - just the machine name is enough for connecting to the default instance)