Connecting to SQL Server

It is important to understand how clients connect to Microsoft SQL Server. When you understand this then all the networking and firewall items make much more sense! It is also worth noting that you can connect with different technologies, like Java, PHP as well as the more Microsoft ADO.NET.

Secure Connections

It is possible to use security certificates to make encrypted connections to SQL Server if sensitive data needs to be transported over a non-secure or un-trusted link. See the following articles for details:

JDBC Connections

Microsoft do suppply JDBC drivers for connecting to SQL Server, see Microsoft JDBC Driver for SQL Server for details. Note that the 4.0 drivers will support Java 7 at runtime, but you will have compilation issues. Here are some tips, based on my experience:

  • See Building the Connection URL, it is worth reading
  • The JDBC driver does not support "(local)" in the connection string like SSMS does, use "localhost" instead
  • Enable TCP/IP connections the JDBC drivers seems to rely on TCP/IP, even if the database is local, dynamic ports work fine by the way

SQL Server Management Studio Connections

It is worth noting that SSMS defaults to using a "Shared Memory" connection. So if you want to test whether your TCP/IP connection is working or that you can connect to a specific port you will need to turn Shared Memory connections off. Once off, you can stop the SQL Browser process and then see if your connection needs it or not.

The format of an SSMS connection is as follows: servername\instancename,port where servername can be (local) if indeed the database is running on the same box as SSMS and where you can use just the servername when the instance has the default name of MSSQLSERVER, the port number is only needed if the SQL Browser service is not running and the instance name is not MSSQLSERVER.