Skip to main content

Documentation index: llms.txt. This page is also available as markdown: append .md to this URL or send Accept: text/markdown.

SQL Server

Extract metadata from your SQL Server databases using the Coalesce extraction script.

Prerequisites​

Before you begin, complete the following:

Install Castor Extractor​

Follow the castor-extractor installation instructions on PyPI.

Create a User​

Create a dedicated user to extract your metadata.

To create a SQL Server login and database user, you must connect using an account with one of these permission sets:

  • sysadmin
  • securityadmin at the server level and db_owner on each target database

Permissions Required by the Catalog User​

The Catalog integration requires read-only access to SQL Server metadata. The Catalog user must not have write or administrative permissions.

Catalog never reads table contents. Every query it runs targets the sys.* catalog views and INFORMATION_SCHEMA, so the user needs no permission on your data.

Server-Level Permissions​

Grant the following permission to allow the Catalog user to discover databases through sys.databases:

GRANT VIEW ANY DATABASE TO <login_name>;

Database-Level Permissions​

Repeat the following steps for each database Catalog should extract metadata from.

First, switch to the target database:

USE <database_name>;
GO

Then grant the required permissions:

  • CONNECT: Allows connection to the database.
  • VIEW DEFINITION: Enables reading schemas, tables, columns, views, and view definitions.
GRANT CONNECT TO <user_name>;
GRANT VIEW DEFINITION TO <user_name>;
VIEW DEFINITION fails silently when missing

Extraction still succeeds without it: views are listed, but their definitions come back empty and the lineage Catalog derives from them is lost, with no error raised.

Query Store Access for Query Lineage​

If Query Store extraction is enabled, grant the following permission to allow access to sys.query_store_* system views:

GRANT VIEW DATABASE STATE TO <user_name>;

Schema-Level Grants for Restricted Environments​

If your environment grants permissions per schema, grant VIEW DEFINITION on every schema Catalog should extract, not only dbo:

GRANT VIEW DEFINITION ON SCHEMA::dbo TO <user_name>;
-- Repeat for other schemas as needed

Example: Create Catalog Login and User​

-- Create login
CREATE LOGIN catalog_login WITH PASSWORD = 'StrongPasswordHere!';
GO

-- Create user in a target database
USE <database_name>;
GO
CREATE USER catalog_user FOR LOGIN catalog_login;
GO

-- Grant required permissions
GRANT CONNECT TO catalog_user;
GRANT VIEW DEFINITION TO catalog_user;
GRANT VIEW DATABASE STATE TO catalog_user; -- optional

Run the Extraction Script​

After installing the package, run the following command in your terminal:

castor-extract-sqlserver [arguments]

The script displays logs as it runs:

INFO - Extracting `DATABASE` ...
INFO - Extracting database: query 1/1
INFO - Results stored to /tmp/sqlserver/1706004977-database.csv
...
INFO - Extracting `USER` ...
INFO - Extracting user: query 1/1
INFO - Results stored to /tmp/sqlserver/1706004977-user.csv
INFO - Wrote output file: /tmp/sqlserver/1706004977-summary.json

Arguments​

Credentials​

Use these arguments to authenticate with your SQL Server instance:

  • -H, --host: MSSQL host address.
  • -P, --port: MSSQL port number.
  • -u, --user: MSSQL username.
  • -p, --password: MSSQL password.

Output​

  • -o, --output: Target folder to store the extracted files.

Optional Arguments​

  • --skip-existing: Skip files already extracted instead of replacing them.
  • --db-allowed: List of databases that should be extracted.
  • --db-blocked: List of databases that should not be extracted.

The values for --db-allowed and --db-blocked are case-sensitive. Make sure to use the exact values present in column sys.databases.name for the respective databases.

Default Database​

  • --default-db: Default database to use when connecting to SQL Server.

This argument is optional and usually not required. The connector automatically discovers and extracts all databases the user has access to.

It becomes necessary in some environments, notably Azure SQL or restricted SQL Server setups, where the user doesn't have access to the master database. In those cases, SQL Server may fail during connection with the following error:

The server principal "user" is not able to access the database "master" under the current security context. (DB-Lib error message 20018)

When this happens, SQL Server attempts to connect to master by default. Setting --default-db forces the connector to connect to a database the user can access, preventing the error.

Use Environment Variables​

If you don't want to specify arguments every time, set the following environment variables in your .bashrc:

export CASTOR_SQLSERVER_HOST=127.0.0.0
export CASTOR_SQLSERVER_PORT=3306
export CASTOR_SQLSERVER_USER=extraction_user
export CASTOR_SQLSERVER_PASSWORD=******
export CASTOR_SQLSERVER_DEFAULT_DB=database # optional
export CASTOR_OUTPUT_DIRECTORY="/tmp/catalog"

Then run the script without any arguments:

castor-extract-sqlserver

You can also run the script with partial arguments. The script looks in your environment variables as a fallback:

castor-extract-sqlserver --output /tmp/catalog