MSSQL Server

Connecting to Microsoft SQL Server can be done natively using an IDE or any supported client library. Alternatively, you can use the sqlcmd command line utility for ad-hoc executions.

Features

The table below explain which features are available for this kind of connection.

Native - it's when a database client is connecting via protocol (.e.g: IDE, client libraries)

One Off - it's when a hoop client performs ad hoc executions (e.g: webapp, hoop cli)

Feature	Native	One Off	Description
TLS Termination Proxy	✅	✅	The local proxy terminates the connection with TLS, enabling the connection with the remote server to be TLS encrypted.
Audit	🚫	✅	The gateway store and audit the queries being issued by the client
Data Masking (DLP)	🚫	✅	A policy can be enabled to mask sensitive fields dynamically when performing queries in the database.
Credentials Offload	✅	✅	The user authenticates via SSO instead of using database credentials.
Interactive Access	✅	✅	Interactive access is available when using an IDE or connecting via a terminal to perform analysis exploration.

Configuration

Name	Type	Description
`HOST`	env-var	The IP or Host of the SQL Server
`PORT`	env-var	The port of the SQL server
`USER`	env-var	The user with access to the instance
`PASS`	env-var	The password with access to the instance
`DB`	env-var	The name of the database to connect (Required when using the connection via command line).
`INSECURE`	env-var	If the connection will ignore the chain of certificates presented by server. (the connection will be subject to man in the middle attacks if the network is not reliable. By default this option is set to `false`)

Connection Command

shell
hoop admin create conn mssql --type mssql -a <agent> \
  -e HOST=<host> \
  -e PORT=<port> \
  -e USER=<user> \
  -e PASS=<pass> \
  -e DB=<db>

create the connection

Connect It

Forward a local port (default to 1444) to connect in the sql server with any client/IDE

shell
hoop connect mssql

plain text
connection: mssql | session: 5531dc59-febc-4eee-af26-96e0e0de6769

---------------------mssql-credentials----------------------
      host=127.0.0.1 port=1444 user=noop password=noop
------------------------------------------------------------
ready to accept connections!

Test it using sqlcmd utility tool

shell
sqlcmd -S 127.0.0.1:1444 -Q "SELECT @@VERSION"

In the same connection, one-off process can be run as well

shell
hoop exec mssql <<EOF
SELECT @@VERSION
GO
EOF

shell
hoop exec mssql -f /tmp/myquery.sql
hoop exec mssql -i 'SELECT @@VERSION'
# pass arguments to sqlcmd
hoop exec mssql -- -s ";" -h -1 -W <<EOF
SELECT * FROM master.INFORMATION_SCHEMA.SCHEMATA
EOF