SQL Server

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

PrivilegePurpose
ALTERAllows creating, altering, and dropping schema objects.
SELECTAllows selecting data from tables.
INSERTAllows inserting data into tables.
UPDATEAllows updating existing records in tables.
CREATE TABLEAllows creating new tables (useful if creating a dedicated database).

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:

Field NameDetails
Integration TitleGive your integration a unique name to identify it in Edilitics.
Integration DescriptionBrief description of the integration purpose.
AI Driven Column InsightsEnable this feature to leverage AI for generating metadata-driven column descriptions automatically. If disabled, column descriptions can be manually curated to align with specific business requirements. Any user-modified descriptions are treated as authoritative and remain immutable.
Database NameThe specific database name in your SQL Server instance.
HostThe hostname or IP address of your SQL Server instance.
PortThe port number (1433).
UsernameThe user account with appropriate privileges.
PasswordThe corresponding password for the user account.

Using a Connection String

Alternatively, connect using the connection string:


sqlserver://<username>:<password>@<host>:<port>/<database_name>


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):

    Confirm accuracy by retrieving a sample of tables and data.

  • Update (Optional):

    Modify connection details if the preview deviates from expectations.

  • Save: Finalize by encrypting and storing connection details within Edilitics.

Enterprise Support & Technical Assistance

For technical inquiries, implementation support, or enterprise-level assistance, our dedicated technical support team is available to ensure optimal deployment and utilization of Edilitics solutions. Please contact our enterprise support desk at support@edilitics.com. Our team of specialists will respond promptly to address your requirements.