Aimplan for SQL database in Fabric

20min
SQL database in Fabric is in public preview. Features and functionality may change. Ensure to check for updates frequently.
This document describes how to configure a SQL database in Microsoft Fabric for use with Aimplan.
﻿
Prerequisites
Before you begin, ensure you have the following:
Administrative privileges to Microsoft Fabric.
Permissions to create applications in Microsoft Entra.
﻿
Create a New SQL Database in Fabric
Follow the steps in the official Microsoft documentation to create a new SQL database: Create SQL Database in Fabric.
﻿
Retrieve the Connection String
The database’s connection string is required for connection. Follow these steps to locate it:
Open the SQL database in Fabric.
Navigate to Connection Strings in the database settings.
Record the connection string for later use.
For detailed guidance, see the Microsoft documentation.
﻿
Create a Service Principal
SQL databases in Fabric use Microsoft Entra ID for authentication. Traditional username/password methods are not supported.
Method #1 - Using PowerShell
If you are comfortable running commands in PowerShell, you can use this snippet to create a new service principal with client secret. Please record the outputs as they will be used in subsequent steps.
PowerShell
az ad sp create-for-rbac --name "Aimplan Fabric SQL"
az ad sp create-for-rbac --name "Aimplan Fabric SQL"
﻿
Method #2 - Using GUI
Register a New Application in Microsoft Entra
Sign in to the Microsoft Entra admin center.
If managing multiple tenants, switch to the correct tenant using the Settings icon ⚙️ in the top menu.
Navigate to Identity > Applications > App registrations, then select New registration.
Enter a Name for the application, e.g., Aimplan Fabric SQL.
For Supported account types, select Accounts in this organizational directory only.
Click Register.
Once registered, record the following details: 
Directory (tenant) ID
Application (client) ID
Object ID
Create a Client Secret
Return to the App registrations section of Microsoft Entra.
Select the application you just created.
Navigate to Certificates & secrets > New client secret.
Provide a description and select an expiration period.
Click Add.
Record the client secret Value. This is displayed only once.
﻿
Configure Permissions
Aimplan typically uses two separate logins for database access, but for simplicity, this guide uses one. For production environments, configure two logins as recommended in the Customer Managed Azure SQL Guide﻿.
Scripts include placeholders like <xxxx>, e.g., CREATE SCHEMA I<xxxx>DAT. Replace <xxxx> with your instance number, using only numeric values (e.g., CREATE SCHEMA I0007DAT).
﻿
Create Application Schemas
Open the database in Fabric.
Select New Query, paste the following SQL code, and click Run:
SQL
CREATE SCHEMA I<xxxx>DAT
GO

CREATE SCHEMA I<xxxx>PBI
GO
CREATE SCHEMA I<xxxx>DAT
GO

CREATE SCHEMA I<xxxx>PBI
GO
﻿
Replace <xxxx> with your instance number.
﻿
Create a Role for the Aimplan Application
Navigate to the Security tab and select Manage SQL security > New role.
Enter a name for the role, e.g., Aimplan Application.
Grant full permissions on I<xxxx>DAT, then click Save.
﻿
Assign the Application to the Role
Select the newly created role and click Manage Access.
Enter the name of the application created earlier, then click Add.
(Optional) If prompted to share the database: 
Click Share database.
Re-enter the application's name and click Grant.
Save your changes.
﻿
Configure Additional Permissions
Since traditional logins are not used, permissions must be assigned to the application directly. Use the following SQL commands:
SQL
-- Replace <xxxx> with your instance number and <App Registration Name> with the application name
ALTER AUTHORIZATION ON SCHEMA :: I<xxxx>DAT TO [<App Registration Name>]
ALTER AUTHORIZATION ON SCHEMA :: I<xxxx>PBI TO [<App Registration Name>]
GRANT CREATE TYPE, CREATE TABLE, CREATE VIEW TO [<App Registration Name>]
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA :: I<xxxx>DAT TO [<App Registration Name>]
ALTER USER [<App Registration Name>] WITH DEFAULT_SCHEMA = I<xxxx>DAT
-- Replace <xxxx> with your instance number and <App Registration Name> with the application name
ALTER AUTHORIZATION ON SCHEMA :: I<xxxx>DAT TO [<App Registration Name>]
ALTER AUTHORIZATION ON SCHEMA :: I<xxxx>PBI TO [<App Registration Name>]
GRANT CREATE TYPE, CREATE TABLE, CREATE VIEW TO [<App Registration Name>]
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA :: I<xxxx>DAT TO [<App Registration Name>]
ALTER USER [<App Registration Name>] WITH DEFAULT_SCHEMA = I<xxxx>DAT
﻿
﻿
Gather and Send Information to Aimplan
After completing the steps, collect the following details:
Connection String
Directory (tenant) ID
Application (client) ID
Object ID
Client Secret
Send these details securely as a one-time secret to [email protected].
﻿
Notes and Best Practices
Secure Storage: Safeguard client secrets and connection strings. Only send secrets over secure channels.
Expiration Management: Set reminders to renew client secrets before expiration.
﻿
Updated 02 Jan 2025
Did this page help you?
Firewall and Network Requirements for Azure SQL Database Access
Aimplan Training