Previously, we had talked about Get Started with Microsoft Fabric – All in-one place for all your Analytical needs…. Part 1 - Micros...
In today's Part we would talk about Microsoft Fabric - How can a SQL user or DBA connect
In Microsoft Fabric, a Lakehouse SQL Endpoint or Warehouse is accessible through a Tabular Data Stream, or TDS endpoint, familiar to all modern web applications that interact with a SQL Server endpoint. This is referred to as the SQL Connection String within the Microsoft Fabric user interface.
To get started, you must complete the following prerequisites:
- You need access to a SQL Endpoint or a Warehouse within a Premium capacity workspace with contributor or above permissions.
Authentication to warehouses in Fabric
In Microsoft Fabric, two types of authenticated users are supported through the SQL connection string:
- Azure Active Directory (Azure AD) user principals, or user identities
- Azure Active Directory (Azure AD) service principals
The SQL connection string requires TCP port 1433 to be open. TCP 1433 is the standard SQL Server port number. The SQL connection string also respects the Warehouse or Lakehouse SQL Endpoint security model for data access. Data can be obtained for all objects to which a user has access.
Retrieve the SQL connection string
To retrieve the connection string, follow these steps:
- Navigate to your workspace, select the Warehouse, and select More options.
- Select Copy SQL connection string to copy the connection string to your clipboard.
Get started with SQL Server Management Studio (SSMS)
The following steps detail how to start at the Microsoft Fabric workspace and connect a warehouse to SQL Server Management Studio (SSMS).
- When you open SSMS, the Connect to Server window appears. If already open, you can connect manually by selecting Object Explorer > Connect > Database Engine.
- Once the Connect to Server window is open, paste the connection string copied from the previous section of this article into the Server name box. Select Connect and proceed with the appropriate credentials for authentication. Remember that only Azure Active Directory - MFA authentication is supported.
- Once the connection is established, Object Explorer displays the connected warehouse from the workspace and its respective tables and views, all of which are ready to be queried.
When connecting via SSMS (or ADS), you see both a SQL Endpoint and Warehouse listed as warehouses, and it's difficult to differentiate between the two item types and their functionality. For this reason, we strongly encourage you to adopt a naming convention that allows you to easily distinguish between the two item types when you work in tools outside of the Microsoft Fabric portal experience.
Considerations and limitations
- SQL Authentication is not supported.
- Multiple Active Result Sets (MARS) is unsupported for Microsoft Fabric Warehouse. MARS is disabled by default, however if MultipleActiveResultSets is included in the connection string, it should be removed or set to false.
- On connection to a warehouse, you may receive an error that "The token size exceeded the maximum allowed payload size". This may be due to having a large number of warehouses within the workspace or being a member of a large number of Azure AD groups. For most users, the error typically would not occur until approaching beyond 80 warehouses in the workspace. In event of this error, work with the Workspace admin to clean up unused Warehouses and retry the connection, or contact support if the problem persists.
Check out the Video on
Microsoft Fabric, SQL Server Management Studio / SSMS, & Lakehoused Linked Data with NO DUPLICATION!
