MSSQL

Getting Started with MSSQL Destination Configuration

Requirements:

• Active MSSQL database instance.

• Allow connections from DataBrain to your MSSQL database.

  • For details on setting up IP whitelisting and ensuring secure connectivity, refer to our guide on

Allow Access to our IP

• Choose the DataBrain Workspace to which you wish to connect the data.

Setup Guide:

1. Ensure Database Accessibility:

   • Ensure your MSSQL database is active and accessible from the machine running DataBrain.

   • Accessibility is dependent on your MSSQL user privileges and network settings. The easiest way to verify if DataBrain can connect to your MSSQL database is via the check connection tool in the UI. For detailed setup and permissions, refer to the MSSQL documentation.

2. Grant Necessary Permissions:

   • Read Access on Tables and information_schema: Grant read access permissions to the tables within MSSQL as well as the information_schema schema. This allows DataBrain to retrieve necessary information and replicate data accurately. You can assign appropriate permissions to the user or role accessing MSSQL to enable reading from tables and accessing metadata stored in the information_schema.

   -- Grant SELECT permission on all tables in the specified schema GRANT SELECT ON [schema_name].[table_name] TO [username]

3. Fill Up Connection Info:

   • Provide the necessary information to connect to your MSSQL database:

     • Name: [Pick a name to help you identify this destination in DataBrain]

     • Server: [Enter your MSSQL account locator. (e.g., mssql-server.database.windows.net)]

     • Port: 1433 (This is the default port for MSSQL databases)

     • Database Name: [Name of the database]

     • User: [Enter the name of the user you want to use to access the database]

     • Password: [Enter the password associated with the username]

Encryption:

• Ensure you have SSL/TLS set up for your MSSQL database if you require encrypted connections from DataBrain for enhanced security.

Permissions:

• Permission to read information_schema.

• Whitelist the IP address.

• Grant read access to the schema (usage) and tables, noting that access to only tables may not suffice in certain databases.

Locating the Configuration Details in MSSQL

1. Name:

   • This is a custom name you decide for identification within Databrain. Choose a name that is relevant to your MSSQL setup.

2. Server:

   • For remote servers, it would be the IP address or domain name of the machine where MSSQL is running.

   • If you have access to the MSSQL shell, you can also use the command SELECT SERVERPROPERTY('MachineName') to retrieve the server name.

3. Port:

   • The default port for MSSQL is 1433. However, this can be customized. If you're in the MSSQL shell, you can use the command SELECT SERVERPROPERTY('Port') to check the port MSSQL is running on.

4. Username:

   • The username is the one you use to log in to the MSSQL shell. If you're logged in, you can use the command SELECT SYSTEM_USER to display the current user.

5. Password:

   • The password is associated with your MSSQL username. MSSQL does not provide a direct method to view passwords due to security reasons. If you've forgotten the password, you'll need to reset it or consult your database administrator.

Replace the placeholders inside the square brackets with actual values when filling in the details. This guide will help you configure MSSQL as a destination in Databrain, ensuring smooth data integration and management.