Permission denied for CREATE TABLE statement

[ToddChitt]

I created a new Workspace connected to a Free Trial license.

I created a new Fabric SQL Database.

I create a new Copy Job (preview) using on-prem SQL as the source via a Gateway (no issues there) and the Fabric SQL DB as the destination. I picked two small tables. The task failed with the following error:

I also tried to run a simple CREATE TABLE statement vis SSMS and got the same thing:

I WAS able to create a table via the Fabric browser window with this statement:

What I got was this (as viewed from SSMS) :

Keep in mind that in all cases I am using the same account, which is a Workspace Admin.

[dlevy · Accepted solution]

Hi @ToddChitt - Sorry to hear you are having problems! 

I've run into a similar situation before myself. For me it happens when I am connecting to the Analytics Endpoint instead of the SQL Endpoint.

For copy table the steps should look a lot like this document: Load data with data pipelines into SQL database - Microsoft Fabric | Microsoft Learn. The key thing is to pick your SQL database endpoint from the OneLake Data Hub.

For the SSMS connection, at the top of the SQL Editor, you should see an "Open In" menu. 

Click on SQL Server Management Studio and copy the server and database name into the connection dialog in SSMS. (database name is on the Connection Properties tab)

Let's make sure you are getting connected to the right place, then we can dig into the double schemas. We like schemas and think everyone should use them but once seems like enough. 

[ToddChitt]

OK, that's better. So is it REALLY a SQL database under the covers? Like FULL functionality equivalent to Azure SQL?

I say NO if I have to wait 10 to 20 minutes for my table to show up after creating it. 

And I say NO if the NAME I used to CREATE the database is NOT the name I get.

And I say NO if the Name of an object is different between the SQL and Analytic endpoints.

(If I wanted that nonesense I would have gone with Dataverse 🙂 )

My view from SSMS:

[dlevy]

Hi @ToddChitt - It sure is! It's the same SQL database as Azure SQL database. I think what might be throwing you off here is the Analytics Endpoint.

Your Fabric SQL database is just like any other SQL database that you already use. The thing that is new here is that everything that happens in your SQL database - all the Inserts, Updates and Deletes are published to a delta store in OneLake. 

The Analytics Endpoint is targeted primarily at reporting. There we read in the data from the delta store in OneLake. The time it takes for data to show up there depends on how busy the database is - if the database is not changing much we start to back off our polling intervals until we detect a change again.  The analytics endpoint is great because it isolates your reporting queries from your production database but it does introduce latency. If you need realtime data in your reports then you'll want to connect directly to the SQL endpoint just like you would with your application.

The GUID in the database name is something we are looking at. Right now we need that uniquifier to prevent database naming conflicts in our backend. Being able to do the level of management we do at a competitive price point requires it.

The double schema on the reporting copy of your data should not be there. We'd really appreciate it if you could open a case so we can gather more information and figure out what is going wrong there. Here is How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

[ToddChitt]

Hello @dlevy and thank you for the information. I have created a support case with Microsoft, Request Number: 2412060040007766.

Regarding the GUID appended to the Database Name behind the SQL Endpoint:

It's not there for the Analytic Endpoint. Did those guys figure out how to do a two-part unique key for a database (Server and database)? Seems that has to be unique. In fact, I ran into it yesterday trying to test out the Copy Job (Preview):

Solving that would go a long way to helping it get accepted by the consumers. Nobody likes when someone else messes with their settings.

By the way, I was happy to see that the Collation setting of the database was "...CI..." (Case Insensitive).

[dlevy]

Hi @ToddChitt - For things like that, https://ideas.fabric.microsoft.com/ is going to be a better bet. Support doesn't have a lot of options for something that is working the way the engineering spec says it should. Feel free to upvote and add entries to the ideas site with anything you think would make the product work better for you. We really appreciate your help improving the product!