MS SQL
Connecting MS SQL to LightBeam
Last updated
Connecting MS SQL to LightBeam
Last updated
LightBeam Spectra users can connect various data sources to the LightBeam application and these data sources will be continuously monitored for PII, and PHI data.
Example: MS SQL, Snowflake, SMB, MySQL, etc.
Login to your LightBeam Instance.
Click on DATASOURCES on the Top Navigation Bar.
Click on “Add a data source”.
Search for “MS SQL”.
Click on MS SQL.
Fill in the details as shown below and click Next:
In the Basic Details section, enter the following information:
Instance Name: Provide a unique name for the MSSQL data source (e.g., MSSQL_Datasource).
Primary Owner: Enter the email address of the individual responsible for this data source.
Source of Truth (Optional): Toggle this option on if the data source acts as the definitive source for validating other data sources.
Description (Optional): Add a brief description of the data source (e.g., "MS-SQL Datasource Instance").
In the Connection section, provide the following details:
Username: The MSSQL user account name (e.g., sql_admin).
Password: The password for the specified username.
Host: The hostname or IP address of the MSSQL server.
Port (Optional): Specify the port number for MSSQL (default: 1433).
Microsoft Entra Authentication: Enable this option if using Entra (Azure AD) authentication.
Default Database: Specify the name of the database to connect to (e.g., AdventureWorks).
Encryption:
Select Encrypted if the connection requires SSL. When selected, an option to Upload SSL Certificate will appear.
SSL Certificate (Optional): Upload the SSL certificate file to establish a secure connection. (This option is displayed only when Encrypted is selected.
Select Unencrypted if SSL is not required.
Click on Test Connection.
Verify that you get the message “Connection Success!” on the screen. Click on Next.
On the next screen, verify that the test database created for the PoC is showing up in the list of databases.
Now we are ready to connect to the test database and proceed.
If you notice that data is not being scanned properly, and you encounter errors, it could be due to a permission problem. Follow these steps to troubleshoot the issue:
Run a SELECT * query on a table in your database to attempt to view the data.
If you receive a "permission denied" error message, it indicates that the user account you are using to connect to the database lacks the necessary permissions to access the data.
Troubleshooting Named Instances:
If you are getting errors in test connection when onboarding datasource, consider doing following:
Check Machine's firewall settings.
Make sure that the TCP/IP corresponding to the instance in SSCM is enabled.
Make sure SQL Browser service is running on the server.
Basic Authentication: LightBeam supports local database users with a username and password for connecting to the database.
Microsoft Entra Authentication: LightBeam supports Microsoft Entra ID (also known as Microsoft Active Directory) users with a username and password for connecting to Azure SQL databases (Only Azure SQL DBs are supported, On-prem Active Directory or on-prem SQL server is not supported). When registering the data source with Microsoft Entra authentication, tick the checkboxes for Microsoft Entra authentication and Encrypted.
To configure minimal permissions for scanning a subset of databases on the instance, the following permissions are needed:
Connect Permissions:
Allow users to connect to the database instance.
Database-Level Permissions:
For each database in the subset, grant:
CONNECT permission: Allows users to connect to the specific database.
SELECT permission: Enables users to read data from the tables within the database.
Use the following script to create a user with such permissions. In this example, we are creating a user with permissions to connect to the customerdb database.
When registering the Microsoft SQL data source, use the login you created (not the user) to provide the connection details
If you want to scan all the databases and can allow a wider scope of permissions, we recommend creating a full read-only user who can access a list of databases in the SYS database, connect to every database, and read data.
Open SQL Server Management Studio (SSMS) and connect to the SQL Server instance that needs to be onboarded with LightBeam.
Right-click on the server instance in the Object Explorer and select "Properties" from the context menu.
In the Server Properties window, navigate to the "Security" page.
Ensure that the "SQL Server and Windows Authentication mode" option is selected under "Server authentication". This enables SQL Server authentication.
Expand the "Security" folder in the Object Explorer and right-click on the "Logins" subfolder. Select "New Login" from the context menu.
In the "New Login" window, switch to the "General" page.
Enter a login name and provide a strong password in the required fields.
Switch to the "User Mapping" page in the "New Login" window.
In the "Users mapped to this login" section, select the databases that need to be scanned by LightBeam.
Check the "db_datareader" role in the "Database role membership" list. This grants the user read-only access to the selected databases.
Click on the "OK" button to create the new login with the specified permissions.
The SQL server instance is ready to be onboarded with LightBeam with the credentials of the newly created login.
Next, the user needs to validate these permissions to the database. This ensures authorized access to the database by the credentials provided by the user. After validating the permissions to the database, the user can configure LightBeam Spectra on the system.
Prerequisites
The following tools need to be installed on the system in order to verify database permissions:
Git
sqlcmd command-line tool
Go into the sql_user_check_sql_server/ directory
Run the following command:
The command will prompt for the password of the database instance.
* HOSTNAME: Hostname or IP address of your instance.
* USERNAME: Username for the database instance.
* PORT NUMBER: Port number of your database instance.
* DATABASE TO CONNECT: Name of the database to which you wish to establish a connection and validate the permissions.
To validate whether the commands were successful, check the output of the file generated from the command. The output file should contain a few rows of data in a JSON format. By following these steps, you can validate the permissions granted to the user account, ensuring that LightBeam Spectra has the necessary access to scan and process data from your databases.
LightBeam automates Privacy, Security, and AI Governance, so businesses can accelerate their growth in new markets. Leveraging generative AI, LightBeam has rapidly gained customers’ trust by pioneering a unique privacy-centric and automation-first approach to security. Unlike siloed solutions, LightBeam ties together sensitive data cataloging, control, and compliance across structured and unstructured data applications providing 360-visibility, redaction, self-service DSRs, and automated ROPA reporting ensuring ultimate protection against ransomware and accidental exposures while meeting data privacy obligations efficiently. LightBeam is on a mission to create a secure privacy-first world helping customers automate compliance against a patchwork of existing and emerging regulations.
Named instances:
Host field would be <Hostname/IP of the machine>\<Instance name>.For example, if hostname is sqlserver.com and INST1 is the name of an instance that needs to be onboarded, then put sqlserver.com\INST1in Host field. Keep Port field empty in this case.
First, clone the repository:
For any questions or suggestions, please get in touch with us at: .
