Luminate Security offers a Software-as-a-Service application access platform that allows users to connect to any kind of application hosted on any datacenter platform without the need to have a direct network connection. For basic explanation about Luminate and how it works, please refer to this article.
Luminate allows Database Administrators to connect to Microsoft SQL Server databases hosted in company data centers or in IaaS / PaaS locations using various methods. This article will explain the required configuration steps and the resulting end-user experience when using Microsoft SQL Server Management Studio or Microsoft SQL Command Line Tools.
The below diagram depicts the components of the system:
The clients are accessing Microsoft SQL Servers that are located in on-premises or IaaS/PaaS datacenter (including Azure SQL Databases PaaS).
The basic assumption is that there is no network connectivity between the DBA's PC and the corporate data center and that the access is being done via Luminate Security.
The precondition to accessing Microsoft SQL Server databases with Luminate is an SSH Server (Linux machine or a Docker Container) configured in Luminate Administration Portal (as an SSH Application) that has TCP access to the relevant Database Servers. In order to configure this, please refer to this article.
Once the SSH Server / Bastion is configured, following steps need to be taken in order to configure the connections to the databases:
1. Get the details of the SSH Server / Bastion that will be used for connection from the Luminate User Portal
a. Log in to the Luminate User portal using your web browser at the following address: https://<your company tenant>.luminatesec.com
b. Choose the relevant SSH Server from the list of the applications (this should be the SSH Server / Bastion that has connectivity to the PostgreSQL Servers inside the corporate datacenter)
c. Copy the "Host Name for SSH Client" and "User Name for SSH Client" fields from the UI:
2. Establish an SSH Tunnel (Port Mapping) to the Database Server(s)
- Since Microsoft SQL Server clients, unlike those for most of other databases (MySQL, PostgreSQL, Oracle, MongoDB, Cassandra, ...) does not support defining connections that use implicit SSH Tunnel, the tunnel should be created explicitly.
- Various SSH Clients can be used to create the tunnel, in this explanation we will show how to do this with an OpenSSH client, that is the simplest and the easiest to use.
Run the following SSH Command:
ssh <User>@<Bastion-Application-Name>@<Bastion-Application-Luminate-URL> -N -L <Local-Port>:<Database-Server-Address>:<Database-Server-Port>
- <User>@<Bastion-Application-Name> is the content of the "User name for SSH client" taken from the Luminate User Portal in the previous step
- <Bastion-Application-Luminate-URL> is the content of the "Host name for SSH client" taken from the Luminate User Portal in the previous step
- <Local Port> is a free port that the SQL Server Clients will be connecting to. If multiple SQL Servers need to be accessed at the same time, different local ports should be used for different servers
- <Database-Server-Address> is the DNS or IP of a database server, that should be accessible from the Luminate Connector
- <Database-Server-Port> is the port on the Microsoft SQL Server that the clients should be connecting to, default is 1433
3. Connect to the SQL Server from a client application
After establishing a secure and authenticated tunnel, in the next step one needs to launch a Microsoft SQL Server client application and connect it to the local port defined in the step above.
Microsoft SQL Server clients are unique in their implementation, as stated below:
|Please note, that Microsoft SQL Server client-side libraries take the DNS from the connection string and assume that it is also the logical database server name. This assumption makes connectivity via proxies very challenging, and it is of utmost importance to use IP Addresses, rather than Domain Names when defining connectivity using the below procedures.
|Please note, that Microsoft SQL Server client libraries use a different syntax for connecting to custom ports. Unlike the regular "IP:Port" scheme, these clients use the "IP,Port" one.
In order to connect to a remote database with SQL Command Line tools, following command should be issued:
SQLCMD -S TCP:127.0.0.1,<Local-Port> -U <User>@<Database-Server-Name> -P <Database-Password> -d <Default-Database>
- <Local-Port> - Is the local port defined in the previous step that maps the secure SSH Tunnel to the relevant Microsoft SQL Server
- <User> - Database user for authentication (SQL Servers have different authentication schemes, the default being the SQL Server Auth)
- <Database-Server-Name> - This is a very important field, it should contain the actual name of the database server. For example, if the server is provided by Microsoft Azure SQL Database (PaaS), this name should be something like <Server-Name>.database.windows.net
- <Database-Password> - Password for the relevant user (within the relevant authentication scheme)
- <Default-Database> - Logical database that the client
Please note, that due to the above warnings, it is important to use an IP address (127.0.0.1) rather than "localhost" (otherwise the SQL Server Client library will assume "localhost" as the name of the database server).
In order to connect to a remote database with SQL Server Management Studio, the connection should be configured in the following manner:
(9006 is a local port defined for a connectivity)