Skip to main content

Search

How can we help you?

How do I configure SQL Server Credentials?

This guide outlines the process of creating a user, granting the necessary permissions, and adding the credentials to the ThinkData Platform to connect to an MS SQLServer database.

Step-by-Step Guide:

Step 1: Create a User

To create a user in your SQL Server instance, follow these steps:

  1. Log in to the SQL Server instance using your preferred SQL client or workbench (e.g., DataGrip, Cloud SQL Studio).

  2. Create a new login with a secure password:

    CREATE LOGIN login_name WITH PASSWORD 'Password123';

     - Replace 'login_name' with the desired login name.

    Important: This 'login_name' will be used as the username when adding credentials to the ThinkData Platform (see Step 3).

             Password Recommendation:

    • Use a password that is 64 characters long, consisting of alphanumeric characters for maximum security.
    • Note: Avoid using special characters in the password, as they may cause connection issues.

  1. Create a new user within the target database:

    USE database_name;
    CREATE USER username FOR LOGIN login_name;

     - Replace 'username' with the desired username.

    Important: This 'user_name' is used only for granting permissions within SQL Server. You will use the 'login_name' for the ThinkData Platform credentials in Step 3. 

Step 2: Grant Permissions to the User

After creating the user, grant only the minimum necessary permissions listed below to enable secure access for the ThinkData Platform.

Basic Permissions

The following permissions are required to connect, list, and virtualize data assets from SQL Server in the ThinkData Platform.

  1. Grant SELECT permission on the required tables within the schema: 
    GRANT SELECT ON OBJECT::schema_name.table_name TO username;
    Replace `schema_name.table_name` with the specific table you want the user to access.
  2. (Optional) Grant SELECT permission on all tables in a schema: 
    GRANT SELECT ON SCHEMA::schema_name TO username;
    Replace 'schema_name' with the name of the schema you wish to provide access to. This grants access to all existing and future tables in the specified schema. 

Additional Permissions for Managing Data Products

If the user needs to create or manage data products from existing data assets in the ThinkData Platform, grant the following additional permissions. Alternatively, you may create a new user and assign these permissions if more precise access control is required.

  1. Grant the CREATE permission on the schema to allow object creation within the schema:

    GRANT ALTER ON SCHEMA::schema_name TO username; -- Allows modification of objects
    GRANT INSERT ON SCHEMA::schema_name TO username; -- Allows data insertion

Step 3: Add SQL Server Credentials to the ThinkData Platform

After creating the credentials in the SQL Server instance, add them to the ThinkData Platform to allow data access by following the steps below. This step only needs to be completed once as long as you continue to use the same 'user' these credentials are associated with.

Steps to add SQL Server credentials to the platform:

  1. Open the platform and navigate to the Credentials page:
    • Data > Credentials > Create Credential
  2. Enter the user name these credentials belong to (or any other identifying name) in the Credential Name field.
  3. In the Type dropdown, select SQL Server.
  4. Enter the  Username (the 'login_name' created in Step 1)  and Password for the SQL Server user created earlier.
  5. Click Save to store the credentials.

Step 4: Using the Credentials

Now, you can use these credentials with a:

 

Related to