Creating SQL DB via REST API

[mikegoatly]

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?

[dlevy]

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

[mikegoatly]

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?

[dlevy]

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?

[mikegoatly]

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.

[dlevy]

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. 

[mikegoatly]

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?

[dlevy · Accepted solution]

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.

[dlevy]

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?