SQL Studio Gateway

1. Overview

How it works

SQL Studio for Mac, iPhone and iPad never connects to SQL Server directly. The app talks only HTTPS/JSON to SQL Studio Gateway, a .NET 10 service that you install on your own server. The gateway does all the work against SQL Server (TDS connections, SMO, DacFx, ScriptDom) on behalf of the app.

  Mac · iPhone · iPad                                                        Your SQL estate
┌────────────────────┐   HTTPS (TLS 1.2/1.3)   ┌──────────────────────────┐  TDS (Encrypt) ┌────────────────┐
│ SQL Studio app     │ ──────────────────────▶ │ SQL Studio Gateway       │ ─────────────▶ │ SQL Server     │
│ (App Store)        │  port 8443, certificate │ .NET 10 service          │  port 1433     │ Azure SQL      │
│ Keychain, sandbox  │  pinning, signed tokens │ users · allow-list ·     │  allow-list    └────────────────┘
└────────────────────┘                         │ audit · background tasks │
                                               └────────────┬─────────────┘
                                                            │ HTTPS 443 (license check)
                                                            ▼
                                                   sqlstudio.geolox.it

The gateway:

• Authenticates people. Each colleague gets a personal gateway account with a strong password and optional two-step verification (TOTP). Gateway accounts are separate from SQL Server logins.
• Decides which servers can be reached. Every connection is checked against the list of allowed servers that you configure, before any network socket is opened.
• Keeps SQL credentials out of storage. SQL Server passwords and Microsoft Entra tokens stay in the gateway's memory only for the lifetime of the connection and are never written to disk or to logs.
• Runs long operations on the server. Backups, restores, data-tier (DAC) operations, script generation and imports run as gateway tasks and continue even if the user closes the app.
• Records who did what. Sign-ins, connections, uploads and tasks are written to a daily audit log.
• Hides your SQL Servers. Users reach only the gateway's HTTPS port; the SQL Servers do not need to be reachable from user devices.

The gateway listens only on HTTPS (TLS 1.2 or 1.3) and never on plain HTTP. Rights inside SQL Server are always those of the SQL login or Entra identity used to connect.

What's in the package

You receive SQLStudio-Gateway-<version>.zip, a framework-dependent .NET 10 build. The same files run on Windows and Linux.

The data directory

All persistent state lives in one folder, set by Gateway:DataDirectory. This guide uses C:\ProgramData\SqlStudioGateway on Windows, /var/lib/sqlstudio-gateway on Linux and the volume mounted at /data in Docker.

The gateway creates the folder with permissions reserved to the account that runs it (explicit ACL on Windows, 700/600 on Linux). It contains keys: treat the folder and its backups as secrets.

2. Before you start

Requirements

Planning checklist

Decide the following before you install. Each item links to the section that explains it.

3. Quick start

The shortest path to a working gateway. Each step links to the detailed instructions.

Windows Server

1. Install the runtime. Install ASP.NET Core Runtime 10.0 (Hosting Bundle or ASP.NET Core Runtime x64). Details
2. Extract the package to C:\Program Files\SQLStudio Gateway. Details
3. Run the install script in an elevated PowerShell: .\extras\install-windows-service.ps1. Details
4. Edit appsettings.Production.json and list the allowed servers. Details
5. Open a console as the service account with runas. Details
6. Activate the license: .\SqlStudio.Gateway.exe license activate SQLS-XXXXX-XXXXX-XXXXX-XXXXX. Details
7. Create the certificate: .\SqlStudio.Gateway.exe cert create --host gateway.contoso.local and note the fingerprint. Details
8. Create the first user: .\SqlStudio.Gateway.exe users add admin --admin, then enable TOTP. Details
9. Start the service: Start-Service SqlStudioGateway. Details
10. Verify https://gateway.contoso.local:8443/healthz and connect the app. Details

Linux (systemd)

1. Install the runtime (ASP.NET Core Runtime 10.0). Details
2. Create the user and folders sqlstudio, /opt/sqlstudio-gateway, /var/lib/sqlstudio-gateway. Details
3. Extract the package to /opt/sqlstudio-gateway. Details
4. Create appsettings.Production.json from the example and list the allowed servers. Details
5. Activate the license, create the certificate and the first user as sqlstudio. Details
6. Install the systemd unit and start it. Details
7. Open TCP 8443 in the host firewall. Details
8. Verify with health, curl and journalctl. Details

Docker

1. Build the image from the extracted package: docker build -t sqlstudio-gateway -f extras/Dockerfile . Details
2. Create the volume sqlstudio-data. Details
3. Activate the license, create the certificate and the first user against the volume. Details
4. Run the container with the allowed servers as environment variables. Details
5. Verify the health status and print the fingerprint. Details

Then connect the apps: section 12.

4. License

Every gateway needs a valid license key to start.

Your key and download link

• The key has the format SQLS-XXXXX-XXXXX-XXXXX-XXXXX. It is delivered by email right after the purchase, together with a one-time download link for the package.
• The download link is valid for 30 days until it is used. After you click Download, the browser can resume an interrupted download for 30 minutes. If the link has expired or was already used, request a new one with the support form.
• Keep the email: you need the key again if you reinstall or move the gateway.

Seats

One license covers one server, that is one gateway installation; every other server needs its own license. An installation is identified by an installation id kept in the data directory, so the seat follows the data directory, not the machine name.

• An installation not seen by the license server for 30 days frees its seat automatically.
• To move the gateway to another server, run license deactivate on the old server, then license activate on the new one. If the old server is no longer available, contact support.
• A new, empty data directory (for example a Docker container started without its volume) is a new installation and takes another seat.

Users

Each license includes 5 gateway users: the accounts created with users add (section 9). Accounts are personal: do not share one account among several people. You can buy extra users at €9.99 per user per year, VAT included, when you buy the license or later on the Manage license page (license key and purchase email). Extra users are a yearly subscription renewed automatically; on the same page you can add more, reduce them, update the card or cancel.

• The license server tells the gateway how many users the license allows; the gateway enforces the limit locally and never sends user names or the number of accounts.
• users add refuses to create an account beyond the limit. It checks the license online first, so extra users bought a moment ago count right away.
• Extra users apply without a restart: at the first sign-in beyond the previous limit the gateway checks the license again (at most every 2 minutes), and in any case at the regular check every 6 hours.
• When there are more accounts than allowed, for example after cancelling extra users at the end of the paid year, accounts count in creation order: the oldest ones keep working, the most recent ones cannot sign in and their open sessions end. They see the message "The gateway license allows N users and this account is beyond the limit". users list marks them over the limit; remove the accounts no longer used with users remove, or buy extra users.
• Licenses issued before the user limit was introduced (October 2026) keep unlimited users.

License commands

Run them with the management command syntax for your platform (see below), as the service account.

As an alternative to license activate, set the key in the configuration: Gateway:License:Key (environment variable Gateway__License__Key). This is handy for Docker. Gateway:License:ServerUrl defaults to https://sqlstudio.geolox.it/store; do not change it unless support asks you to.

How the license is checked

• At startup the gateway contacts the license server.
  • Valid license: the gateway starts.
  • Disabled or unknown key, or no free seat: the gateway refuses to start and logs the reason with the support link.
  • License server unreachable: the gateway starts on its last valid answer if that answer is less than 14 days old, and logs a warning. Otherwise it refuses to start.
• While running the gateway re-checks every 6 hours. If the key has been disabled, the API answers HTTP 503 with the message "The gateway license is not valid…" and the support link; the apps show this message to users.
• The license server's answers are signed (ECDSA), so they cannot be forged or edited in license.json.
• Each check also reports the server's description: a non-reversible fingerprint of the operating system's machine id (Windows MachineGuid, Linux /etc/machine-id), the operating system, CPU, memory, versions and the number of gateway accounts (never their names). The installation is bound to that machine: the same data directory started on another machine is refused. Reinstalling Windows changes the machine id: run license deactivate first, or ask support to reset the installation.

Network requirements

• Outbound HTTPS (TCP 443) from the gateway to sqlstudio.geolox.it.
• Proxies: on Windows the gateway uses the system proxy settings. On Linux and Docker set the HTTPS_PROXY and NO_PROXY environment variables (examples in sections 6 and 7).

Help with keys

To add or remove extra users use the Manage license page. For lost keys, seats, transfers or download links, use the support form at sqlstudio.geolox.it/store/support.html (it sends your request to support@geolox.it) or email support@geolox.it.

Management command syntax

All management commands (license, users, cert, health) are run with the gateway program itself. In the rest of this guide <command> stands for one of them.

Windows

From the install folder, in a console running as the service account (keys are protected with DPAPI for that account). Open it from a Command Prompt with:

runas /user:.\SqlStudioGateway "cmd /k cd /d \"C:\Program Files\SQLStudio Gateway\""

then run:

.\SqlStudio.Gateway.exe <command>

Linux

cd /opt/sqlstudio-gateway && sudo -u sqlstudio env Gateway__DataDirectory=/var/lib/sqlstudio-gateway dotnet SqlStudio.Gateway.dll <command>

Docker

docker run --rm -it -v sqlstudio-data:/data sqlstudio-gateway <command>

The help command prints the list of commands. Run without a command, the program starts the gateway itself in the foreground, which is useful only for testing (stop it with Ctrl+C).

5. Install on Windows Server

This procedure installs the gateway in C:\Program Files\SQLStudio Gateway, keeps its data in C:\ProgramData\SqlStudioGateway and runs it as the Windows service SqlStudioGateway under the local account SqlStudioGateway.

Procedure

1. Install ASP.NET Core Runtime 10.0.

   Download the Hosting Bundle or the ASP.NET Core Runtime x64 installer from dotnet.microsoft.com/download/dotnet/10.0 and run it. Then check, in a new PowerShell window:

   dotnet --list-runtimes

   The output must contain a line starting with Microsoft.AspNetCore.App 10.0.

2. Extract the package.

   Open PowerShell as Administrator, go to the folder that contains the downloaded zip and run:

   Unblock-File -Path .\SQLStudio-Gateway-*.zip
   Expand-Archive -Path .\SQLStudio-Gateway-*.zip -DestinationPath "C:\Program Files\SQLStudio Gateway"

   Unblock-File removes the "downloaded from the Internet" mark so Windows does not block the extracted files. Check that C:\Program Files\SQLStudio Gateway\SqlStudio.Gateway.exe exists.

3. Run the install script.

   In the same elevated PowerShell:

   Set-Location "C:\Program Files\SQLStudio Gateway"
   Set-ExecutionPolicy -Scope Process -ExecutionPolicy Bypass
   .\extras\install-windows-service.ps1

   With no parameters the script uses the defaults below. Pass a parameter only to change it, for example .\extras\install-windows-service.ps1 -Port 9443.

   Parameter	Default	Meaning
   -InstallDir	The extracted package folder	Where the gateway files are.
   -DataDirectory	C:\ProgramData\SqlStudioGateway	The gateway's data directory.
   -Account	SqlStudioGateway	Local account that runs the service. The script creates it if missing and asks you for its password.
   -Port	8443	HTTPS port.

   The script:

   • creates the local account (if missing) and grants it the Log on as a service right;
   • sets the ACLs: read access to the install folder; full control of the data directory only for that account and Administrators;
   • writes appsettings.Production.json from the example, with the data directory and port (only if the file does not exist yet);
   • registers the Windows service SqlStudioGateway with startup type Automatic (Delayed Start), without starting it;
   • adds the inbound Windows Firewall rule for the port;
   • prints the next steps.
   Note. Store the account's password in your password vault. If you change it later, update it in the service properties too (services.msc › SQL Studio Gateway › Log On), otherwise the service will not start.
4. Edit the configuration.

   Open C:\Program Files\SQLStudio Gateway\appsettings.Production.json in an elevated editor and add at least the allowed servers:

   {
     "Gateway": {
       "DataDirectory": "C:\\ProgramData\\SqlStudioGateway",
       "AllowedServers": [ "sql01.contoso.local", "sql02.contoso.local,1433", "*.database.windows.net" ],
       "Https": { "Port": 8443 }
     }
   }

   Backslashes must be doubled in JSON. All settings are described in section 11; the pattern syntax in section 10.

5. Open a console as the service account.

   From a Command Prompt (cmd.exe, it does not need to be elevated):

   runas /user:.\SqlStudioGateway "cmd /k cd /d \"C:\Program Files\SQLStudio Gateway\""

   Type the account's password. A new console opens in the install folder, running as SqlStudioGateway. Run steps 6 to 8 in this console.

6. Activate the license.

   .\SqlStudio.Gateway.exe license activate SQLS-XXXXX-XXXXX-XXXXX-XXXXX
   .\SqlStudio.Gateway.exe license status

   If activation fails because the server cannot reach sqlstudio.geolox.it, check the firewall and the Windows proxy settings (section 4).

7. Create the TLS certificate.

   List every name users will type in the app, and optionally the IP address:

   .\SqlStudio.Gateway.exe cert create --host gateway.contoso.local --host 10.0.0.15

   The command prints the SHA-256 fingerprint. Keep it: users compare it in the app. To use a certificate from your corporate CA instead, see section 8.

8. Create the first administrator and enable two-step verification.

   .\SqlStudio.Gateway.exe users add admin --admin
   .\SqlStudio.Gateway.exe users totp admin enable

   The first command asks for the password twice (at least 12 characters). The second prints an otpauth:// URI and the secret key to add to an authenticator app; they are shown only once. Create the other users the same way (section 9).

9. Start the service.

   Back in the elevated PowerShell:

   Start-Service SqlStudioGateway
   Get-Service SqlStudioGateway

   The status must be Running. If the service stops immediately, look at Event Viewer › Windows Logs › Application: the gateway logs the reason (missing certificate, license problem, invalid setting).

10. Verify.

    On the server, in the elevated PowerShell:

    Set-Location "C:\Program Files\SQLStudio Gateway"
    .\SqlStudio.Gateway.exe health; $LASTEXITCODE

    0 means the running gateway answered; 1 means it did not. Then, from a user's network:

    curl.exe -k https://gateway.contoso.local:8443/healthz

    The answer is Healthy. You can also open https://gateway.contoso.local:8443/healthz in a browser; with a self-signed certificate the browser warns that the certificate is not trusted, which is expected (the app verifies it by fingerprint). Finally print the fingerprint again for your users:

    .\SqlStudio.Gateway.exe cert fingerprint

11. Publish the DNS name and connect the apps.

    Make sure gateway.contoso.local resolves to the server for your users, then follow section 12.

Checking the installation

Get-CimInstance Win32_Service -Filter "Name='SqlStudioGateway'" | Select-Object Name, StartName, StartMode, State
Get-NetFirewallPortFilter | Where-Object LocalPort -eq 8443 | Get-NetFirewallRule | Select-Object DisplayName, Enabled, Direction, Action
Get-Acl C:\ProgramData\SqlStudioGateway | Format-List

Whenever you change appsettings.Production.json or the certificate, restart the service: Restart-Service SqlStudioGateway. User commands (users …) take effect without a restart.

Alternative: a group Managed Service Account (gMSA)

In an Active Directory domain you can run the service as a gMSA instead of a local account. The password is then managed by the domain. Keep these points in mind:

• Grant the gMSA the same rights the script grants the local account: Log on as a service, read access to the install folder and full control of the data directory.
• Decide the account before activating the license and creating the certificate and users: keys created by one account cannot be decrypted by another. Management commands must also run as the gMSA; because a gMSA has no password you can type, runas cannot be used (use a tool that can start a process as the service account, such as PsExec).
• Windows Authentication to SQL Server uses the identity of the gateway's service account. A local account cannot authenticate to SQL Servers on other machines; if you plan to enable Windows Authentication (section 12), run the gateway as a gMSA or domain account with the least privileges needed.

6. Install on Linux (systemd)

This procedure installs the gateway in /opt/sqlstudio-gateway, keeps its data in /var/lib/sqlstudio-gateway and runs it as the systemd service sqlstudio-gateway under the system user sqlstudio.

1. Install ASP.NET Core Runtime 10.0.

   Follow the instructions for your distribution at dotnet.microsoft.com/download/dotnet/10.0 (package aspnetcore-runtime-10.0). Then check:

   dotnet --list-runtimes

   The output must contain Microsoft.AspNetCore.App 10.0. and dotnet must be at /usr/bin/dotnet, which the systemd unit uses. If it is elsewhere (command -v dotnet), adjust ExecStart in step 6.

2. Create the service user and the folders.

   sudo useradd --system --home /var/lib/sqlstudio-gateway --shell /usr/sbin/nologin sqlstudio
   sudo install -d -o sqlstudio -g sqlstudio -m 700 /var/lib/sqlstudio-gateway
   sudo install -d -o root -g root -m 755 /opt/sqlstudio-gateway

3. Extract the package.

   From the folder that contains the downloaded zip:

   sudo unzip SQLStudio-Gateway-*.zip -d /opt/sqlstudio-gateway
   sudo chmod -R a+rX /opt/sqlstudio-gateway

   The program files are owned by root and only readable by sqlstudio.

4. Create the configuration.

   cd /opt/sqlstudio-gateway
   sudo cp appsettings.Production.example.json appsettings.Production.json
   sudo chown root:sqlstudio appsettings.Production.json
   sudo chmod 640 appsettings.Production.json
   sudo nano appsettings.Production.json

   Set at least the allowed servers:

   {
     "Gateway": {
       "DataDirectory": "/var/lib/sqlstudio-gateway",
       "AllowedServers": [ "sql01.contoso.local", "*.database.windows.net" ],
       "Https": { "Port": 8443 }
     }
   }

5. Activate the license, create the certificate and the first user.

   cd /opt/sqlstudio-gateway
   sudo -u sqlstudio env Gateway__DataDirectory=/var/lib/sqlstudio-gateway dotnet SqlStudio.Gateway.dll license activate SQLS-XXXXX-XXXXX-XXXXX-XXXXX
   sudo -u sqlstudio env Gateway__DataDirectory=/var/lib/sqlstudio-gateway dotnet SqlStudio.Gateway.dll cert create --host gateway.contoso.local --host 10.0.0.15
   sudo -u sqlstudio env Gateway__DataDirectory=/var/lib/sqlstudio-gateway dotnet SqlStudio.Gateway.dll users add admin --admin
   sudo -u sqlstudio env Gateway__DataDirectory=/var/lib/sqlstudio-gateway dotnet SqlStudio.Gateway.dll users totp admin enable

   Note the SHA-256 fingerprint printed by cert create and the TOTP secret printed by users totp (shown only once).

6. Install and start the systemd unit.

   sudo cp /opt/sqlstudio-gateway/extras/sqlstudio-gateway.service /etc/systemd/system/
   sudo systemctl daemon-reload
   sudo systemctl enable --now sqlstudio-gateway

   The unit shipped in the package:

   # /etc/systemd/system/sqlstudio-gateway.service
   [Unit]
   Description=SQL Studio Gateway
   After=network-online.target
   Wants=network-online.target
   
   [Service]
   Type=notify
   User=sqlstudio
   Group=sqlstudio
   WorkingDirectory=/opt/sqlstudio-gateway
   ExecStart=/usr/bin/dotnet /opt/sqlstudio-gateway/SqlStudio.Gateway.dll
   Environment=ASPNETCORE_ENVIRONMENT=Production
   Environment=Gateway__DataDirectory=/var/lib/sqlstudio-gateway
   Restart=on-failure
   NoNewPrivileges=true
   ProtectSystem=strict
   ProtectHome=true
   PrivateTmp=true
   ReadWritePaths=/var/lib/sqlstudio-gateway
   CapabilityBoundingSet=
   
   [Install]
   WantedBy=multi-user.target

   The gateway supports Type=notify, so systemd knows when it is ready. The unit only allows writes to the data directory and grants no capabilities (port 8443 does not need any).

7. Open the port in the host firewall.

   With firewalld:

   sudo firewall-cmd --permanent --add-port=8443/tcp
   sudo firewall-cmd --reload

   With ufw:

   sudo ufw allow 8443/tcp

8. Verify.

   systemctl status sqlstudio-gateway
   cd /opt/sqlstudio-gateway && sudo -u sqlstudio env Gateway__DataDirectory=/var/lib/sqlstudio-gateway dotnet SqlStudio.Gateway.dll health; echo $?
   curl -k https://localhost:8443/healthz
   journalctl -u sqlstudio-gateway -n 50

   health returns 0 and curl prints Healthy. Print the fingerprint for your users with cert fingerprint.

Proxy and other environment variables

Add environment variables to the service with a drop-in file instead of editing the unit:

sudo systemctl edit sqlstudio-gateway

[Service]
Environment=HTTPS_PROXY=http://proxy.contoso.local:8080
Environment=NO_PROXY=localhost,127.0.0.1,.contoso.local

sudo systemctl restart sqlstudio-gateway

Management commands that contact the license server (license activate, license deactivate) need the proxy too: add HTTPS_PROXY=http://proxy.contoso.local:8080 after env in the command.

Logs go to the systemd journal: journalctl -u sqlstudio-gateway (add -f to follow).

7. Install with Docker

The image runs as the non-root user app, writes only to the /data volume and /tmp, listens on 8443 and has a built-in HEALTHCHECK (the health command, which queries https://127.0.0.1:8443/healthz). The ASP.NET Core runtime is included in the image.

1. Build the image.

   Extract the package, then from the extracted folder:

   docker build -t sqlstudio-gateway -f extras/Dockerfile .

2. Create the data volume.

   docker volume create sqlstudio-data

3. Activate the license, create the certificate and the first user (same volume, same non-root user as the service).

   docker run --rm -it -v sqlstudio-data:/data sqlstudio-gateway license activate SQLS-XXXXX-XXXXX-XXXXX-XXXXX
   docker run --rm -it -v sqlstudio-data:/data sqlstudio-gateway cert create --host gateway.contoso.local
   docker run --rm -it -v sqlstudio-data:/data sqlstudio-gateway users add admin --admin
   docker run --rm -it -v sqlstudio-data:/data sqlstudio-gateway users totp admin enable

   Behind a proxy add -e HTTPS_PROXY=http://proxy.contoso.local:8080 to the license commands. Instead of license activate you can pass the key to the container with -e Gateway__License__Key=SQLS-XXXXX-XXXXX-XXXXX-XXXXX in the next step.

4. Run the container.

   docker run -d --name sqlstudio-gateway --restart unless-stopped \
     --read-only --tmpfs /tmp --cap-drop ALL --security-opt no-new-privileges \
     -p 8443:8443 -v sqlstudio-data:/data -v /etc/machine-id:/etc/machine-id:ro \
     -e Gateway__AllowedServers__0=sql01.contoso.local \
     -e Gateway__AllowedServers__1=*.database.windows.net \
     sqlstudio-gateway

   The read-only /etc/machine-id mount binds the license to the Docker host, so updating or recreating the container keeps the same installation. Without it the container has no machine id and the installation is bound only to the /data volume.

   Configure the gateway with environment variables (syntax in section 11). For many settings, or for secrets such as a certificate password, put them in a file readable only by root and pass it with --env-file:

   # /etc/sqlstudio-gateway.env   (chmod 600)
   Gateway__AllowedServers__0=sql01.contoso.local
   Gateway__AllowedServers__1=*.database.windows.net
   Gateway__License__Key=SQLS-XXXXX-XXXXX-XXXXX-XXXXX
   HTTPS_PROXY=http://proxy.contoso.local:8080
   NO_PROXY=localhost,127.0.0.1,.contoso.local

   docker run -d --name sqlstudio-gateway --restart unless-stopped \
     --read-only --tmpfs /tmp --cap-drop ALL --security-opt no-new-privileges \
     -p 8443:8443 -v sqlstudio-data:/data -v /etc/machine-id:/etc/machine-id:ro --env-file /etc/sqlstudio-gateway.env \
     sqlstudio-gateway

5. Verify.

   docker inspect --format '{{.State.Health.Status}}' sqlstudio-gateway
   docker logs sqlstudio-gateway
   docker run --rm -v sqlstudio-data:/data sqlstudio-gateway cert fingerprint
   docker run --rm -v sqlstudio-data:/data sqlstudio-gateway license status

   The health status becomes healthy a few seconds after start. Open TCP 8443 on the host firewall for your users.

8. TLS certificate

The gateway accepts only HTTPS (TLS 1.2 or 1.3) and needs a certificate to start. It looks for one in this order:

1. Gateway:Https:Certificate:Path: a PFX/PKCS#12 file;
2. Gateway:Https:Certificate:Thumbprint: a certificate in the Windows certificate store;
3. <DataDirectory>/gateway.pfx: the certificate created by cert create.

Which certificate to use

Create a self-signed certificate

cert create --host gateway.contoso.local --host 10.0.0.15

Creates an ECDSA P-256 certificate valid 825 days with the given names in the Subject Alternative Name, saves it as <DataDirectory>/gateway.pfx with a random password protected in the data directory (the gateway loads it automatically), and prints the validity, the names and the SHA-256 fingerprint. Repeat --host for every DNS name or IP address users will type.

Use a corporate certificate

From a PFX file (any platform): set the path in appsettings.Production.json and the password only as an environment variable or secret store, never in the JSON file.

"Https": { "Port": 8443, "Certificate": { "Path": "C:\\ProgramData\\SqlStudioGateway\\certs\\gateway.pfx" } }

On Windows, set the password as a machine environment variable (elevated PowerShell), then restart the service:

[Environment]::SetEnvironmentVariable("Gateway__Https__Certificate__Password", "<pfx-password>", "Machine")
Restart-Service SqlStudioGateway

On Linux use Environment=Gateway__Https__Certificate__Password=… in a systemctl edit drop-in; in Docker mount the file read-only and use an --env-file:

-v /etc/sqlstudio/gateway.pfx:/certs/gateway.pfx:ro \
-e Gateway__Https__Certificate__Path=/certs/gateway.pfx \
--env-file /etc/sqlstudio-gateway.env

From the Windows certificate store: import the certificate with its private key into Local Computer › Personal, and set its thumbprint:

"Https": { "Port": 8443, "Certificate": { "Thumbprint": "8F3C…", "StoreName": "My", "StoreLocation": "LocalMachine" } }

The service account must be able to read the private key: in certlm.msc right-click the certificate › All Tasks › Manage Private Keys… and give SqlStudioGateway Read permission. Restart the service.

Fingerprint

cert fingerprint                 # the configured certificate
cert fingerprint gateway.cer     # any .cer/.crt/.pem/.pfx file

Prints the subject, the expiry date and the SHA-256 fingerprint of the DER certificate in two forms: hexadecimal (0e9c8e…) and with colons (0E:9C:8E:…). The app shows the colon form. For a PFX file the command asks for its password (empty if none), or reads it with --password-stdin.

Send the fingerprint to your users over a channel other than the gateway connection itself (for example your intranet or a signed email), so they can verify it independently.

Renewal plan

1. Track the expiry date. cert fingerprint shows it; add a reminder at least a month before.
2. Announce the change. Publish the new fingerprint to users before the switch.
3. Replace the certificate. Self-signed: run cert create again with the same --host names (it replaces gateway.pfx). Corporate: import the new certificate and update Thumbprint or Path.
4. Restart the gateway so it loads the new certificate, and run cert fingerprint.
5. Users confirm the new fingerprint in the app with Verify Certificate... in the sign-in window, or Options › General › Edit.... Until then the app refuses the new certificate.

9. Gateway users and two-step verification

Gateway users are separate from SQL Server logins: they only grant access to the gateway. The license sets how many accounts can sign in (5 included, more as extra users: see Users in section 4). After signing in, users still connect to each SQL Server with their own SQL or Entra credentials. Users are managed only from the command line on the server; there is no self-registration and no remote password reset.

Commands

Examples (Windows syntax; on Linux and Docker use the prefixes from section 4):

.\SqlStudio.Gateway.exe users add mario.rossi
.\SqlStudio.Gateway.exe users totp mario.rossi enable
.\SqlStudio.Gateway.exe users list

Scripted creation on Linux:

cd /opt/sqlstudio-gateway && printf '%s\n' "$PASSWORD" | sudo -u sqlstudio env Gateway__DataDirectory=/var/lib/sqlstudio-gateway dotnet SqlStudio.Gateway.dll users add ci-bot --password-stdin

You can run user commands while the service is running: the gateway rereads the user file automatically.

User names and passwords

• User names: 1 to 64 characters; letters, digits and . _ - @. Names are not case-sensitive.
• Passwords: at least 12 characters (maximum 256), must not contain the user name, at least 4 different characters.
• Passwords are stored as Argon2id hashes. Give each person their initial password through a secure channel and change it with users passwd when needed.

Roles

Every account has the user role; --admin adds the admin role. users list shows the roles. What a user can do inside SQL Server always depends on the SQL login or Entra identity they connect with.

Two-step verification (TOTP)

1. Enable it with users totp <name> enable. The command prints an otpauth:// URI and the secret key for manual entry. The secret is not shown again.
2. Hand the secret to the user securely. The user adds the account to any standard authenticator app (time-based codes, 6 digits, 30 seconds), either from the URI (or a QR code generated from it) or by typing the secret key.
3. The user signs in: after the correct password the app asks for the Verification code (TOTP).

Each code can be used only once. If a user loses the device, run users totp <name> enable again to issue a new secret (the old one stops working), or disable it. Changing the password, the TOTP setting or removing the user invalidates the user's existing sessions at the next request.

Lockout and sign-in limits

• After 5 failed attempts the account is locked for 15 minutes. The lock persists across restarts. Unlock it earlier with users unlock <name>.
• At most 5 sign-in attempts per minute for each IP address and user name, and 30 requests per minute per IP address to the anonymous sign-in endpoints. Excess attempts get HTTP 429.
• The error message is the same for a wrong password and an unknown user, and unknown names are "locked" too, so the gateway does not reveal which accounts exist.
• Every outcome (success, wrong password, unknown user, locked, limit exceeded, wrong code) is written to the audit log.

The thresholds can be changed with the Gateway:RateLimit:* settings (section 11).

Sessions

After sign-in the app receives a 15-minute access token and a refresh token that rotates at every renewal. A session stays alive while it is used within 12 hours (sliding) and lasts at most 7 days; then the user signs in again. A refresh token that is reused (a sign of theft) revokes the whole session.

10. Allowed servers

Every connection, and every Failover Partner, is checked against Gateway:AllowedServers before any socket is opened. Without this list the gateway would be a bridge into your internal network. With an empty list in Production every connection is refused (and the gateway logs a warning at startup).

Before matching, the server name is normalized: lowercase, tcp: prefix removed, . and (local) become localhost, the port follows the comma and the instance follows the backslash.

Pattern syntax

• * does not cross dots (so 10.0.0.* cannot accept 10.0.0.5.attacker.com); use ** for several domain levels.
• The np: (named pipes) and lpc: (shared memory) prefixes are always refused.
• Write each entry in the same form users type in the app. If users type sql01.contoso.local, the pattern sql01 does not match it.
• Matching by name trusts the gateway server's DNS. Use reliable internal DNS, or IP address patterns.

Examples

In appsettings.Production.json:

"AllowedServers": [ "sql01.contoso.local", "sql02.contoso.local,1433", "sqlcluster.contoso.local\\PROD", "*.database.windows.net", "10.20.30.*" ]

As environment variables (Docker, systemd):

Gateway__AllowedServers__0=sql01.contoso.local
Gateway__AllowedServers__1=sql02.contoso.local,1433
Gateway__AllowedServers__2=*.database.windows.net

Restart the gateway after changing the list. The app shows whether the allow-list is active in the gateway profile (Server allow-list).

11. Configuration reference

Where settings come from

The gateway reads its settings, in increasing order of priority, from:

1. appsettings.json (defaults, do not edit);
2. appsettings.Production.json (your settings, in the install folder);
3. environment variables;
4. the command line.

Values are validated at startup: an invalid value stops the gateway with a message that names it. The gateway runs in the Production environment (the systemd unit sets ASPNETCORE_ENVIRONMENT=Production explicitly). Do not set any other environment on a production server.

Environment variable syntax

Replace each : with a double underscore __. Array items take an index.

Gateway__Https__Port=9443
Gateway__AllowedServers__0=sql01.contoso.local
Gateway__AllowedServers__1=*.database.windows.net
Gateway__License__Key=SQLS-XXXXX-XXXXX-XXXXX-XXXXX
Gateway__Https__Certificate__Password=<pfx-password>

All settings

Other environment variables the gateway honors: ASPNETCORE_ENVIRONMENT (keep Production), and on Linux/Docker HTTPS_PROXY and NO_PROXY for the license check.

Example appsettings.Production.json

{
  "Gateway": {
    "DataDirectory": "C:\\ProgramData\\SqlStudioGateway",
    "AllowedServers": [
      "sql01.contoso.local",
      "sql02.contoso.local,1433",
      "sqlcluster.contoso.local\\PROD",
      "*.database.windows.net",
      "10.20.30.*"
    ],
    "AllowIntegratedSecurity": false,
    "MaxConnectionsPerUser": 25,
    "Https": {
      "Port": 8443,
      "Certificate": { "Thumbprint": "8F3C…", "StoreLocation": "LocalMachine" }
    },
    "RateLimit": { "LockoutThreshold": 5, "LockoutMinutes": 15 }
  }
}

Omit the Certificate block if you use the certificate created by cert create. Restart the gateway after every change to this file.

12. Connect the apps

Users install SQL Studio from the App Store: on Mac (macOS 26 or later), iPhone and iPad (iOS/iPadOS 26 or later). The app is free and needs no account with us. Make sure they install SQL Studio and not SQLStudio Light, which connects to servers directly without a gateway.

Give each user: the gateway URL (https://gateway.contoso.local:8443), the certificate fingerprint, their gateway user name and initial password, and their TOTP secret if enabled.

First connection

1. Configure Gateway. At first launch the Configure Gateway sheet opens. Enter a Profile name (for example "Company Gateway") and the Gateway URL, https://gateway.contoso.local:8443 (only https:// URLs are accepted). Press Test Connection. When the gateway is reached, the app shows its product, version, whether the server allow-list is active and the maximum upload size.
2. Verify the certificate. If the certificate is not trusted by the device (self-signed or from a private CA), the app shows its SHA-256 fingerprint in the form AA:BB:CC:…. Compare it, character by character, with the output of cert fingerprint on the gateway. Only if they match, turn on I have verified that the thumbprint matches the gateway's and press Trust and Continue. From then on the certificate is pinned and any other certificate is refused. If the certificate is trusted by the device, the app says so and offers Also pin this certificate (recommended for internal gateways).
3. Sign in. In Sign In to Gateway enter the gateway User name and Password. If two-step verification is enabled, the Verification code (TOTP) field appears. The refresh token is kept only in the device Keychain: the app signs in automatically on later launches while the session is valid, and asks again when it expires.
4. Connect to a server. Connect to Server opens. Enter the Server name as the gateway reaches it (sql01.contoso.local, sql01.contoso.local,1433 or sql01.contoso.local\PROD), choose the Authentication, enter the credentials and press Connect. The server must match an entry of the allow-list.

Users can configure several gateways (profiles) and switch between them with Manage Gateways... in the sign-in window or in Options › General. When the gateway certificate is renewed, they confirm the new fingerprint with Verify Certificate... in the sign-in window or Options › General › Edit....

Connect to Server options

• Server name: server, server,port, server\instance or admin:server (dedicated admin connection). Named instances without a port need SQL Server Browser (UDP 1434) reachable from the gateway. The combo box lists recent servers; <Search more...> picks from registered servers and recent connections.
• Encryption: Mandatory, Optional or Strict (SQL Server 2022 and Azure SQL); Trust server certificate; Host name in certificate.
• Options >>: Connection Properties (database, with <Browse for more...>; network protocol; packet size; connection and execution timeouts; application intent; multi-subnet failover; custom color), Always Encrypted (secure enclaves and attestation URL) and Additional connection parameters (key=value;).
• Additional connection parameters are limited to: Failover Partner, Load Balance Timeout, Max Pool Size, Min Pool Size, Pooling, Connect Retry Count, Connect Retry Interval, Server SPN, Failover Partner SPN, IP Address Preference, Current Language. Keywords such as AttachDBFilename, Integrated Security, Data Source or Password are refused. A Failover Partner must also be in the allow-list.
• Connection timeout: 1 to 600 seconds.

Authentication types

The gateway never stores SQL credentials: they stay in memory for the lifetime of the connection and are erased when the connection closes (by the user, after 120 minutes of inactivity, or at shutdown).

Security on the devices

• SQL passwords, gateway tokens and Microsoft Entra refresh tokens are stored only in the device Keychain (this device only, not synced to iCloud), never in preferences or logs. Exported registered servers never include passwords.
• Options › General › Security can require Touch ID, Face ID or the device password to use saved passwords and sessions, and lock the app automatically after a period of inactivity. Lock SQL Studio (⌃⌘L on Mac) locks it immediately.
• The app runs SQL nowhere locally: it has no SQL Server driver and talks only to the gateway.

13. Using SQL Studio

SQL Studio runs on Mac, iPhone and iPad, in English and Italian (following the system language).

Object Explorer

• Object Explorer (F8): progressive tree of all connected servers with over 200 node types (databases, tables, views, programmability, Query Store, Service Broker, storage, security, server objects, replication, Always On, management, Integration Services, SQL Server Agent, XEvent Profiler) and complete context menus.
• Toolbar: connect, disconnect, stop expansion, filter (name, schema, owner), refresh, collapse all and server-side object search. Drag an object into the editor to insert its name.
• Object Explorer Details (F7): sortable list of the children of the selected node. Registered Servers (⌃⌘G): nested server groups, import and export (JSON, without passwords).
• Commands: delete, rename, enable/disable, refresh, bring online/offline, view dependencies. Starting or stopping the SQL Server or SQL Server Agent service is not available.

Query editor

• T-SQL syntax colors, light and dark themes; IntelliSense for keywords, snippets, databases, schemas, tables, columns with aliases, procedures and parameters; real-time syntax check; T-SQL formatting; over 300 templates.
• Scripts with GO, GO n and SQLCMD mode (:setvar, :on error…); results in grid, text or file; multiple result sets; clickable XML and JSON; query options (SET, isolation, timeouts).
• A dedicated session per window with transactions, Commit / Rollback and database switching; cancel execution.
• Estimated and actual execution plans with a graphical viewer, Live Query Statistics and client statistics; open and save .sql and .sqlplan files.

The full list is in Help › Keyboard Shortcuts.

Scripting, data and design

• Script … as CREATE, ALTER, DROP, DROP and CREATE, CREATE OR ALTER, SELECT, INSERT, UPDATE, DELETE, EXECUTE, to a new window, the clipboard, a file or a SQL Server Agent job. The Generate Scripts wizard scripts objects and data to one file, one file per object, or text.
• Edit Top 200 Rows, the table designer (columns, keys, indexes, constraints, change scripts), database diagrams, Import Flat File and data import/export.

Administration dialogs

Over 130 dialogs and wizards, each with a Script button: server and database properties; backup, restore, copy and snapshots; attach, detach and shrink; logins, users, roles, schemas, keys, certificates, credentials and audits; tables, indexes and statistics; Always On availability groups; replication; SQL Server Agent jobs, schedules, alerts and operators; Resource Governor, Extended Events, Database Mail and maintenance plans; the Integration Services (SSIS) catalog; data-tier applications (extract, deploy, BACPAC export/import), which run on the gateway with Microsoft DacFx.

Backups are written to and restored from the file system of the SQL Server, which the dialogs browse with the SQL user's permissions; .bak files are not uploaded through the gateway. Local files such as BACPAC or CSV are uploaded to the gateway (up to Gateway:MaxUploadBytes) and deleted automatically.

Reports and monitoring

• 63 standard server and database reports (performance, activity, disk usage, transactions, backups, indexes, sessions).
• Query Store reports: regressed queries, overall resource consumption, top queries, wait statistics, tracked queries.
• Activity Monitor, Job Activity Monitor, Always On Dashboard, Log File Viewer, vulnerability assessment, data classification, missing indexes, live Extended Events and XEvent Profiler.

Long-running tasks

Backup, restore, DAC operations, Generate Scripts and imports run as gateway tasks. They continue on the gateway even if the user closes the app or the device sleeps. The Tasks button shows progress and messages and offers Cancel, Open in New Query (resulting script) and Save... (resulting file); a notification is sent when a task completes. The gateway runs up to Gateway:MaxConcurrentTasks tasks in parallel and keeps completed tasks and their files for Gateway:TaskRetentionHours. Only the user who started a task can see and download it.

14. Operations

Health check

• GET https://gateway.contoso.local:8443/healthz (anonymous) answers Healthy and nothing else. Point your monitoring system at it.
• The health management command queries https://127.0.0.1:<port>/healthz on the local machine and exits with 0 (healthy) or 1. The Docker image uses it as its HEALTHCHECK.
• GET /gateway/info (anonymous) returns the version, whether the allow-list is active, the maximum upload size and the enabled authentication types. Use it to check the version after an update: curl.exe -k https://localhost:8443/gateway/info.

Logs

Logs never contain passwords, tokens, connection strings or query text. Error responses follow RFC 7807 and include a traceId: ask users for it and search for it in the log to find the matching entry. License problems at startup and during the 6-hour checks are logged with the reason and the support link.

Audit log

Daily JSON Lines files in <DataDirectory>/audit/audit-YYYYMMDD.jsonl, append-only with private permissions. The same events are written to the application log (category SqlStudio.Gateway.Core.Security.FileAuditLog). One line per event:

{"timestamp":"2026-10-02T08:15:30.123Z","user":"<id>","userName":"mario.rossi","action":"connection.open","target":"sql01","detail":"SqlPassword; connectionId=…","success":true,"ip":"10.0.0.20","traceId":"…"}

The audit log never contains passwords, tokens, connection strings or SQL text. Forward it to your SIEM for retention and immutability: the local file can be modified by whoever controls the service account. auth.refresh.reuse indicates a possibly stolen refresh token and deserves an alert.

Backup and restore

Back up two things:

• the data directory (license, users, keys, certificate, audit log);
• appsettings.Production.json from the install folder.

The data directory contains keys: protect backups as secrets. Examples:

# Windows (elevated PowerShell): copy with ACLs
robocopy C:\ProgramData\SqlStudioGateway D:\Backup\SqlStudioGateway /MIR /COPYALL /R:1 /W:1
Copy-Item "C:\Program Files\SQLStudio Gateway\appsettings.Production.json" D:\Backup\

# Linux
sudo tar -C /var/lib -czpf /root/sqlstudio-gateway-data-$(date +%F).tar.gz sqlstudio-gateway
sudo cp /opt/sqlstudio-gateway/appsettings.Production.json /root/

# Docker
docker run --rm -v sqlstudio-data:/data:ro -v "$PWD":/backup alpine tar -czpf /backup/sqlstudio-data.tar.gz -C /data .

Restore to the same path, for the same service account. On Windows the key ring is also protected with DPAPI for the service account, so restored keys work only for that account on that server. If the keys cannot be decrypted, the gateway reports it at startup (see Troubleshooting). To move the gateway to a new server, follow License › Seats.

Updating the gateway

An update replaces the program files only. Keep appsettings.Production.json and the data directory; the package does not contain an appsettings.Production.json, so extracting it does not overwrite yours. Do not run the install script again.

Windows

1. Download the new SQLStudio-Gateway-<version>.zip and open PowerShell as Administrator in its folder.
2. Stop the service: Stop-Service SqlStudioGateway
3. Back up the data directory and appsettings.Production.json (see above).
4. Extract over the install folder:

   Unblock-File -Path .\SQLStudio-Gateway-*.zip
   Expand-Archive -Path .\SQLStudio-Gateway-*.zip -DestinationPath "C:\Program Files\SQLStudio Gateway" -Force

5. Compare the new appsettings.Production.example.json with your file for new settings.
6. Start and verify:

   Start-Service SqlStudioGateway
   Set-Location "C:\Program Files\SQLStudio Gateway"
   .\SqlStudio.Gateway.exe health; $LASTEXITCODE
   curl.exe -k https://localhost:8443/gateway/info

Linux

1. Stop: sudo systemctl stop sqlstudio-gateway
2. Back up the data directory and appsettings.Production.json.
3. Extract over the install folder:

   sudo unzip -o SQLStudio-Gateway-*.zip -d /opt/sqlstudio-gateway
   sudo chmod -R a+rX /opt/sqlstudio-gateway

4. Update the unit if it changed:

   sudo diff /opt/sqlstudio-gateway/extras/sqlstudio-gateway.service /etc/systemd/system/sqlstudio-gateway.service
   sudo cp /opt/sqlstudio-gateway/extras/sqlstudio-gateway.service /etc/systemd/system/ && sudo systemctl daemon-reload

5. Start and verify:

   sudo systemctl start sqlstudio-gateway
   curl -k https://localhost:8443/gateway/info
   journalctl -u sqlstudio-gateway -n 50

Docker

1. Build the new image from the new extracted package: docker build -t sqlstudio-gateway -f extras/Dockerfile .
2. Back up the volume (see above).
3. Replace the container, keeping the same volume and options:

   docker stop sqlstudio-gateway && docker rm sqlstudio-gateway
   docker run -d --name sqlstudio-gateway --restart unless-stopped \
     --read-only --tmpfs /tmp --cap-drop ALL --security-opt no-new-privileges \
     -p 8443:8443 -v sqlstudio-data:/data -v /etc/machine-id:/etc/machine-id:ro --env-file /etc/sqlstudio-gateway.env \
     sqlstudio-gateway

4. Verify: docker inspect --format '{{.State.Health.Status}}' sqlstudio-gateway

Security hardening

Built-in protections

Recommendations

1. Run the gateway on a dedicated, patched host with a least-privilege service account. It concentrates SQL credentials in memory while connections are open.
2. Limit exposure: publish port 8443 only to the VPN or the management network, not to the Internet.
3. Keep the allow-list as tight as possible and never set AllowAnyServer in production. Prefer reliable internal DNS or IP patterns.
4. Leave AllowIntegratedSecurity off unless you need it; if you enable it, give the gateway's account minimal rights on SQL Server.
5. Enable two-step verification for all users, especially admin accounts; remove accounts of people who leave.
6. Keep Mandatory or Strict encryption towards SQL Server and avoid Trust server certificate outside tests.
7. Renew the certificate before it expires and distribute the new fingerprint through a separate channel.
8. Protect the data directory and its backups (on Linux and Docker the keys are protected only by file system permissions).
9. Forward the audit log to a SIEM and alert on auth.refresh.reuse and repeated failed sign-ins.
10. Run one gateway instance per data directory.
11. Restrict outbound SMB from your SQL Servers at network level: a UNC path typed in a file dialog makes the SQL Server service authenticate to that share (as xp_dirtree would in a query window).
12. If you put a reverse proxy in front of the gateway, it must re-encrypt to HTTPS; list its address in ForwardedHeaders:KnownProxies and allow PUT and DELETE (or POST with X-HTTP-Method-Override).

15. Troubleshooting

Start with the log of your platform (section 14): the gateway logs the reason for every startup failure.

16. Getting help

• Support form: sqlstudio.geolox.it/store/support.html (sends your request to support@geolox.it).
• Email: support@geolox.it.

Please include:

• the email address used for the purchase (or the licensee name) and, for license questions, the output of license status (it contains the installation id);
• the gateway version (from /gateway/info) and the app version;
• the system the gateway runs on (Windows Server, Linux distribution, Docker) and the device and OS version of the app;
• the SQL Server version;
• the exact text of the error message, with its traceId if shown, and the related lines of the gateway log.

Do not send passwords, TOTP secrets, the PFX file or the data directory. We usually reply within two business days.