logo

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 one off 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
Required
Description
HOST
env-var
yes
The IP or Host of the SQL Server
PORT
env-var
yes
The port of the SQL server
USER
env-var
yes
The user with access to the instance
PASS
env-var
yes
The password with access to the instance
DB
env-var
yes
The name of the database to connect (Required when using the connection via command line).
INSECURE
env-var
no
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 database/mssql -a <agent> \ -e HOST=<host> \ -e PORT=<port> \ -e USER=<user> \ -e PASS=<pass> \ -e DB=<db>
create the connection

How to Use

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

Powered by Notaku