Warehouses

Connect SQL Server

Connect a SQL Server database, on Azure SQL, Amazon RDS or your own server, with a SQL login that can only read.

On this page

The SQL Server connector signs in to one database with a SQL login. It works with Azure SQL Database, Azure SQL Managed Instance, SQL Server on Amazon RDS, and your company's own SQL Server, on your premises or hosted, by its public name or address.

Availability. The SQL Server connector must be on for your workspace. See Connect a data warehouse.

To connect Azure SQL as a Microsoft Entra service principal instead of a SQL login, see Connect Azure SQL.

Make a login that can only read

In the database, as its owner, make a user and add it to db_datareader only. Use your own names. On Azure SQL Database, a contained user needs no server login.

CREATE USER ahamo_reader WITH PASSWORD = '<a long generated password>';
ALTER ROLE db_datareader ADD MEMBER ahamo_reader;

On a Managed Instance or RDS, make the login in master first, then the user for it.

CREATE LOGIN ahamo_reader WITH PASSWORD = '<a long generated password>';
-- then, in the database
CREATE USER ahamo_reader FOR LOGIN ahamo_reader;
ALTER ROLE db_datareader ADD MEMBER ahamo_reader;

db_datareader reads every table and view and changes nothing. Grant nothing else. To keep a table out, use DENY SELECT ON <schema or table> TO ahamo_reader.

When you connect, Ahamo checks that the login can't write, and refuses one that can.

Open the firewall

Ahamo reaches your server from one fixed address. Allow it in the server's firewall, on the server's port, 1433 unless it uses another. The connect dialog shows the address.

The server must be reachable from the internet. Ahamo never connects to a private address, so servers reachable only on a private network are not supported.

The server's certificate

The connection is always encrypted, and Ahamo always checks that the server's certificate is valid for the name you give. It never skips the check.

  • Azure SQL and RDS have certificates a public authority signed, and need nothing more.
  • If your company's own certificate authority signed the server's certificate, paste that authority's certificate, as PEM, when you connect. You can paste up to three.

Connect

  1. Open Connectors, find SQL Server and choose Connect.
  2. Enter the server's name or public IPv4 address, the port and the database name.
  3. Paste your company's CA certificate if your own authority signed the server's certificate. Leave it empty otherwise.
  4. Paste the login and its password as login:password, with no spaces. Ahamo keeps it encrypted and never shows it again.
  5. Name the connection, choose who may use it, and choose Test and connect.

Limits

A statement may run for 2 minutes, then Ahamo stops it. The session runs with read-only intent, so a server with a readable secondary uses it. Its application name is Ahamo person and the id of the person who asked, which the server's sessions and audit show.

Troubleshooting

SQL Server didn't accept the login for this database. Paste the login and password as login:password, and check that the login has a user in this database.

This login can change data, so Ahamo doesn't connect with it. Use a login that is only a member of db_datareader, with no db_datawriter, db_owner, EXECUTE or other grants that write.

Ahamo couldn't reach the server. Check the server name and port, and allow Ahamo's fixed address in the server's firewall.

The server's certificate isn't valid for its name. Use the name the certificate was issued for. If your company's own authority signed it, connect again and paste that authority's certificate as PEM.

The server's name leads to a private address, so Ahamo doesn't connect to it. Give the server's public name or address, and allow Ahamo's fixed address in its firewall.

SQL Server no longer accepts this connection's login. If the password changed, open the connection, choose Paste a new key and paste the new login:password.

On this page