How to Connect to a Data Source

Goal

By the end of this page, you will have a saved SQL Server connector in AnalyticsCreator and a successful connection test.

When to use this how-to

Use this page when you have a SQL Server database and you need AnalyticsCreator to connect to it.

This page only covers the connector setup. Metadata import is covered in the next how-to.

Before you start

Prepare the following SQL Server connection details:

• SQL Server name, instance name, or IP address
• Database name
• Authentication method
• User name and password, if SQL Server Authentication is used
• Read access to the source database
• SQL Server Management Studio, if you want to test the connection outside AnalyticsCreator first

Step 1: Test the connection in SQL Server Management Studio

Before creating the connector in AnalyticsCreator, test the same SQL Server, database, and authentication method in SQL Server Management Studio.

After connecting in SSMS, run a simple check against the source database:

SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
  AND TABLE_TYPE = 'BASE TABLE';

Replace dbo with the schema you want to check.

If SSMS cannot connect, resolve the SQL Server access issue before configuring AnalyticsCreator.

Step 2: Open Sources > Connectors

In AnalyticsCreator, open the project where the SQL Server connector should be created.

Go to Sources > Connectors.

Step 3: Add a new connector

Add a new connector from the Connectors area.

AnalyticsCreator opens the connector page, where you define the connector name, connector type, connection settings, and test the connection.

Step 4: Select the MSSQL connector type

Select MSSQL as the connector type.

Use this connector type for Microsoft SQL Server or a compatible SQL Server endpoint.

Step 5: Enter the connector name

Enter a connector name that clearly identifies the SQL Server source.

Examples:

IBroker_SQLServer
ERP_SQLServer
Northwind_SQLServer

Use a name that will still be understandable later when the project contains multiple connectors.

Step 6: Enter the SQL Server connection settings

Enter the SQL Server connection settings required by your environment.

Use the same server, database, and authentication information that worked in SSMS.

Step 7: Use an encrypted string for sensitive connection values

If the connection string contains sensitive values, create an encrypted string instead of storing the value as plain text.

Go to Options > Encrypted Strings > Add, add the protected value, and then reference it from the connector settings.

Step 8: Test the connector

Use Test Connection to validate the connector settings.

If the test succeeds, AnalyticsCreator can establish the SQL Server connection with the current settings.

Step 9: Save the connector

Save the connector after the connection test succeeds.

The SQL Server connector is now available for source-related workflows in AnalyticsCreator.

Expected result

At this point, you should have:

• A saved SQL Server connector in AnalyticsCreator
• The MSSQL connector type selected
• Connection settings entered for the SQL Server source
• A successful connector test

Troubleshooting

The connection fails in SSMS and AnalyticsCreator

If the connection fails in both SSMS and AnalyticsCreator, fix the SQL Server access issue first.

• Confirm that the SQL Server name, instance name, or IP address is correct.
• Confirm that the database name is correct.
• Confirm that the SQL Server service is running.
• Confirm that the user name and password are correct.
• Confirm that the user has permission to connect to the database.

SSMS works, but AnalyticsCreator fails

If SSMS connects successfully but AnalyticsCreator does not, compare the settings used in both tools.

• Use the same server or instance name.
• Use the same database name.
• Use the same authentication method.
• Check whether the connector references the correct encrypted string.
• Check whether the connection settings include the required encryption or certificate options for your SQL Server environment.

The remote SQL Server cannot be reached

If the SQL Server is remote, check network access between the AnalyticsCreator machine and the SQL Server machine.

• Confirm that the SQL Server host is reachable from the AnalyticsCreator machine.
• Check whether a firewall blocks the SQL Server connection.
• For a default SQL Server instance, check the configured TCP port.
• For a named SQL Server instance, confirm whether the instance uses a fixed or dynamic port.
• Restart the SQL Server service after changing TCP/IP or port settings.

The connector saves, but the next metadata import fails

If the connector saves successfully but metadata import fails in the next step, check SQL Server permissions before changing the connector.

• Verify that the expected tables are visible in SSMS.
• Confirm that the selected database is the correct source database.
• Confirm that the SQL Server user can read the required schemas.
• Confirm that the SQL Server user can read the required tables and views.

Do not continue if

• The connector test fails.
• The same connection fails in SSMS.
• You are unsure which SQL Server database should be used as the source.
• The SQL Server user cannot see the required source tables in SSMS.

Next step

After creating and testing the SQL Server connector, continue with the next how-to: How to import SQL Server metadata into AnalyticsCreator.