Understanding “Data Source=(local)” 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")

Dan Benediktson
SQL Server Protocols
Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

Comments

• Anonymous
  September 23, 2008
  And what's the difference between (local), ., localhost and machine name? Are they use the same protocol, or (local) and . use shared memory and localhost and machine name use tcp/ip?

• Anonymous
  September 23, 2008
  The comment has been removed

• Anonymous
  January 03, 2009
  Unable To Connect Remote Connection when my asp.net website hosting after that when user logining then it will pass the error message. Code Behind---------  SqlConnection con = new SqlConnection();       // con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\SiliguriNeuro.mdf;Integrated Security=True;User Instance=True";       con.ConnectionString = "Data Source=.\SQLEXPRESS,1433;AttachDbFilename=|DataDirectory|\SiliguriNeuro.mdf;Initial Catalog=SiliguriNeuro;Integrated Security=SSPI;User ID=nrhalder;Password=12345678;";      //  con.ConnectionString = "Data Source={0};User ID={1};Password={2}";        con.Open();        string UserName = TextBox1.Text;        string Password = TextBox2.Text;      //  Class1.UserType = TextBox1.Text;     //   Class2.UserId = UserName.ToString();        SqlDataReader dr;        string str = "select * from Login where UserName='" + UserName + "' and Password='" + Password + "'";        SqlCommand cmd = new SqlCommand(str, con);        dr = cmd.ExecuteReader();        if (dr.HasRows)        {            Server.Transfer("PatientDetails.aspx");        }        else            Label6.Text = "You are not a Valid User"; please help me

• Anonymous
  January 09, 2009
  I've got a 2 node 2005 cluster.  Is there a way to make local work on it.  I'm assuming it's trying to connect to the local default instance on the machine, which would be myclusterNode1 - obviously not going to work, as it needs to be going to the virtual SQL name. Thanks Sam  

• Anonymous
  January 14, 2009
  Sam, When connecting to cluster instances, you cannot use (local) and must use the virtual server name, please refer to my other blog for details: http://blogs.msdn.com/sql_protocols/archive/2005/12/05/500013.aspx Thanks, Xinwei

• Anonymous
  July 13, 2009
  Great blogs! Keep up the good work :)

• Anonymous
  July 14, 2010
  thanks!!! good explanation and it is very helpful

• Anonymous
  October 27, 2010
  how to use  mixed mode authentication??

• Anonymous
  August 23, 2011
  I would like to connect to my local sql server, which of the following is better and why  : DataSource = Local DataSource = . DataSource = LocalHost

• Anonymous
  August 24, 2011
  Mgargi: note that, as the blog mentions, the correct string is not "Local" but rather "(local)". As the second comment describes, none of them is better than any other - they are equivalent. So, you may use whichever you want.

• Anonymous
  August 28, 2011
  By testing, I find ADO connection time out is differ in two kind of connection string:

1. data source = "ip\instance name", connecttimeout=5s, connection is so slow, try serval times, and every time is over 30s;
2. data source = "server name\instance name",connecttimeout=5s, the first connecion is slow, but after is fast, about 5s; Btw, the database is sql sever 2008 express R2; Please tell me why I get this result, thank you so much.

• Anonymous
  December 18, 2011
  Thats a Neat Explanation.. Keep Going

• Anonymous
  July 18, 2012
  This posting is provided "AS IS" with no warranties, and confers no rights

• Anonymous
  April 17, 2014
  This is more confusing than just going on google. "Microsoft writing" is for people who already know the answer and not for people who use their product as a tool which is most of us.

• Anonymous
  May 18, 2014
  I would like to get the ip address of a connection I have made to a remote database (to visually verify that I am connected to the correct one). I used to get the connection string in VB using the adox catalog.activeconnection value. that does not work under vb.vet. What is the solution? I can't seem to obtain the values anywhere in the catalog.

• Anonymous
  August 05, 2014
  Thanks for your great explanation... Nevertheless I could not solve my issue. Actually, I would like to Run my C# windows application on a computer with windows XP and SQL Express R2 installed. So, I tried any possible connection strings but none of them worked! any suggestions will be highly appreciated.

• Anonymous
  September 12, 2015
  I am getting a message here like keyword not supported 'id' Imports System.Data.SqlClient Public Class loginform    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click        Dim con As New SqlConnection        Dim com As New SqlCommand        Dim dr As SqlDataReader        Dim i As String        i = Environment.MachineName.ToString        Try            If TextBox1.Text = "" And TextBox2.Text = "" Then                MsgBox("Please enter username and password")                TextBox1.Focus()            ElseIf TextBox1.Text = "" Then                MsgBox("Please enter username")                TextBox1.Focus()            ElseIf TextBox2.Text = "" Then                MsgBox("Please enter password")                TextBox2.Focus()            Else                con.ConnectionString = "Data Source= " & i & "SC-EHSANSQLEXPRESS;Integrated Security=False;Connect Timeout=15;Encrypt=False;TrustServerCertificate=Ture ;uname=Gms;Pwd=maghavaN"                'con.ConnectionString = "Data Source=SC-EHSANSQLEXPRESS;Initial Catalog=school;Integrated Security=True"                con.Open()                com.Connection = con                com = New SqlCommand("select * from username  WHERE uname='" & TextBox1.Text & "' and pwd='" & TextBox2.Text & "'", con)                dr = com.ExecuteReader()                If dr.Read Then                    Dim main As New Main                    main.Show()                    main.Label6.Text = TextBox1.Text                    Me.Hide()                Else                    MsgBox("Enter correct username and password")                End If                con.Close()            End If        Catch ex As Exception            MsgBox("" & ex.Message)