Create a read-only database user for the Agent

Connector setup · Every database

Maak een alleen-lezen database user for the Agent

The Agent only ever reads. Give it a login that can only read, so nothing anyone does in Query Streams can change your data. One section per database, with the exact commands to copy.

Ongeveer 10 minuten SQL Server, PostgreSQL, MySQL en meer Read only

Wat je uiteindelijk overhoudt

A new database login, called qs_reader in the examples below, with a password you choose. It can sign in and read the tables you allow, and nothing else. You type that name and password into the Gebruikersnaam en Wachtwoord boxes when you add the connector in Data Agents.

Why a separate read-only login

The Agent already refuses to run anything that is not a read. Every query it is asked to run, whether it comes from the Portal, Excel, Google Sheets, an AI assistant or the REST API, is checked on your computer before it reaches the database. Anything that starts with something other than SELECT (or a few other read-only words such as MET, SHOW en UITLEGGEN), or that contains INVOEGEN, UPDATE, VERWIJDEREN, DRUPPEL, WIJZIGEN, STUDIEBEURS and similar words, is turned away with the message Only SELECT queries are permitted in read-only mode.

A read-only login is the second lock. That check is a word filter on our side; a read-only login is enforced by your database itself. With both in place, even a mistake on our side could not change a row. It also keeps the powerful logins (sa, postgres, root, your WordPress or hosting account) out of Query Streams entirely.

Wat je nodig hebt

Pick your database

• Microsoft SQL Server (including SQL Express)
• PostgreSQL
• MySQL and MariaDB (including cPanel and WordPress)
• Oracle, Sneeuwvlok of Google BigQuery

In every command below, replace qs_reader if you want a different name, replace ChangeMe-2026! with your own password, and replace Verkoop with the name of your own database.

Microsoft SQL Server

The Portal’s SQL Server connector signs in with a SQL Server login (a name and a password), not with your Windows account. Many SQL Server and SQL Express installs only accept Windows accounts until you switch one setting, so the first two steps check that. Then you create the login, either by typing or by clicking.

1. Open SQL Server Management Studio

   On the computer that runs SQL Server (or any computer that has SQL Server Management Studio, SSMS for short), click Start, type SSMS and open it. A box titled Connect to Server appears. Leave Authenticatie op Windows-verificatie en klik Verbinden.

   A panel called Object Explorer fills the left side, with your server’s name at the top and folders such as Databases en Beveiliging under it. If instead you see an error that mentions a certificate chain of not trusted, go back to the Connect box, tick Trust Server Certificateen klik Verbinden again.

2. Turn on SQL Server logins (the Windows-only trap)

   In Object Explorer, right-click the server name at the very top and choose Eigenschappen. In the list on the left of the window that opens, click Beveiliging. Onder Server authentication there are two choices.

   Als SQL Server and Windows Authentication mode is already selected, click Cancel and go to step 3. If Windows Authentication mode is selected, click SQL Server and Windows Authentication mode, dan OK. A message says the change takes effect after SQL Server restarts; click OK. Then right-click the server name again and choose Restarten klik Ja when asked. The icon by the server name turns grey and back to green in under a minute.

   Why this matters: while the server is on Windows-only, the Agent’s test fails with Connection failed: Login failed for user ‘qs_reader’. That is exactly the message you would get for a wrong password, so people retype the password again and again. If you are sure of the password, this setting is the reason.

3. Open a query window

   Klik op de New Query button on the toolbar near the top. A blank white page opens in the middle. That is where you paste. (Prefer clicking to typing? Skip to the click-through version below.)

4. Paste the commands and run them

   Copy the block below, paste it into the query window, change the password and the database name (Verkoop appears twice), then click Uitvoeren on the toolbar or press F5:

   Paste into the query window, then press F5

   CREATE LOGIN qs_reader WITH PASSWORD = 'ChangeMe-2026!', CHECK_EXPIRATION = OFF;
   GO
   USE Sales;
   GO
   CREATE USER qs_reader FOR LOGIN qs_reader;
   ALTER ROLE db_datareader ADD MEMBER qs_reader;
   GO

   De Messages tab at the bottom says Commands completed successfully. If it says the password does not meet the operating system policy, pick a longer password with a mix of letters, numbers and a symbol, and run it again. If it says Database ‘Sales’ does not exist, expand Databases in Object Explorer to see the real name and change both Verkoop lines.

   Want the Agent to read more than one database? Run the last three lines again for each one, with its name in place of Verkoop.

The click-through version. Instead of steps 3 and 4: in Object Explorer, expand Beveiliging, right-click Logins, kiezen New Login…. On the General page type qs_reader in Login name, kiezen SQL Server authentication, type the password in Wachtwoord en Confirm password, en untick User must change password at next login (the Agent cannot change a password, so with this ticked it never gets in). Then click User Mapping on the left. Under Users mapped to this login, tick the box beside your database. In the lower box, Database role membership for, tick db_datareader and leave publiek ticked. Click OK. The new name appears under Security → Logins.

Agent on a different computer from SQL Server? Haven 1433 (or the instance’s own port, plus UDP 1434 for a named instance such as SQLEXPRESS) has to be open on the SQL Server computer: open a firewall port on Windows.

PostgreSQL

Three parts: create the login, then make sure PostgreSQL accepts connections from the Agent’s computer, which it does not out of the box. If the Agent runs on the same computer as PostgreSQL and you will type localhost as the server, you only need part one.

1. Open a query window as the postgres user

   With pgAdmin (installed alongside PostgreSQL on Windows): open pgAdmin, expand Servers on the left, enter the postgres password you chose when PostgreSQL was installed, expand Databases, click your database once, then choose Tools → Query Tool. A blank editor opens.

   On a Linux server, type this and press Enter; the prompt changes to sales=#:

   Type this, then press Enter

   sudo -u postgres psql -d sales

2. Create the login and give it read access

   Paste the block below, change the password and verkoop, and run it (in pgAdmin press F5; in psql press Enter). If your tables are not in the publiek schema, change publiek too, or repeat the last three lines for each schema.

   Paste, then run

   CREATE ROLE qs_reader LOGIN PASSWORD 'ChangeMe-2026!';
   GRANT CONNECT ON DATABASE sales TO qs_reader;
   GRANT USAGE ON SCHEMA public TO qs_reader;
   GRANT SELECT ON ALL TABLES IN SCHEMA public TO qs_reader;
   ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO qs_reader;

   You see CREATE ROLE, dan STUDIEBEURS three times and ALTER DEFAULT PRIVILEGES. The last line makes tables created later readable too, but only tables created by the user who ran it; if your application creates tables as its own user, run that line while signed in as that user. If you see database "sales" does not exist, use the name exactly as pgAdmin lists it; if it contains capital letters, put it in double quotation marks, like "Sales".

3. Find PostgreSQL’s two settings files

   Still in the query window, run these two lines one at a time:

   Run each, note the answer

   SHOW config_file;
   SHOW hba_file;

   Each answers with a full path. On Windows they are usually in C:\Program Files\PostgreSQL\17\data\ (the number is your version); on Ubuntu or Debian, /etc/postgresql/17/main/. The first file is postgresql.conf, the second pg_hba.conf. Write both paths down.

4. Let PostgreSQL listen to the network

   Open postgresql.conf in a text editor. On Windows: click Start, type Notepad, right-click it and choose Run as administrator, dan File → Open, paste the path, and change the file type box at the bottom right to All Files. On Linux: sudo nano followed by the path.

   Find the line that starts listen_addresses (Notepad: press Ctrl en F together). If it reads listen_addresses = '*', leave it. If it reads 'localhost' or starts with a #, replace the whole line with:

   The line in postgresql.conf

   listen_addresses = '*'

   Save the file. Nothing changes yet; this setting is only read when PostgreSQL restarts (step 6).

5. Allow the Agent’s computer in pg_hba.conf

   First find the Agent computer’s address: on that computer click Start, type cmd, open Command Prompt, type ipconfig en druk Enter. The line IPv4 Address shows something like 192.168.1.25.

   Open pg_hba.conf the same way as the last file. At the bottom are lines starting gastheer. Look at the last word on those lines, usually scram-sha-256 (older servers say md5), and add this line at the very end, using that same last word and the Agent’s address with /32 after it:

   Add at the end of pg_hba.conf

   host    all    qs_reader    192.168.1.25/32    scram-sha-256

   Save the file. To allow any computer on that office network instead of one, write 192.168.1.0/24.

6. Restart PostgreSQL

   Windows: klik Start, type Diensten, open de Diensten app, find the entry whose name starts with postgresql-x64-, right-click it and choose Restart. Its Status column blinks and shows Running again. Linux: type the line below; it prints nothing when it works.

   Linux: type this, then press Enter

   sudo systemctl restart postgresql

   If PostgreSQL does not come back (Status stays blank, or the command prints failed), a typo crept into one of the two files. Open them again, compare with the lines above, save, and restart again. Port 5432 also has to be open in the server’s firewall: open a firewall port on Windows.

The Use SSL tick box. The Portal’s PostgreSQL connector has Use SSL ticked by default, and with it ticked the Agent insists on an encrypted connection. A PostgreSQL installed on Windows usually has encryption switched off, so the test fails with Connection failed: SSL connection requested. No SSL enabled connection from this host is configured. That message means: untick Use SSL and test again. Leave it ticked for any database in the cloud.

What a missing pg_hba.conf line looks like today. You might expect a clear refusal. Instead, the Portal shows an amber Signed in met No database opened yet. Name it on the next step. and the Agent’s message Login succeeded, but no database could be opened (tried postgres, template1). Enter the database name. If you then type your database name, it says This login can’t open database ‘sales’. Despite the words “Login succeeded”, this is almost always pg_hba.conf turning the Agent away: go back to step 5. We are fixing the message so it says so.

MySQL and MariaDB

MySQL and MariaDB use the same commands. One thing surprises everybody: a MySQL user belongs to a place. 'qs_reader'@'localhost' can only sign in from the database computer itself; the same name and password from the Agent’s computer is refused. The part after the @ has to match where the Agent is.

1. Open a query window as an administrator

   MySQL Workbench: open it, click your server under MySQL Connections, enter the root password; a query tab opens. On a Linux server: type sudo mysql en druk Enter; the prompt changes to mysql> of MariaDB [(none)]>.

2. Create the user and allow SELECT only

   Paste the block below, change the password, the database name (shop) and the part after the @ if you chose something other than '%' (it appears twice), and run it (Workbench: the lightning-bolt button; command line: Enter):

   Paste, then run

   CREATE USER 'qs_reader'@'%' IDENTIFIED BY 'ChangeMe-2026!';
   GRANT SELECT ON shop.* TO 'qs_reader'@'%';
   FLUSH PRIVILEGES;

   Each line answers Query OK. To let it read every database on the server, write *.* in plaats van shop.*. If it says Operation CREATE USER failed, a user of that name and place already exists: pick another name, or ask whoever set it up.

3. Let the server listen to the network

   On Ubuntu and Debian, MySQL and MariaDB only listen to the database computer itself until you change bind-address. Open the settings file (sudo nano and the path):

   MySQL on Ubuntu/Debian

   /etc/mysql/mysql.conf.d/mysqld.cnf

   MariaDB on Ubuntu/Debian

   /etc/mysql/mariadb.conf.d/50-server.cnf

   MySQL on Windows

   mijn.ini in C:\ProgramData\MySQL\MySQL Server 8.0\ (ProgramData is hidden: paste the path into File Explorer’s address bar)

   Find the line bind-address = 127.0.0.1. Change it to the line below and save. On Windows there is usually no such line, which means it already listens; leave the file alone.

   The line in the settings file

   bind-address = 0.0.0.0

4. Restart the database

   Linux: type sudo systemctl restart mysql (of sudo systemctl restart mariadb) and press Enter; it prints nothing when it works. Windows: in de Diensten app right-click MySQL80 (of MariaDB) and choose Restart. Port 3306 also has to be open in the server’s firewall: open a firewall port on Windows.

The Use SSL tick box and the RSA message. The Portal’s MySQL and MariaDB connectors have Use SSL ticked by default. A MariaDB without encryption set up fails with Connection failed: Server does not support SSL: untick Use SSL and test again. On MySQL 8 or later, keep it ticked. MySQL creates its own certificate, and unticking it leads to Connection failed: Authentication method 'caching_sha2_password' failed. Either use a secure connection, specify the server's RSA public key with ServerRSAPublicKeyFile, or set AllowPublicKeyRetrieval=True. The Agent cannot do the last two today, so the answer is to tick Use SSL again. (Switching the user to the older mysql_native_password method also works on MySQL 8.0, but MySQL 8.4 switches that method off by default and MySQL 9 removed it, so we do not recommend it.)

The cPanel route (most web hosts). In cPanel the Databases section has two pages you need. Older cPanel calls them MySQL Databases en Remote MySQL; cPanel 120 and later calls them Manage My Databases en Remote Database Access.

1. Create the user

   Open Manage My Databases (of MySQL Databases). Under Een gebruiker toevoegen, type qs_reader in Gebruikersnaam, type the password in both password boxes, and click Create User. cPanel puts your account name in front, so the real username becomes something like myaccount_qs_reader. Write that full name down; it is what goes in the Portal.

2. Give it SELECT on your database, nothing else

   Onder Add User To Database, pick the new user and your database in the two menus and click Toevoegen. A page of privilege tick boxes opens (MySQL Account Maintenance). Do niet tick ALL PRIVILEGES. Tick only SELECTKlik vervolgens Make Changes.

3. Allow the Agent’s address

   On the Agent’s computer, open a browser and search for what is my IP; note the address it shows (this is the office’s public address, not the 192.168 one). In cPanel open Remote Database Access (of Remote MySQL), type that address in Gastheer, add a Comment zoals Query Streams Agenten klik Add Host. If you do not see this page at all, your host has switched it off; ask them, or install the Agent on the hosting server if they allow it.

WordPress sites. The database name, server and login are in the site’s wp-config.php file, on the lines DB_NAME, DB_HOST, DB_USER en DB_PASSWORD. Gebruik DB_NAME en DB_HOST from there, but do not give the Agent that DB_USER: it is the account WordPress itself writes with. Make the separate read-only user above instead.

Oracle

Run this as a DBA (for example SYSTEM) in SQL Developer or SQL*Plus, connected to the pluggable database that holds your tables (on Oracle XE that is XEPDB1, on Oracle Free FREEPDB1). Replace SALES.ORDERS with each table the Agent should read, one GRANT line per table.

CREATE USER qs_reader IDENTIFIED BY "ChangeMe-2026!";
GRANT CREATE SESSION TO qs_reader;
GRANT SELECT ON SALES.ORDERS TO qs_reader;

Each line answers User created of Grant succeeded. In the Portal, type the owner of the tables (here SALES, in capitals) in Standaardschema. Left blank, it uses QS_READER, which owns no tables, and the test says Schema `QS_READER` doesn’t exist or this login can’t read any of its tables. Granting every table at once (READ ANY TABLE) covers every schema in the database, including system ones; ask your DBA before using it.

Sneeuwvlok

In Snowsight, open a new SQL worksheet as a user with the SECURITYADMIN of ACCOUNTADMIN role and run the block below. Replace COMPUTE_WH, SALES en QS_USER (the Snowflake user the Agent signs in as) with your own names.

CREATE ROLE QS_READER;
GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE QS_READER;
GRANT USAGE ON DATABASE SALES TO ROLE QS_READER;
GRANT USAGE ON ALL SCHEMAS IN DATABASE SALES TO ROLE QS_READER;
GRANT SELECT ON ALL TABLES IN DATABASE SALES TO ROLE QS_READER;
GRANT SELECT ON FUTURE TABLES IN DATABASE SALES TO ROLE QS_READER;
GRANT ROLE QS_READER TO USER QS_USER;

Each line answers Statement executed successfully. In the Portal, type QS_READER in Rol en COMPUTE_WH in Magazijn. If a warehouse or role is missing a grant, the test says Invalid Warehouse of Invalid Role. The FUTURE line needs SECURITYADMIN or higher, and if a schema has its own future grants, Snowflake ignores this one for that schema.

Google BigQuery

BigQuery has no SQL logins. The Agent signs in as a Google Cloud service account, and two roles make it read-only:

BigQuery-gegevensviewer

Reads tables and views. Give it on the project, or only on the datasets you want to share.

BigQuery Job User

Lets it run queries. Give it on the project. Without it the test says This service account can’t run queries in project …

In the Google Cloud console go to IAM & Admin → IAM, find the service account (or click Grant access and paste its email address), and add both roles. Do not add BigQuery Data Editor, BigQuery Admin, Editor of Owner; any of those can change data. You then paste the account’s JSON key into Service Account JSON-sleutel in the Portal.

What the message means

After you click Testverbinding in Data Agents, the message under Connection failed (or under an amber Signed in) is the Agent’s own words. Test connection runs on the Agent’s computer, so it needs the Agent online first: Is the Agent online? Then find your message:

Next: add the connection

Open Data Agents, klik Connector toevoegen on your Agent, pick your database, and type the new user’s name and password into Gebruikersnaam en Wachtwoord. Then follow the guide for where you want the data:

• Microsoft SQL Server to Excel
• PostgreSQL naar Excel
• MySQL naar Excel · MariaDB naar Excel
• Oracle naar Excel · Snowflake naar Excel · BigQuery naar Excel

Gerelateerde handleidingen

• Installeer de agent op Windows.
• Is the Agent online?
• Open a firewall port on Windows
• Stel de Microsoft Access-connector in.
• Alle handleidingen