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")
Public con As New System.Data.SqlClient.SqlConnection("Integrated Security=SSPI;Initial Catalog=TestDatabase;Data Source=10.1.1.10")
SQL Server Protocols
Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights