topic Re: New Fabric SQL Datasbase does not let me CREATE TABLE from SSMS in Data Engineering

New Fabric SQL Datasbase does not let me CREATE TABLE from SSMS

ToddChitt — Thu, 05 Dec 2024 15:43:10 GMT

I just now provisioned a Fabric Workspace (under a Trial license). I provisioned a Fabric SQL Database.

Like I do with all other Fabric Workspaces, I copy the SQL Connection String and set up a new Registered Server in SQL Server Management Studio. Behind this SQL Endpoint I have access to the Fabric Warehouses, Lakehouses, and now the SQL Databases.

BUT, I cannot issue a CREATE TABLE statement from within SSMS. I get a permissions error. The same statement can be run in either a Lakehouse or a Warehouse in the same Fabric Workspace. I am an Admin in the workspace, and I was the one to create the SQL database.

I can do a CREATE TABLE statement from within the Fabric browser interface with the SQL Database, but NOT from SSMS.

I see this as a BUG, either from the standpoint of Fabric or of SSMS.

I discovered this by trying to create and run a Copy Job (Preview) and use my Fabric SQL Database as the destination. This failed with the same permissions issue. Since this was all Fabric, I'm leaning to a Fabric issue.

Anyone run accross this?

Re: New Fabric SQL Datasbase does not let me CREATE TABLE from SSMS

Anonymous — Fri, 06 Dec 2024 03:14:48 GMT

Hi @ToddChitt ,

I am currently unable to create a SQL database in Fabric, but found the same thing when connecting to the data warehouse in SSMS.

This error message indicates that an external policy denial was encountered while executing the CREATE TABLE statement. This usually means that the current privileges are insufficient to perform the operation.

I found a way:

First, use the following statement in SSMS to query the user currently in use.

SELECT SYSTEM_USER.

Create a SQL query in Fabric warehouse and assign the db_owner role to the current user using the following statement.

USE [warehouseTest1]; -- Replace with your database name -- Grant db_owner role to the user ALTER ROLE db_owner ADD MEMBER [Yanghuijie@M365x21440024.onmicrosoft.com]; -- Replace with your username

Now go back to SSMS and you can create the table. The result is shown below:

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Re: New Fabric SQL Datasbase does not let me CREATE TABLE from SSMS

ToddChitt — Fri, 06 Dec 2024 14:14:35 GMT

With Fabric SQL Databases being so new, there WILL be learning curves (and bumps in the road) as we, the community struggle to adopt them and learn of the nuance differences. One key difference is the Connection Strings.

I have been using the Connection String from a Fabric Warehouse or Lakehouse in SSMS for quite some time (I usually set up Registered Servers for them.) And that Connection String exposes all of the database type objects under one server. You can even run cross-database queries if you want to move data from a Lakehouse to a Warehouse. And the new Fabric SQL Database is also there. But it is NOT there as a typical SQL Database because the SQL Endpoint is an ANALYTIC Endpoint.

If you open the SQL Database in the browser, then click "Open in" >> "SQL Server Management Studio":

You get a dialog that shows the SQL Endpoint and a Database name (which is your database plus a GUID after it).

Compare this connection string to the one from the Lakehouse endpoint. They will be different:

(same 'server' GUID, but different suffixes)

Behind the DATABASE Endpoint you have full SQL Database capabilities: PRIMARY KEYS, INDEXES, etc.

Behind the ANALYTIC/DATA WAREHOUSE Endpoint you have ANALYTIC capabilities.

One allows CREATE TABLE and one does not. But same data.

Microsoft is still working on things like removing the GUID from the SQL Endpoint Database name, and other minor bugs.

This was an invaluable revelation for me pointed out by someone in the forums.