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 (17)

  1. Soczo Zsolt says:

    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?

  2. SQL Protocols says:

    For clients shipped with SQL Server 2005 and beyond, all of (local), ., and localhost are equivalent and are not tied to any particular protocol.  You can verify this for yourself pretty easily if you have a local SQL Server 2005 instance with both TCP and Shared Memory enabled:

    C:>osql -E -Stcp:(local)

    1> select net_transport from sys.dm_exec_connections where session_id = @@SPID

    2> go

    net_transport

    —————————————-

    TCP

    C:>osql -E -Slocalhost

    1> select net_transport from sys.dm_exec_connections where session_id = @@SPID

    2> go

    net_transport

    —————————————-

    Shared memory

    Machine name is slightly different from those, since depending on the network transport protocol, we may have to do a DNS lookup to resolve it to an IP Address.  You can still get shared memory from using the local machine name, though:

    C:>osql -E -SLocalMachineName

    1> select net_transport from sys.dm_exec_connections where session_id = @@SPID

    2> go

    net_transport

    —————————————-

    Shared memory

  3. Md.Meraj says:

    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

  4. Sam says:

    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  

  5. SQL Protocols says:

    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

  6. csjones says:

    Great blogs! Keep up the good work 🙂

  7. gaurav says:

    thanks!!!

    good explanation and it is very helpful

  8. nitk says:

    how to use  mixed mode authentication??

  9. Mgargi says:

    I would like to connect to my local sql server, which of the following is better and why  :

    DataSource = Local

    DataSource = .

    DataSource = LocalHost

  10. SQL Protocols says:

    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.

  11. YP says:

    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.

  12. Rohit Gopidi says:

    Thats a Neat Explanation.. Keep Going

  13. This posting is provided "AS IS" with no warranties, and confers no rights says:

    This posting is provided "AS IS" with no warranties, and confers no rights

  14. Larry Whitaker says:

    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.

  15. Peter Richards says:

    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.

  16. Shuan says:

    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.

  17. Ehsan says:

    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)