Microsoft SQL

Lightup account setup

Lightup supports connections to MS SQL versions 2012, 2014, 2017, and 2019. You'll need a user account that can query data and metadata for all tables and views you want to monitor. You might already have a user set up that will work, or you might need to create one. We recommend you use a role to provide sufficient privileges to the Lightup user.

Create a Lightup user

-- Create lightup account on the server. 
--Replace <password> with actual password.
CREATE LOGIN lightup WITH PASSWORD = '<password>';

-- Grant lightup user rights via built-in role, in each database you want to monitor. 
-- Replace <database> with the database name.
USE <database>;
CREATE USER lightup FOR LOGIN lightup;
ALTER ROLE db_datareader ADD MEMBER lightup;

Connect to a Microsoft SQL datasource

  1. In the left pane, open a workspace menu and select Datasources.
  2. In the main page select Create Datasource +.
  1. Enter a Datasource Name, then for Connector Type select Microsoft SQL.
  2. Under Configure connector, provide the following inputs:
  • Host Name - The hostname for the datasource (check the browser address bar; you want the string between // and /. Example: https://host.na.me/
  • Database Name - The name of the database you're connecting to. Each datasource can only connect to one database. If you need to connect to more than one, you'll need to create a datasource for each.
  • Username - Enter lightup.
  • Password - The lightup account's password.
  1. After entering the required settings and any optional settings that apply, below the Configure connector section select Test Connection.
  2. After a successful connection test, select Save.
  3. Your new datasource appears in the list of available datasources. By default, these are listed in alphabetical order, so you might have to scroll or change the sort order to see your new datasource.

Advanced/Schema scan frequency

You can adjust how often scans run for a datasource.

  • In section 3 - Advanced, select a value for Schema scan frequency: Hourly, Daily, or Weekly.

Query Governance

Microsoft-SQL datasources support the Query History, Scheduling, Enable data storage, Maximum backfill duration, and Maximum distinct values settings. For steps, see Set query governance settings for a datasource.

Date/time data types

These MS-SQL date/time data types are supported:

Object types

These MS-SQL object types are supported: