FAQ: How do I find the correct "server" or "data source" value for an SQL Server instance in a connection string?

Questions:

To connect to an instance of SQL Server, you need to specify the server in the connection string. However, after the SQL Server installation, you do not know which server name you should input in the connection string is. This article is to let you know how to find the correct server value.

Answer:

To get the server value of a SQL Server instance, the first thing you need to do is to find the instance name.To find the instance name of a SQL Server instance, please follow the steps below:

• 1. Open SQL Server Configuration Manager (SSCM) from Start - > All Programs -> Microsoft SQL Server 2005/2008/2008 R2 -> Configuration Tools;
• 2. In the SSCM, click SQL Server Services under SQL Server Configuration Manager (Local) , you could find list times like SQL Server (<InstanceName>) , the < InstanceName > is exactly what we need to find, instance name. If you install multiple instances on the server, you will see more than one SQL Server (<InstanceName>) list times there.

After you find out the instance name, you can determine whether it is a default instance or named instance. If the instance name is MSSQLSERVER, it is a default instance. Otherwise, it should be a named instance such SQLEXPRESS, SQL2008 and so on. You can install multiple instances on a server but only one default instance.

After you get the instance name and know instance type (default or named instance), you will know the value of server or data source:

For default instance, use <ComputerName> as the server name; and for named instance, use <ComputerName>\<InstanceName> as the server or data source.

Note: If the SQL Server instance is installed on the local server, you can also use localhost, (local), . (dot), 127.0.0.1 (the IP address of localhost), and local IP address and <ComputerName>. If the SQL Server instance is installed on the different trusted domain, you may need to use full computer name such as ComputerName.domain.corp.company.com instead of <ComputerName>. If the SQL Server installed is public network environment, you may need to use IP address instead of <ComputerName>. If you just know the IP address/computer name and the TCP port for a SQL Server named instance, you can use "tcp:<server ip address/computer name>,<TCP port>" as the value of server or data source.

Once we get the server value, we can make connections to the SQL Server instance with the correct connection string as follows:

Server=WinSrv1\SQL2008;Database=myDataBase;UID=myUsername;PWD=myPassword;

orData Source=WinSrv1\SQL2008;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

References

Using Named Instances
https://msdn.microsoft.com/en-us/library/ms165614(SQL.90).aspx;
Localhost
https://en.wikipedia.org/wiki/Localhost.