SQL Server Integration with Edilitics

SQL Server is a powerful relational database management system developed by Microsoft. It is widely used for storing, managing, and retrieving data for various applications. SQL Server allows users to create databases, define tables with structures, and write queries to access and manipulate information efficiently. Its scalability and robust security features make it a popular choice for businesses of all sizes. To learn more about SQL Server, visit the official SQL Server website.

Within the Edilitics platform, SQL Server can be used both as a source and destination for data, making it a versatile tool for data analysis workflows. This enables you to leverage SQL Server’s strengths for data storage and retrieval within your Edilitics environment.

This step-by-step guide equips you with the knowledge to configure your SQL Server database and integrate it with Edilitics for data analysis.

Before You Begin#

Ensure you have the following:

• MS SQL Server instance is up and running.

• SQL Server version 12 or higher for compatibility with Edilitics.

• TCP/IP protocol enabled on your MS SQL Server, typically using port 1433 for communication.

• For pipelines using Change Tracking mode, the database user requires:

  • VIEW CHANGE TRACKING

  • ALTER DATABASE

• The database user must always possess SELECT privileges.

Setting Up SQL Server for Edilitics Integration#

Step 1: Set Up SQL Server (Optional)#

If you already have a functioning SQL Server instance, skip this step. This section outlines installing SQL Server on Windows Server 2019.

• Download and Install SQL Server#

  • Visit the Microsoft Evaluation Center and navigate to the SQL Server downloads section.
  
  
  • Download the 64-bit SQL Server 2019 Evaluation Edition.
  
  

  • Run the downloaded installer.

  • Choose Basic Installation and follow the on-screen instructions.

Step 2: Enable Remote Access#

To allow Edilitics to connect to your SQL Server instance:

• Enable TCP/IP Protocol#

  • Open SQL Server Configuration Manager.

  • Navigate to SQL Server Network Configuration > Protocols for MSSQLSERVER.

  
  
  • Right-click TCP/IP and select Enable.
  
  
  • Verify the IP Addresses tab to ensure the TCP/IP port is set to 1433.
  
  

• Create Firewall Rules#

  • To allow incoming connections to port 1433:

  • Open PowerShell as an administrator.

  • Run the following command:

  
  

  New-NetFirewallRule -DisplayName "SQLServer Default Instance" -Direction Inbound -LocalPort 1433 -Protocol TCP -Action Allow

  
  

• If additional firewall rules are needed, consult your network administrator.

• Create a Login User#

  • Open SQL Server Management Studio (SSMS) and connect using Windows Authentication.

  • Enable SQL Server and Windows Authentication mode.

  
  
  • Right-click your server name, select Properties > Security.
  
  
  • Enable SQL Server and Windows Authentication mode.
  
  
  • Restart the SQL Server service.
  
  
  • Create a new SQL Server login user with appropriate privileges:
  
  
  
  

  CREATE LOGIN [<master_user>] WITH PASSWORD=N'<strong_password>', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON;

  GO

  ALTER SERVER ROLE [sysadmin] ADD MEMBER [<master_user>];

  GO

  
  
  
  

Step 3: Configure the Database and User#

• Create a New Database (Optional)#

  • Open SSMS.
  
  
  • Right-click Databases > New Database.
  
  
  • Enter a Database Name and click OK.
  
  

• Create a Schema (Optional)#

  • To organize tables, create a schema within your database:
  
  

  USE [<your_database_name>];

  GO

  CREATE SCHEMA [<schema_name>];

  GO

  
  

• Create a Database User and Grant Privileges#

  • Run the following SQL commands to create a database user and assign privileges:
  
  

  CREATE LOGIN [<login_name>] WITH PASSWORD=N'<strong_password>', DEFAULT_DATABASE=[<database_name>];

  GO

  CREATE USER [<username>] FOR LOGIN [<login_name>];

  GO

  GRANT ALTER, SELECT, INSERT, UPDATE ON SCHEMA::<schema_name> TO <username>;

  GO

  GRANT CREATE TABLE, INSERT, SELECT, UPDATE ON DATABASE::<database_name> TO <username>;

  GO

  
  

Connecting SQL Server to Edilitics#

Step 4: Add the SQL Server Connector in Edilitics#

• Log in to Edilitics and navigate to the Integrations module.

• Click New Integration.

• Choose the SQL Server connector from the list or use the search bar.

Step 5: Configure the SQL Server Connection#

Enter the following connection details:

Using a Connection String#

Alternatively, connect using the connection string:

Step 6: Test the Connection#

• Once you've entered your credentials, click the Test Connection button. Edilitics will attempt to connect to your SQL Server database. You'll see a confirmation message if the connection is successful. If there's a problem, Edilitics will provide details to help you troubleshoot.

Step 7: Verify & Finalize Integration#

• Preview (Optional): Retrieve a sample of tables and data.

• Update (Optional): Modify connection details if needed.

• Save: Finalize by encrypting and storing connection details securely.