2.2 Common Connection Problems

I’ve encountered a wide variety of errors when attempting the initial connection. They usually stem from one or more of the following:

  • I didn’t build a valid connection string.
    • ConnectionStrings.com provides a reference for connection string formats.
  • I misspelled something. I need to correct server and database names more than I’d like to admit.
  • I don’t have permissions to the SQL Server.
    • When I see errors like the “The server principal is not able to access.” then I suspect permissions are the issue. If I don’t own the server, then I submit a ticket to the help desk for authorization. If I own the server, then I check the SQL server logs through SQL Server Management Studio. The logs usually direct me to either create a user or grant permissions.
  • I don’t have the right driver selected.
    • It’s easy to attempt a connection with a driver that doesn’t exist on the client. Windows users can check available drivers by searching Administrative tools -> Double click Data Sources. If I publish to an external source, e.g. ShinyApps.IO, then I check their support docs for available driver names. At the time of this blog post, they have SQL Server driver named “SQLServer” available.
  • I need to specify the port number.
    • Sometimes the default port is changed from 1433. In that case, I must specify the port number after the server name, e.g. myServer = ‘.,2050’
  • A firewall is blocking the connection.