Unable to link Azure SQL Database to Static Web App

I have an Azure SQL Database running. I've created a database blog and table Test.

Using the Azure Portal I've linked my database to the SWA.

Inside my app I have a directory swa-db-connections with the staticwebapp.database.config.json file.

The error I'm getting from /data-api is:

{
   "Message":"{\u0022Message\u0022:\u0022Response status code does not indicate success: 400 (Bad Request).\u0022,\u0022ActivityId\u0022:\u00229bdbf202-015a-4e11-b239-bdb199103170\u0022}",
   "ActivityId":"<id>"
}

Leading me to believe there is an issue with the connection string, with this I'll leave out entities.

Database config

{
  "$schema": "https://github.com/Azure/data-api-builder/releases/download/v1.1.7/dab.draft.schema.json",
  "data-source": {
    "database-type": "mssql",
    "connection-string": "Server=tcp:endpoint.database.windows.net,1433;Initial Catalog=blog;Persist Security Info=False;User ID=my_sql_username;Password=my_sql_password;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;",
    "options": {
      "set-session-context": false
    }
  },
  "runtime": {
    "rest": {
      "enabled": true,
      "path": "/rest",
      "request-body-strict": true
    },
    "graphql": {
      "enabled": true,
      "path": "/graphql",
      "allow-introspection": true
    },
    "host": {
      "cors": {
        "origins": [],
        "allow-credentials": false
      },
      "authentication": {
        "provider": "StaticWebApps"
      },
      "mode": "production"
    }
  }
}

I've made sure to allow Azure resources to access the database alongside my public IP address (I'm not sure how else to "open" the database).

What could be the issue with the connection here? I've already tried unlinking the database as per Microsoft's help page.

I tried your configuration of staticwebapp.database.config.json and encountered the same error.

You need to change the connection string in staticwebapp.database.config.json as shown below:

"connection-string": "Server=tcp:<your_server>.database.windows.net,1433;Database=<your_db>;User ID=<your_user>;Password=<your_password>;Encrypt=true;"

Also, change the CORS origin to "*":

"cors": {

"origins": ["*"]

Next, add the entities section for the tables. For example:

"entities": {
  "Person": {
    "source": "dbo.MyTestPersonTable",
    "permissions": [
      {
        "actions": ["*"],
        "role": "anonymous"
      }
    ]
  }
}

In your case, it’s Test, so replace dbo.MyTestPersonTable with dbo.Test.

Then, add the code to list all items in Astro with REST/GraphQL requests as shown in the documentation below.

Create the table in Azure SQL as described. Refer to this guide for details on GraphQL and REST APIs in Astro.

I have referred this documentation to add an Azure SQL database connection to Azure Static Web Apps.

Use the following command to test locally: swa start . --data-api-location swa-db-connections

Then push the code to Git. In your YAML file, change the Out_location to dist. Link your Azure SQL database in the Database connection and view the app in the browser at /data-api/rest/<entity>.

For example, I have entities for Author and Comment as shown below:

"entities": {
  "Author": {
    "source": "dbo.Author",
    "permissions": [
      {
        "actions": ["*"],
        "role": "anonymous"
      }
    ]
  },
  "Comment": {
    "source": "dbo.Comment",
    "permissions": [
      {
        "actions": ["*"],
        "role": "anonymous"
      }
    ]
  }
}

Output with data-api/rest/Author:

Output with data-api/rest/Comment: