topic Re: Creating SQL DB via REST API in SQL database

Creating SQL DB via REST API

mikegoatly — Thu, 05 Dec 2024 15:49:58 GMT

Hi folks - this documentation suggests that creating a SQL Database in Fabric should be possible using the items REST API: Deploy a SQL database via REST API - Microsoft Fabric | Microsoft Learn

When I try to use the suggested REST endpoint to create a database, I get the following error:

{ "requestId": "a1bd0f7b-045d-4e3b-a54a-d9682ad3fe66", "errorCode": "UnknownError", "message": "An unexpected error occurred while processing the request" }

(Note that I had to change the auth scope from https://analysis.windows.net/powerbi/api to https://api.fabric.microsoft.com)

Checking the Fabric Portal, the database doesn't show up there. For a short while after making this call if I try again using the same database name in the request, I get:

{ "requestId": "2700f348-ec09-495c-98c8-f21a2235463e", "errorCode": "ItemDisplayNameAlreadyInUse", "message": "Requested 'ApiSqlDb' is already in use" }

So it would seem that the API is kind of doing something, just not getting all the way through to completion.

Answers to the obvious questions:

1. Have I tried different workspaces: yes

2. Is the SQL Database feature enabled for me in the admin portal: yes

3. Can I create a SQL Database in the workspace through the UI: yes

Has anyone managed to get SQL Databases programatically created using this REST call?

Re: Creating SQL DB via REST API

dlevy — Thu, 05 Dec 2024 16:56:54 GMT

Hi mikegoatly - Sorry to hear things aren't going smoothly. Either scope will work - we just haven't converted this script to use the Fabric scope yet.

Are you able to see your other databases in the workspace when you look in $databases?

Re: Creating SQL DB via REST API

mikegoatly — Thu, 05 Dec 2024 17:02:52 GMT

Heh, that was an obvious question I didn't check 😊

The call to https://api.fabric.microsoft.com/v1/workspaces/<workspaceid>/items?type=SQLDatabase does return databases created in the workspace:

{ "value": [ { "id": "REMOVED", "type": "SQLDatabase", "displayName": "TestDb", "description": "", "workspaceId": "REMOVED" } ] }

I assume that's what you mean by $databases?

Re: Creating SQL DB via REST API

dlevy — Thu, 05 Dec 2024 17:07:57 GMT

That works too! I usually just drop $databases from line 26 into the immediate window in ISE to see what I've got in the workspace - usually to make sure I'm in the right one. 🤣

Is TestDb the one you are creating via API or the one you did via the GUI? Removed means redacted, right...or is that API returning that?

Re: Creating SQL DB via REST API

mikegoatly — Thu, 05 Dec 2024 17:23:55 GMT

Got it - I'm using Postman to exercise the APIs. I did try the script at one point to see if there was something I was doing wrong, but got the same error from that.

Yes, REMOVED is REDACTED in this instance and the database returned there is one that I manually created in the Fabric UI.

If I check the GET API after using the POST to try to create a new database, it doesn't show up there.

Re: Creating SQL DB via REST API

dlevy — Thu, 05 Dec 2024 17:59:01 GMT

For Postman you have to be careful with the bearer token. You can use the F12 developer tools in Edge to find the bearer token while logged into the Fabric portal or just grab it using that powershell snippet.

The powershell script was actually written because people were struggling to get all the tumblers to align with Postman. If the powershell script is not working then we are probably going to need to get a support case opened to have someone dig into the logs and see where it is short circuiting.

Re: Creating SQL DB via REST API

mikegoatly — Thu, 05 Dec 2024 20:21:38 GMT

I'm pretty sure I've got auth working. I have a collection set up where I can test out various Fabric APIs; the bearer token is kept as an environment variable so I can update it easily. (I use the Azure CLI to get new ones with az account get-access-token --query accessToken -o tsv --resource https://api.fabric.microsoft.com | Set-Clipboard )

What's the best way to raise the support case for the Fabric team?

Re: Creating SQL DB via REST API

dlevy — Thu, 05 Dec 2024 22:15:54 GMT

Yeah, if you're able to list the databases in the workspace then the token is good. Assuming you are using the token here is for the same ID that works fine to create databases in the portal then it should work. If the api call runs successfully and the database is not visible in the workspace within 5 minutes then something is wrong - 5 minutes is an enternity and then some here.

Here is the link that shows How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn.

Re: Creating SQL DB via REST API

dlevy — Fri, 17 Jan 2025 18:41:09 GMT

Hi mikegoatly - We published an update to the REST API example this week. Please give it a shot and let me know how it works for you. Here is the link: https://learn.microsoft.com/en-us/fabric/database/sql/deploy-rest-api