Microsoft SQL Server has a feature called “Named Instances”. This allows you to run multiple databases on the same host (or clustered hosts) with separate settings. Each instance runs on its own port, but when you are using Microsoft stuff you don’t need to know the port number, and you connect by the name. For instance, if your host name is “dbserver” and your instance is named “proddb”, you would connect using the hostname “dbserver\proddb”.
What happens under the covers is that your client contacts “dbserver” on the default MS-SQL port (1433).
- MS-SQL responds with the named instance’s port. The client then connects to that port.
Looker (and many other apps which are not Microsoft-centric) does not know how to do this. Once it connects to a port it expects to be able to do queries and it will not go talk to any other port.
- For Looker to connect to an MS-SQL named instance, our customer will need to find out what port the named instance is running on. There is no standard port which named instances run on as MS-SQL chooses a random port at startup.
Here is a page describing how to find the port:
That page is for 2012 but the procedure should be similar across the various versions of MS-SQL.
Once the customer has the port number, they’ll use it in the Looker database connection configuration. For example, if the port is 61499 and the host is named dbserver, their connection in Looker will look like this:
Note that the db hostname will NOT be dbserver\proddb.
Lastly, it is important to note that MS-SQL may choose a different port for the named instance when they reboot the server. That could require them to change firewall rules and reconfigure Looker to use the new port. Therefore the customer may wish to assign a static port to their named instance as detailed in this page: