🛡️ Intro

In this example, we'll secure access to a Microsoft SQL Database server using Border0.
We'll walk through the following steps together.

1. Launch a sqlserver (Docker)
2. Create a database socket and link it to our connector
3. Authenticate and Connect
4. watch the recording
5. Using the Border0 Desktop app to access your database
6. Troubleshooting

1 Run a local Microsoft SQL server with docker

You can make any Microsoft SQL database server available with Border0; for testing purposes, we'll use a Microsoft SQL server Docker container as the server.

👍

It works through NAT and Firewalls

Don't worry if your laptop is behind NAT. Border0 works perfectly fine with resources that are behind NAT.

docker run --name msql1 --rm -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=My-secret-pw" -d -p 1433:1433 mcr.microsoft.com/mssql/server:2022-latest

On MacOS, you may need to run it like this, so that the right platform type is used:

docker run --platform linux/amd64 --name msql1 --rm -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=My-secret-pw" -p 1433:1433 mcr.microsoft.com/mssql/server:2022-latest

This will start a local SQL server listening on port 1433, with My-secret-pw as the password.
Before we continue, let's just make sure if it came up ok and test connecting to the database locally as user SA and password My-secret-pw
sqlcmd -S 127.0.0.1,1433 -U SA -P My-secret-pw

Once connected, you can do a query like the one below. If this works, you're all good to continue. Your local MySQL instance is working.

sqlcmd -S 127.0.0.1,1433 -U SA -P My-secret-pw
1> SELECT session_id, net_transport  FROM sys.dm_exec_connections
2> GO
session_id  net_transport
----------- ----------------------------------------
         51 TCP

(1 row affected)
1>

2 - Create a database socket and link it to your connector.

Now that we have a working database for our example it's time to create a database socket and link it to our connector.

• In the Sockets page, click on Add New Socket and select Microsoft SQL Server
• set a name and optionally a description

Upstream Configuration
here we can configure how your connector can connect and authenticate to the upstream database server.

• Select standard for Service type
• For hostname and port we'll use localhost and port 1433. Adjust as needed for your environment.
• For authentication select SQL Server Authentication.
• Credentials, in our example we used SA and My-secret-pw Adjust as needed for your environment.
• Connector: Select your connector from the list

That's it, now click create new database socket button. And you should have access to your SQL Server database.

Now connect your newly created socket to the Border0 Platform. For example by clicking on the blue connect button, which will redirect you to our web based database client.

📘

Securing your credentials

In the preceding example, we configured Border0 with credentials to access an upstream database. These credentials are essential for the connector running in your environment to establish a secure connection. While Border0 stores these credentials in encrypted form, we strongly recommend that you keep them local to the connector.
You can have the Border0 connector integrated with your secret management solution for enhanced security and keep your secrets local. Learn more about this in our guide:

https://docs.border0.com/docs/using-secret-managers-to-store-credentials

Authentication methods

We support the following upstream authentication methods:

• Sql Authentication
• Kerberos
• Azure Active Directory Username and Password
• Azure Active Directory Integrated

The default is SQL authentication, if you specify kerberos the format for username depends on the OS the connector (or socket connect) runs. On Windows use "DOMAIN\USER", on linux or macOS use "[email protected]". To enable kerberos authentication use the --kerberos flag or select Kerberos in as the authentication method.

To use Azure AD authentication select "Azure" as the service type (or use the --azure_ad flag).

Troubleshooting

Should you see any issues with your new socket, it's typically because it can't connect to the upstream database. To troubleshoot that take a look at the connector logs as described here

3 - Connecting to Your MSSQL Database

You can connect to your MSSQL database using three convenient methods: the Border0 desktop app, our agentless client portal, or the command-line interface (CLI).

For the fastest and most seamless experience, use our desktop app. It simplifies the process of discovering and connecting to your MSSQL databases with your preferred database client..

Using the Command-Line Interface (CLI)

For advanced users or those who prefer the CLI, you can use the border0 CLI tool

1. List available databases: Use the border0 client db command to list all databases you have access to
2. Select your MSSQL database
3. Select your preferred client: The CLI will display a list of supported SQL Server clients, including:
   • sqlcmd
   • dbeaver
   • datagrip
   • TablePlus
   • SQL Server Management Studio (Windows only)
4. Connect: Choose your preferred client from the list, and the CLI will launch it and establish the connection.

border0 client db

If your favorite client is not listed, you can always connect with your client of choice, simply use the socket name as the server name.

4 - Review Session Details and Recordings

To review your MSSQL session, open the Border0 portal and select the corresponding database socket. Go to the "Sessions" tab and find your session. Clicking on the session will display a recording of the activity. This recording details the SSO identity used (who connected), the originating IP address, Device, connection time, and a complete log of all executed SQL queries.

Troubleshooting

Should you see any issues with your new socket, it's typically because it can't connect to the upstream database. To troubleshoot that take a look at the connector logs as described here

If that doesn't help resolve the issue, feel free to contact us, and we'd be happy to help you troubleshoot.