Topics from the Microsoft SQL Server Protocols team - Netlibs, TDS, SQL Browser, etc.
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 BenediktsonSQL Server ProtocolsDisclaimer: This posting is provided "AS IS" with no warranties, and confers no rights
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
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
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