SQL Server

Covers SQL Server 2019 or later on your own servers, Azure SQL Database, Azure SQL Managed Instance and Amazon RDS for SQL Server. Tested on SQL Server 2022 and Azure SQL Database.

Before you start

• SQL Server 2019 or later. The gateway computes tokens inside SQL Server with a UTF-8 collation, which 2019 introduced.
• An administrator who can create a login and a database user.
• The server’s host name on the gateway’s allowed hosts (Allow the host).

Network access

The gateway connects to the server’s port, 1433 by default, over TLS. Open that port to the gateway as described in Network access. The certificate and host name are always checked. If the server’s certificate comes from your company’s CA, paste that CA in setup.

Create a read-only login

Run this as an administrator. Replace sales with the database Portward will read, dbo with the schema its tables are in, and <password> with a strong password.

USE master;
CREATE LOGIN portward_reader WITH PASSWORD = '<password>';
USE sales;
CREATE USER portward_reader FOR LOGIN portward_reader WITH DEFAULT_SCHEMA = dbo;
GRANT SELECT ON SCHEMA::dbo TO portward_reader;

Portward reads the tables and views in the user’s default schema. Don’t add the user to db_owner, and don’t grant INSERT, UPDATE, DELETE or ALTER. Portward can’t expose a table the login could change.

Hosted versions

Azure SQL Database

Azure SQL Database has no USE. Connect to the database Portward will read (not master) as the server admin, and create a contained user there:

CREATE USER portward_reader WITH PASSWORD = '<password>', DEFAULT_SCHEMA = dbo;
GRANT SELECT ON SCHEMA::dbo TO portward_reader;

• The server admin itself is refused, because it owns the database.
• You don’t need to paste a CA. Azure’s certificate comes from a public CA.
• Under Networking, allow the gateway’s address in the server’s firewall rules, or use a private endpoint the gateway can reach.

Azure SQL Managed Instance

Use the SQL in Create a read-only login. From outside the instance’s virtual network, connect to its public endpoint, <instance>.public.<dns-zone>.database.windows.net, on port 3342. Turn the public endpoint on in the instance’s Networking page first. From inside the network, use port 1433. Add that host to the allowed hosts.

Amazon RDS for SQL Server

• Run the SQL in Create a read-only login as the master user. The master user itself is refused, because it owns the databases it created.
• In setup, use the instance endpoint and add that exact name to the allowed hosts.
• You don’t need to paste a CA. The gateway checks the certificate against Amazon’s RDS CA bundle.
• Allow port 1433 from the gateway in the instance’s security group.

Connect it in setup

Choose SQL Server and fill in Name (the name people and the AI see, such as sales), Host, Port (empty means 1433; 3342 for a Managed Instance’s public endpoint), Database, User (portward_reader) and Password.

What the setup check tests

Before it saves, setup connects with the login and checks, in this order:

1. The host is on the allowed hosts (“isn’t in the hosts the gateway operator allowed for setup”). An RDS name allowed only by a wildcard gets “is on a domain other customers share”.
2. It can connect over TLS with the certificate checked. Each failure has its own message: “the server didn’t answer; check the host, port and its firewall”, “the server refused the login; check the user, password and database”, or “the server’s TLS certificate couldn’t be verified”.
3. The login is not sysadmin, not a member of db_owner, and doesn’t hold CONTROL SERVER (“connect with a read-only login, not sysadmin or db_owner”).
4. A token computed inside SQL Server matches the gateway’s own (“in-database tokens don’t match; SQL Server 2019+ is required”).
5. It lists the tables and views in the user’s default schema that it can read. A table the login could INSERT, UPDATE, DELETE or ALTER is listed as one it could change, and can’t be exposed.

Each time the gateway starts, it runs the same checks and refuses a login that can change a table it exposes (“use a read-only login”).