First published on MSDN on Sep 19, 2008
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
