All Collections
Connecting your Database or Data Warehouse
How to integrate with Microsoft SQL server
How to integrate with Microsoft SQL server

Everything you need to know to bring your existing data from Microsoft SQL server to Locale.

Rishabh Jain avatar
Written by Rishabh Jain
Updated over a week ago

Microsoft SQL Server is a relational database management system developed by Microsoft.

Prerequisites

  • The Microsoft SQL Server is running.

  • The SQL Server database host’s IP/DNS and port are available. You can obtain these details from your SQL Server administrator if you do not have them.

  • The necessary privileges are granted on the database to your database user

Steps

  • Step 1: Whitelist Locale’s IP Addresses

  • Step 2: Create a User and Grant Privileges

  • Step 3: Retrieve the Hostname and Port Number (Optional)

  • Step 4: Configure Microsoft SQL server as a Data Connection on Locale.

Creating a Database User and Granting Privileges

1. Create a database user

If you want to use an existing user with Locale, you can skip these steps, However, It is recommended to create a new user for the locale with only the necessary permissions.

Perform the following steps to create the login and database users in your MS SQL database:


1. Log in to your MS SQL Server instance

2. Access the database and the database schema where you want to add the user

USE <database_name>;

3. Enter the following command to create a login user in the selected database schema:

CREATE LOGIN <login_user> WITH PASSWORD = '<strong_password>', DEFAULT_DATABASE = <database_name>;

4. Create a database user for the login user:

CREATE USER '<username>' FOR LOGIN <login_user>;

2. Grant privileges to the user

Perform the following steps to grant the required privileges to the database user, to read the database objects and schema:

1. Log in to your MS SQL Server instance as the login user created in this section using an SQL client tool.

2. Use the following SQL queries to grant privileges to the database user:

GRANT SELECT ON SCHEMA::<schema_name> TO <username>; 

GRANT SELECT ON DATABASE::<database_name> to <username>;

Configure Microsoft SQL Server as a Data Connection

Perform the following steps to configure SQL Server as a data connection:

1. Go to Add Data connections and select Microsoft SQL Server

2. Fill in the basic details we configured in the previous steps

💡 Note: User Login user configured in the previous step database user file

3. Click on Test Connection and wait for it to be authenticated

4. Once the connection is tested, you can then click on Save Connection and start using it to create your alerts.

Note : Locale will cancel queries that run for more than 90 seconds. This is to protect your database from running rogue queries and also to prevent a backlog of alerts in the Locale system. If you have a use case where this needs to be increased then please get in touch with our Support.

Happy Alerting 🙌🏻


Blazing-fast operations minus all the constant firefighting👨‍🚒

Learn how to set up a control tower for your operations in under 15 minutes ️‍🔥

...with Locale for modern ops teams


Did this answer your question?