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

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

Rate This
  • Comments 15

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

Leave a Comment
  • Please add 5 and 6 and type the answer here:
  • Post
  • 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?

  • 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

  • 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

  • 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  

  • 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

  • Great blogs! Keep up the good work :)

  • thanks!!!

    good explanation and it is very helpful

  • how to use  mixed mode authentication??

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

    DataSource = Local

    DataSource = .

    DataSource = LocalHost

  • 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.

  • 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.

  • Thats a Neat Explanation.. Keep Going

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

  • 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.

  • 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.

Page 1 of 1 (15 items)