I ran across a great article on Connection Strings from my good friend, Allen White (b, t, c), recently. Quite a few of the items he mentioned in there, I knew, but it was a good review to stop and think about how to actually connect to systems. Especially as many of us will be moving to more complex, distributed environments that might include cloud (public or private) resources.
The address part of the connection is simple for many people, but often misunderstood if you’ve only worked in single domain with default instances. Many people are used to just putting in the name of the server and connecting from SSMS or some other dialog based application.
However I do think it’s important to understand that connections to SQL Server are just like browsing the web. For example, I can do this:
This is a connection of the format:
protocol:name of server, port;
We don’t often worry about protocols, but in case you might want to ensure it’s a TCP connection, specify it. This is like:
In this case, I’d specified http. However you should try ftp:, https:, or other protocols in your browser. You might be surprised how often these work.
The server address is a fully qualified domain name (FQDN). In a single domain, local network, you often just put the name of the host, but you should be aware that any address works.
The port is 1433 by default, but you should be aware that this can be changed by the SQL Server administrator in the Configuration Manager, in Services, or even with firewall rules. If you’re ever in doubt, specify it.
I hadn’t thought about security being more than trusted or not, but Allen pointed out a couple of settings I need to use. He talks about using SSPI to ensure Kerberos is used and also to set a value to encrypt the connection string. Both are good ideas.
Five Minutes of Learning
Read Allen’s article and learn a bit more about connection strings, or review your knowledge. Little pieces like this can help keep your skills sharp and allow you to pick up tidbits of knowledge that might really help you some day.