SSH and Remote Host Port when using SSH tunnel

[nbauer]

Here is our use case.

• We are using SQL Server and clients are distributed across multiple SQL Server instances
• We only want to maintain one version of our LookML code (connection string in the model does not change)
• For the connection settings for this connection string, we need to use an SSH Server.
• The SQL Server database name is controlled by an User Attribute

The problem is that the SQL Server “Remote Host Port” does not allow configuration based upon a User Attribute, yet we need this single connection to be able to access multiple hosts.

As yet, we’ve been told this can’t be done, but we haven’t had an explanation of why it can’t be done.  Is this simply a limitation in the current UI?  Is this something fundamental about using an SSH tunnel (by the way, we can configure a single tunnel to talk to multiple servers)?

This just seems to be so basic.  That is, have a single Looker instance with a common connection to SQL Server, where both the SQL Server Host and SQL Server Databasename can be set via User Attributes.  Why, exactly, can this not be done?

Thanks!

[Hugo_Selbie]

Hi @nbauer, 

Although that feature doesn’t exist in the Looker product (i.e. the ability to parameterize host and port) when using an SSH tunnel. For your use case you might consider investigating linked servers. 

https://docs.microsoft.com/en-us/sql/relational-databases/linked-servers/linked-servers-database-eng...

https://docs.microsoft.com/en-us/sql/t-sql/functions/openquery-transact-sql?view=sql-server-ver15

That way you can have one master server parsing queries to your child independent servers all within your VPC and Looker treats it like one connection which can be handled over the SSH tunnel.

The parameterization would be handled at the LookML level with `sql_table_name: {{ _user_attributes['linked_server'] }}.schema.table_name ;;` Where the user attribute would reference the appropriate server name and direct the traffic to the appropriate sql server instance.

[bfayle]

This was a huge problem for my team as well when implementing Looker SSO embed in our application. We needed the ability for our pipeline to support production, development, staging and pull request environments.

We would have preferred to have a single model and dynamically control the database connection using user attributes.

Instead we found it necessary to create a model specific to each environment that's hard coded to its own connection. The pipeline dynamically creates the connection and environment specific model in Looker via the API.

The ugly part of this is we don't want these environment specific models to be in the master branch, especially the pull request specific environments. This complicates the merging process as we need to cherry pick commits to prevent these models getting merged.