Understanding Data Source=(local) in SQL Server Connection Strings

Source: https://blogs.msdn.microsoft.com/sql_protocols/2008/09/19/understanding-data-sourcelocal-in-sql-server-connection-strings/


Lately we have noticed many misunderstandings surrounding the usage of the Data Source keyword in connection strings, caused by people generalizing from an example demonstrating a connection string for local connectivity and creating their own connection string for a remote connection.  Here is one such example connection string for local connectivity as it would be used in VB.Net:

Public con As New System.Data.SqlClient.SqlConnection(“Integrated Security=SSPI;Initial Catalog=TestDatabase;Data Source=(local);”)

This connection string’s options can be dissected as:

–          Integrated Security=SSPI; – This means we want to connect using Windows authentication

–          Initial Catalog=TestDatabase; – This means the database we want to first connect to is named “TestDatabase”.

–          Data Source=(local); – This means that we want to connect to the SQL Server instance located on the local machine.

The confusion occurs with this last option, since people think that “local” is a keyword referring to the local machine account, when in fact the special keyword is “(local)”, including the parentheses.  As a result, if they want to use a remote connection, and if their server’s name is, for example, TestServer with IP address 10.1.1.10, they try to use the connection string option: “Data Source=(TestServer)” or “Data Source=(10.1.1.10)”.  Since the special keyword here is the whole word “(local)”, the correct connection string option would be: “Data Source=TestServer” or “Data Source=10.1.1.10”.  So, as a whole line of code, this would now read:

Public con As New System.Data.SqlClient.SqlConnection(“Integrated Security=SSPI;Initial Catalog=TestDatabase;Data Source=TestServer”)

or:

Public con As New System.Data.SqlClient.SqlConnection(“Integrated Security=SSPI;Initial Catalog=TestDatabase;Data Source=10.1.1.10”)

 

Leave a Comment