(SQL) Tip of the Day: Connecting with Management Studio

Today’s (SQL) Tip…

Recently, I have seen several occasions where customers could not connect a tool such as Management Studio to their Azure databases.  There are a variety of reasons that connectivity failures can occur, but here's an initial checklist that will typically result in a successful connection.

  • On the Azure Management Portal, ensure that the IP address being used is allowed through the "firewall."
    • You can also ensure this through management studio if you have another machine somewhere that can connect with it.
  • Set the server name to your Azure server.database.windows.net.
  • Change to SQL Server Authentication and enter credentials that have been granted permissions on at least one database on the server.
    • Note that I am *not* using the username@servername syntax here!
  • Alter the Management Studio connection window by clicking the Options button.

clip_image001

  • Change the connection properties as shown below to reflect the database desired, the TCP/IP protocol, and a connection timeout setting of 45 seconds.  Changing these three things will eliminate most of the connection difficulties we see almost every day.
    • Changing the database to the one desired will prevent multiple hops through master to get to the right set of data.  In addition, logins that do not have permissions to the entire server may have difficulties connecting to any other database.  Databases also have firewall rules (not to be confused with the server-level firewall rules.)  Therefore, an IP address that is allowed at the database level may actually connect whereas not specifying the database name may not connect.
    • Certain DLLs and fallback settings may try to use named pipes as a connection protocol.  As SQL Azure doesn’t support that protocol, forcing TCP usually helps matters.
    • Finally, the internet can be inconsistent.  A setting of 45 seconds for the connection timeout field usually provides ample time for a login to succeed and the authentication tokens to be received by management studio before an error is thrown complaining of a timeout.

clip_image002