Using Fabric SQL Database as Storage for Prepare Instance

Note: This article is intended for Azure database administrators. Ensure the following tasks are completed by someone with the appropriate Azure database administration privileges.

Jet Analytics Data Integration can use Microsoft Fabric SQL Database as storage for Prepare instances, providing a fully managed, cloud-based solution with seamless integration into the Microsoft Fabric ecosystem.

Why use Fabric SQL Database?

• Seamless integration with your Fabric ecosystem
• Database mirroring to Fabric OneLake
• Fully managed database-as-a-service
• Always running the latest SQL Engine with no patches or updates required
• Automatic scaling capabilities

See the Fabric SQL Database decision guide on Microsoft Learn for more information.

Prerequisites in Fabric

Create a Fabric SQL database

1. Navigate to your Fabric workspace.
2. Click + New item.
3. Scroll to the Store Data section and select SQL database (preview).
4. Enter a database name and click Create.

Add an admin user or service principal

1. Navigate to your Fabric workspace.
2. Click Manage Access.
3. Select Add people or groups.
4. Enter the name or email of the account to add:
   1. A Service Principal is recommended.
   2. A non-MFA user/password account is also supported.
5. Select the Admin role from the dropdown.
6. Click Add.

Locate connection details

1. Open the newly created SQL database in the Fabric portal.
2. Click Settings (the blue gear icon in the ribbon under the Home tab).
3. Select Connection Strings.
4. Note the server name — this is the value between Data Source = and the first comma. Include ,1433 but exclude the tcp: prefix.
5. Note the database name — this is the value between Initial Catalog= and the following semicolon.

Configure Jet Analytics

Configure your Prepare instance

This Jet Analytics portal is managed by the insightsoftware Support team. For assistance or additional information, reach out to Product Support.

1. Navigate to Instances in the Jet Analytics portal.
2. Click Add Instance > Add prepare instance.
3. Configure General Settings:

   

   • Enter the instance name.
   • Select SQL Server as the server storage type.
4. Configure SQL Server Settings:
   • Enter the server name from the connection string (including ,1433).
   • Enter the database name from the connection string.
   • Select the authentication type:
     • Microsoft Entra Service Principal (recommended)
     • Microsoft Entra Password Authentication (must be a non-MFA user)
   • Enter the username and password of a user or service principal with Admin permissions.
5. Validate the connection:

   

   • In Jet Analytics Data Integration, refresh the instances list.
   • Locate the newly created instance and double-click to open it.
   • Right-click the instance and select Edit Instance.
   • Click Test Storage Connection.

Troubleshooting

Invalid Cast Exception error

When executing a table in the Prepare instance, the following error is returned:

Cause: The Ingest Lakehouse Parquet files were created using Spark Runtime Version 1.3, which stores dates and timestamps in a format not directly supported by Fabric SQL Databases.

Resolution:

1. Navigate to your Fabric workspace and click Workspace settings.
2. Expand Data Engineering/Science and select Spark settings.
3. Click the Environment tab.
4. Set Runtime Version to 1.2 (Spark 3.4, Delta 2.4).
5. Click Save.
6. If your Ingest Lakehouse was previously created using runtime 1.3, run a full load transfer task for your data sources after switching to 1.2, or delete the affected tables in your Lakehouse and re-run the transfer tasks.

Fabric capacity paused

If you encounter connection errors indicating that Fabric capacity is unavailable, start your Fabric capacity in the Azure portal.