Thanks for the feedback! We are routing this to the appropriate team for follow-up. cc @aim-for-better, @idear1203.

Synapse team, please look into this issue.

Hello Team,

We would like to share more details about the scenario for helping.

We have reproduced on anohter Tenant/Subscription, having the following Debug detail from Powershell and also reviewing the Code from Powershell commands (using IL Spy which it can return code differences). This test has been done over a Subscription with Testing purpouse:

Avoding the Authentication Phase, and going directly to the actions over Synapse Workspaces we have seen:

The first call to check the Workspace works fine against the Api-Version 2019-06-01-preview. The GET request was:

https://management.azure.com/subscriptions/d75695ac-29e2-4d42-b940-d5281eb6bd08/resourceGroups/weappgroup01/providers/Microsoft.Synapse/workspaces/wks001?api-version=2019-06-01-preview

With the following Headers:

Headers:
x-ms-client-request-id : 2092d186-69f0-4b07-a83a-6269afa92416
accept-language : en-US

And Body is empty as expects in the GET Verb

The Response has been:

Status Code: OK (200)
Headers:
Pragma : no-cache
Strict-Transport-Security : max-age=31536000; includeSubDomains
x-ms-request-id : 87b2f204-a0ec-4a24-83fe-39dac09024cb
x-ms-ratelimit-remaining-subscription-reads: 11999
x-ms-correlation-request-id : a4363db0-95a9-4e0c-9855-c9d38e963cb0
x-ms-routing-request-id : FRANCESOUTH:20201023T082906Z:a4363db0-95a9-4e0c-9855-c9d38e963cb0
X-Content-Type-Options : nosniff
Cache-Control : no-cache
Date : Fri, 23 Oct 2020 08:29:05 GMT
Server : Microsoft-HTTPAPI/2.0

Body:

{
  "id": "/subscriptions/d75695ac-29e2-4d42-b940-d5281eb6bd08/resourceGroups/weappgroup01/providers/Microsoft.Synapse/workspaces/wks001",
  "location": "westeurope",
  "name": "wks001",
  "type": "Microsoft.Synapse/workspaces",
  "identity": {
    "type": "SystemAssigned",
    "TenantId": "8138a4df-9f1a-4158-8725-bd913911fc54",
    "principalId": "6bd33df6-7631-40cf-abee-3c0524a75da7"
  },
  "properties": {
    "connectivityEndpoints": {
      "web": "https://web.azuresynapse.net?workspace=%2fsubscriptions%2fd75695ac-29e2-4d42-b940-d5281eb6bd08%2fresourceGroups%2fweappgroup01%2fproviders%2fMicrosoft.Synapse%2fworkspaces%2fwks001",
      "sql": "wks001.sql.azuresynapse.net",
      "dev": "https://wks001.dev.azuresynapse.net",
      "sqlOnDemand": "wks001-ondemand.sql.azuresynapse.net"
    },
    "managedResourceGroupName": "synapseworkspace-managedrg-db583029-b52d-478c-98a8-ede65ad2bb39",
    "defaultDataLakeStorage": {
      "accountUrl": "https://westa001.dfs.core.windows.net",
      "filesystem": "synapse"
    },
    "sqlAdministratorLogin": "Administrador",
    "privateEndpointConnections": [],
    "workspaceUID": "08c509ba-dcd2-4e1e-94e8-8bd0b739c964",
    "extraProperties": {
      "IsScopeEnabled": false,
      "WorkspaceType": "Normal"
    },
    "provisioningState": "Succeeded"
  }
}

The Expiration Check of the Token if Success and then the next call for ensuring if the Database already exists. This call returns the BadRequest error:

GET Request: https://management.azure.com/subscriptions/d75695ac-29e2-4d42-b940-d5281eb6bd08/resourceGroups/weappgroup01/providers/Microsoft.Synapse/workspaces/wks001/sqlDatabases/galaxydb?api-version=2020-04-01-preview

Headers:
x-ms-client-request-id : da0535c3-47f7-4fad-8f1b-3929abf06826
accept-language : en-US

Body is empty as expected

The Response has been:

Status Code: Bad Request
Headers:
Pragma : no-cache
x-ms-failure-cause : gateway
x-ms-request-id : 465b3c78-c686-4c10-876c-816f41df51a4
x-ms-correlation-request-id : 465b3c78-c686-4c10-876c-816f41df51a4
x-ms-routing-request-id : FRANCESOUTH:20201023T082906Z:465b3c78-c686-4c10-876c-816f41df51a4
Strict-Transport-Security : max-age=31536000; includeSubDomains
X-Content-Type-Options : nosniff
Cache-Control : no-cache
Date : Fri, 23 Oct 2020 08:29:06 GMT

Body:

{
  "error": {
    "code": "NoRegisteredProviderFound",
    "message": "No registered resource provider found for location 'westeurope' and API version '2020-04-01-preview' for type 'workspaces'. The supported api-versions are '2019-06-01-preview'. The supported locations are 'westus2, eastus, northeurope, westeurope, southeastasia, australiaeast, westcentralus, southcentralus, eastus2, uksouth, westus, australiasoutheast, eastasia, brazilsouth, centralus, centralindia'."
  }
}

The command continue executing after this response because if the code is different than OK the body is not included as part of the response, and also it does not generate an exception:

public async Task<AzureOperationResponse<SqlDatabase>> GetWithHttpMessagesAsync(string resourceGroupName, string workspaceName, string sqlDatabaseName, Dictionary<string, List<string>> customHeaders = null, CancellationToken cancellationToken = default(CancellationToken))
{
(...)
        if (statusCode == HttpStatusCode.OK)
	{
		_responseContent2 = await _httpResponse.Content.ReadAsStringAsync().ConfigureAwait(continueOnCapturedContext: false);
		try
		{
			_result.set_Body(SafeJsonConvert.DeserializeObject<SqlDatabase>(_responseContent2, Client.DeserializationSettings));
		}
		catch (JsonException val2)
		{
			JsonException val3 = val2;
			_httpRequest.Dispose();
			_httpResponse?.Dispose();
			throw new SerializationException("Unable to deserialize the response.", _responseContent2, (Exception)val3);
		}
	}
	if (_shouldTrace)
	{
		ServiceClientTracing.Exit(_invocationId, (object)_result);
	}
	return _result;
}

In the next frame, the function returns the Body which is Null

public static async Task<SqlDatabase> GetAsync(this ISqlDatabasesOperations operations, string resourceGroupName, string workspaceName, string sqlDatabaseName, CancellationToken cancellationToken = default(CancellationToken))
{
	AzureOperationResponse<SqlDatabase> val = await operations.GetWithHttpMessagesAsync(resourceGroupName, workspaceName, sqlDatabaseName, null, cancellationToken).ConfigureAwait(continueOnCapturedContext: false);
	try
	{
		return val.get_Body();
	}
	finally
	{
		((IDisposable)val)?.Dispose();
	}
}

Skip intermediate frames until reach the level where the logic choose if continue and when the Null is evaluated, then the code considers than the Database does not exists, then continue with the creation:

public override void ExecuteCmdlet()
{
       (...)
       if (base.SynapseAnalyticsClient.GetSqlDatabaseOrDefault(ResourceGroupName, WorkspaceName, Name) != null)
	{
		throw new SynapseException(string.Format(Resources.SynapseSqlDatabaseExists, Name, ResourceGroupName, WorkspaceName));
	}
	SqlDatabase sqlDatabase = new SqlDatabase
	{
		Location = workspaceOrDefault.Location,
		Tags = TagsConversionHelper.CreateTagDictionary(Tag, true)
	};
	string parameterSetName = base.ParameterSetName;
	if (parameterSetName == "CreateByNameParameterSet" || parameterSetName == "CreateByParentObjectParameterSet")
	{
		sqlDatabase.MaxSizeBytes = MaxSizeInBytes;
		sqlDatabase.Collation = (CmdletExtensions.IsParameterBound<NewAzureSynapseSqlDatabase, string>(this, (Expression<Func<NewAzureSynapseSqlDatabase, string>>)((NewAzureSynapseSqlDatabase c) => c.Collation)) ? Collation : "SQL_Latin1_General_CP1_CI_AS");
		if (base.ShouldProcess(Name, string.Format(Resources.CreatingSynapseSqlDatabase, ResourceGroupName, WorkspaceName, Name)))
		{
			PSSynapseSqlDatabase pSSynapseSqlDatabase = new PSSynapseSqlDatabase(base.SynapseAnalyticsClient.CreateSqlDatabase(ResourceGroupName, WorkspaceName, Name, sqlDatabase));
			this.WriteObject((object)pSSynapseSqlDatabase);
		}
		return;
	}

The request of the Creation is the following:

Check the Token to validate if expires: Success

Method: PUT
Request Url: https://management.azure.com/subscriptions/d75695ac-29e2-4d42-b940-d5281eb6bd08/resourceGroups/weappgroup01/providers/Microsoft.Synapse/workspaces/wks001/sqlDatabases/galaxydb?api-version=2020-04-01-preview

Headers:
x-ms-client-request-id : c422c3e5-ffdb-414d-80d6-6bd4fa771773
accept-language : en-US

Body:

{
  "properties": {
    "collation": "SQL_Latin1_General_CP1_CI_AS",
    "maxSizeBytes": 0
  },
  "location": "westeurope"
}

The response is the same than in the previous request for checking the database:

Status Code: Bad Request
Headers:
Pragma : no-cache
x-ms-failure-cause : gateway
x-ms-request-id : 8c3a8738-d271-467d-a968-bab0af09bfe2
x-ms-correlation-request-id : 8c3a8738-d271-467d-a968-bab0af09bfe2
x-ms-routing-request-id : FRANCESOUTH:20201023T082906Z:8c3a8738-d271-467d-a968-bab0af09bfe2
Strict-Transport-Security : max-age=31536000; includeSubDomains
X-Content-Type-Options : nosniff
Cache-Control : no-cache
Date : Fri, 23 Oct 2020 08:29:06 GMT

Body:

{
  "error": {
    "code": "NoRegisteredProviderFound",
    "message": "No registered resource provider found for location 'westeurope' and API version '2020-04-01-preview' for type 'workspaces'. The supported api-versions are '2019-06-01-preview'. The supported locations are 'westus2, eastus, northeurope, westeurope, southeastasia, australiaeast, westcentralus, southcentralus, eastus2, uksouth, westus, australiasoutheast, eastasia, brazilsouth, centralus, centralindia'."
  }
}

Based on this traces, we have try to re-register the provider because it was registered before the latest updates. After register again, we got the same response.

If we test creating manually the HTTP Request (with Postman) for check or create the database against the previous Api-Version as the response describe (2019-06-01-preview), this request return Not Found (as expected because it´s a new feature)

I hope this information will help to locate what we are doing wrong or solving the issue

Thanks in advance

Hi @isrgarmes , this command New-AzSynapseSqlDatabase is designed for a preview feature named SQL Pool Gen3 and only available in very few whitelisted subscriptions. Do you want to create a SQL pool? If so, could you please try New-AzSynapseSqlPool?

Thanks for the information.

We are implemeting the same steps than we did from the Portal, but using Powershell. What we are working on is on the following steps:

1. Create a Synapse Workspace instance for being used in SQL OnDemand mode
2. Create a Database instance inside of the Workspace (here is where we have the issue)
3. Assign the identity from external Azure Services with Managed Identities as Administrator of the new Database

If this command is not the way to do the point 2, how we can create the database?

Kind Regards

I think you should use New-AzSynapseSqlPool instead. I will let someone more familiar to this part to take a look.

In my case, my customer does not want to create a Pool, they wants to use SQL under demand (Serverless) like is commented on this article: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/on-demand-workspace-overview

SQL On demand is created by default on workspace creation time. The customer should just need to create a workspace then they can connect to the on demand instance.

Thanks, but if we use the the command New-AzSynapseWorkspace to create the workspace, we have not a database where works only have the workspace. Until we could have the database, we can not give access to the users or create the views over the external data. All this process is automatized using Azure DevOps. How we can proceed to create the Workspace and also the first database as we have when we done it from the Azure Portal?

Please refer to this documentation:
https://docs.microsoft.com/en-us/azure/synapse-analytics/quickstart-create-workspace
https://docs.microsoft.com/en-us/azure/synapse-analytics/quickstart-sql-on-demand

If you have already created a workspace then all you need to do is connect through one of the clients mentioned in the second link, and issue the create database call via TSQL.

Thanks for the insights. We will consider this option to manage the Automatic Delivery of the Solution to see if fits with Customer scenarios.