Go To App

SQLServer

What is required to complete the onboarding ?

It's time to start the technical onboarding with Catalog. It's pretty simple; you need to grant access to your SQL Server metadata to Catalog and to perform a couple of tests.

You need certain qualifications to complete the onboarding. You should be

🧑‍💻 A super user with the ability to impersonate all schema owners

When new schemas are created, part of the process will need to be repeated

What will Catalog do with this access ?

🔒 Catalog will only be granted USAGE on all schemas and REFERENCE on all tables. It is the minimum role for Catalog to read your metadata without accessing your data.

Need some more info ?

🔑 Privileges and access documentation is here

💁 User creation and access to system table:

1. Whitelist Catalog's IP on SQL Server

To allow Catalog to connect to SQL Server, you will first need to whitelist Catalog's IP address:

Client whitelists Catalog's IP: 35.246.176.138

Catalog's SQL Server connector is based on sqlAlchemy. As such, it executes SQL queries directly in your SQL Server databases. So, in order for the connection to be successful, we need to be able to connect directly to you SQL Server database, as those requests are not encapsulated using the HTTP protocol. It is frequent that our clients provides Catalog an access to an exact replica of their database.

2. Create Catalog User on SQL Server

CREATE LOGIN <login_name> WITH PASSWORD = '<password>';
CREATE USER <username> FOR LOGIN <login_name>;
USE <database>;
GO
GRANT VIEW DEFINITION TO <username>;

3. Test the Catalog User

The Catalog User should now be correctly set up. Before you give Catalog the credentials, let's first run a test to make sure everything works well.

  1. 1.Connect to your SQL Server cluster using the catalog credentials you've just created

  2. 2.Run the following checks:

3.1 Check schemas the Catalog user can see

WITH ids AS (
    SELECT DISTINCT
        table_catalog,
        table_schema
    FROM information_schema.tables
    WHERE table_catalog = DB_NAME()
)
SELECT
    d.database_id,
    database_name = i.table_catalog,
    schema_name = s.name,
    s.schema_id,
    schema_owner = u.name,
    schema_owner_id = u.uid
FROM sys.schemas AS s
INNER JOIN ids AS i
    ON s.name = i.table_schema
LEFT JOIN sys.sysusers AS u
    ON s.principal_id = u.uid
LEFT JOIN sys.databases AS d
    ON i.table_catalog = d.name

4. Add new credentials in Catalog App

You must a Catalog admin to do it.

You can now enter the newly created credentials in the Catalog App here.

Go to "Settings > Integrations"

Click on "SQL Server Add"

Add the credentials using the following format:

{
    "port": 1234,
    "host": "host",
    "user": "user",
    "password": "*****",
    "database": "database"
}

Appendix

Queries run by Catalog

Information below concerns the queries Catalog will run on Sql Server with the rights granted above. We only extract metadata (columns, schemas, tables, roles).

  • Databases:

SELECT
    db.database_id,
    database_name = db.name
FROM sys.databases AS db
WHERE db.name = DB_NAME()

  • Schemas:

-- Fetch database information
WITH ids AS (
    SELECT DISTINCT
        table_catalog,
        table_schema
    FROM information_schema.tables
    WHERE table_catalog = DB_NAME()
)

SELECT
    d.database_id,
    database_name = i.table_catalog,
    schema_name = s.name,
    s.schema_id,
    schema_owner = u.name,
    schema_owner_id = u.uid
FROM sys.schemas AS s
INNER JOIN ids AS i
    ON s.name = i.table_schema
LEFT JOIN sys.sysusers AS u
    ON s.principal_id = u.uid
LEFT JOIN sys.databases AS d
    ON i.table_catalog = d.name

  • Tables:

/*
Select all types of tables:
- Views
- Base Tables
- External Tables
*/
WITH extended_tables AS (
    SELECT
        table_id = object_id,
        table_name = name,
        table_owner_id = principal_id,
        schema_id
    FROM
        sys.tables

    UNION

    SELECT
        table_id = object_id,
        table_name = name,
        table_owner_id = principal_id,
        schema_id
    FROM
        sys.views

    UNION

    SELECT
        table_id = object_id,
        table_name = name,
        table_owner_id = principal_id,
        schema_id
    FROM
        sys.external_tables
),
-- Get the row count per table
partitions AS (
    SELECT
        object_id,
        row_count = SUM(rows)
    FROM sys.partitions
    GROUP BY object_id
),
-- Append row count to table properties
extended_tables_with_row_count AS (
    SELECT
        et.*,
        row_count
    FROM
        extended_tables AS et
    LEFT JOIN partitions AS p
        ON et.table_id = p.object_id

),
-- Generate table identifiers and fetch table description
table_ids AS (
    SELECT
        table_id,
        table_name,
        schema_name = ss.name,
        schema_id = ss.schema_id,
        table_owner_id,
        table_owner = u.name,
        row_count,
        comment = CONVERT(varchar(1024), ep.value)
    FROM
        extended_tables_with_row_count AS et
    LEFT JOIN sys.schemas AS ss
        ON et.schema_id = ss.schema_id
    LEFT JOIN sys.sysusers AS u
        ON et.table_owner_id = u.uid
    LEFT JOIN sys.extended_properties AS ep
        ON (
            et.table_id = ep.major_id
            AND ep.minor_id = 0
            AND ep.name = 'MS_Description'
        )
),

meta AS (
    SELECT
        database_name = t.table_catalog,
        database_id = db.database_id,
        schema_name = t.table_schema,
        t.table_name,
        t.table_type
    FROM
        information_schema.tables AS t
    LEFT JOIN sys.databases AS db
        ON t.table_catalog = db.name
    WHERE t.table_catalog = db_name()
)

SELECT
    m.database_name,
    m.database_id,
    m.schema_name,
    i.schema_id,
    m.table_name,
    i.table_id,
    m.table_type,
    i.table_owner,
    i.table_owner_id,
    i.comment,
    tuples = i.row_count
FROM
    meta AS m
LEFT JOIN table_ids AS i
    ON (m.table_name = i.table_name AND m.schema_name = i.schema_name)

  • Columns:

*
Select all types of tables:
- Views
- Base Tables
- External Tables
*/
WITH extended_tables AS (
    SELECT
        table_id = object_id,
        table_name = name,
        table_owner_id = principal_id,
        schema_id
    FROM
        sys.tables

    UNION

    SELECT
        table_id = object_id,
        table_name = name,
        table_owner_id = principal_id,
        schema_id
    FROM
        sys.views

    UNION

    SELECT
        table_id = object_id,
        table_name = name,
        table_owner_id = principal_id,
        schema_id
    FROM
        sys.external_tables
),

-- Create the column identifiers
column_ids AS (
    SELECT
        sd.database_id,
        database_name = sd.name,
        column_id = sc.column_id,
        column_name = sc.name,
        table_id,
        table_name,
        schema_name = ss.name,
        schema_id = ss.schema_id,
        comment = CONVERT(varchar(1024), ep.value)
    FROM sys.columns AS sc
    LEFT JOIN extended_tables AS et ON sc.object_id = et.table_id
    LEFT JOIN sys.schemas AS ss ON et.schema_id = ss.schema_id
    LEFT JOIN sys.databases AS sd ON sd.name = DB_NAME()
    LEFT JOIN sys.extended_properties AS ep
        ON
            sc.object_id = ep.major_id
            AND sc.column_id = ep.minor_id
            AND ep.name = 'MS_Description'
),

columns AS (
    SELECT
        i.database_name,
        i.database_id,
        schema_name = c.table_schema,
        i.schema_id,
        table_name = c.table_name,
        i.table_id,
        c.column_name,
        c.data_type,
        c.ordinal_position,
        c.column_default,
        c.is_nullable,
        c.character_maximum_length,
        c.character_octet_length,
        c.numeric_precision,
        c.numeric_precision_radix,
        c.numeric_scale,
        c.datetime_precision,
        i.comment,
        column_id = CONCAT(i.table_id, '.', c.column_name)
    FROM
        information_schema.columns AS c
    LEFT JOIN column_ids AS i
        ON
            (
                c.table_name = i.table_name
                AND c.table_schema = i.schema_name
                AND c.column_name = i.column_name
            )
)

SELECT * FROM columns

  • Users:

SELECT
    user_name = u.name,
    user_id = u.principal_id
FROM sys.database_principals AS u
PreviousS3NextSynapse

Last updated

Was this helpful?