Unable to send message from Azure SQL to Service Bus

Question

The goal is to send messages to Service Bus from Azure SQL Server.

I have added the Managed Identity to the queue with the Azure Service Bus Data Owner permission.

When I run the code to set up the credential, there is no error.

OPEN MASTER KEY DECRYPTION BY PASSWORD='<password>'

GO

CREATE DATABASE SCOPED CREDENTIAL [https://<name>.servicebus.windows.net/<queue>] WITH IDENTITY = 'Managed Identity', SECRET = '{"resourceid":"<name>"}';

When I run the code to send the message I get an error.

DECLARE @response NVARCHAR(MAX);

DECLARE @url nvarchar(4000) = N'https://<name>.servicebus.windows.net/<queue>';

DECLARE @payload NVARCHAR(MAX) = '{ "message": "Hello, world!" }';

EXEC sp_invoke_external_rest_endpoint

@method = N'POST',

@url = @url,

@credential = [https://<name>.servicebus.windows.net/<queue>],

@payload = @payload,

@response = @response OUTPUT;

SELECT @response;

Error:

Msg 31636, Level 16, State 1, Procedure sys.sp_invoke_external_rest_endpoint_internal,

Line 1 [Batch Start Line 0]

Error retrieving the managed identity access token for the resource id '<name>'. JWTErrorState = 79. 'AADSTS500011: The resource principal named <name> was not found in the tenant named <tenant name>. This can happen if the application has not been installed by the administrator of the tenant or consented to by any user in the tenant. You might have sent your authentication request to the wrong tenant. Trace ID: 6af1b66b-1b58-43c1-924d-d61230730300 Correlation ID: ed0cb312-18e4-4598-9398-1ff7185647da Timestamp: 2024-07-03 17:58:48Z'

I believe what I have done is followed the instruction but I cannot find any example code that covers the exact scenario I am attempting.

Answer 1

Ok so the problem was not related to managed identity.

Clearly that was set up correctly. The issue was to do with the parameters.

The correct code is as follows:

`DROP DATABASE SCOPED CREDENTIAL [https://<serviceBusName>.servicebus.windows.net/<queueName>]`

OPEN MASTER KEY DECRYPTION BY PASSWORD='<thePassword>'

CREATE DATABASE SCOPED CREDENTIAL [https://<serviceBusName>.servicebus.windows.net/<queueName>] WITH IDENTITY = 'Managed Identity', SECRET = '{"resourceid":"https://servicebus.azure.net"}';

DECLARE @response NVARCHAR(MAX);

DECLARE @url NVARCHAR(4000) = N'https://<serviceBusName>.servicebus.windows.net/<queueName>/messages';

DECLARE @Guid NVARCHAR(40) = NEWID();

DECLARE @payload NVARCHAR(MAX) = '{ "message": "Hello, world! ' + @Guid + '" }';

EXEC sp_invoke_external_rest_endpoint

@method = N'POST',

@url = @url,

@credential = [<serviceBusName>.servicebus.windows.net/<queueName>],

@payload = @payload,

@response = @response OUTPUT;

PRINT @Guid + ' ' + @response;

The Guid is added so that when testing the Guid of the message can be correlated with the message in the Service Bus Explorer.

80F40412-5445-4269-8899-D01CFA8863B5

{

"response":{

"status":{

"http":{

"code":201,

"description":"Created"

}

},

"headers":{

"Date":"Fri, 19 Jul 2024 08:38:04 GMT",

"Transfer-Encoding":"chunked",

"Content-Type":"application\/xml; charset=utf-8",

"Server":"Microsoft-HTTPAPI\/2.0",

"Strict-Transport-Security":"max-age=31536000"

}

}

}

Answer 2

@Spencer Thompson (Network)

Issue: Unable to send message from Azure SQL to Service Bus

CREATE DATABASE SCOPED CREDENTIAL [https://<name>.servicebus.windows.net/<queue>] WITH IDENTITY = 'Managed Identity', SECRET = '{"resourceid":"<name>"}';

Resolution: Changed the above code to the following and it worked.

CREATE DATABASE SCOPED CREDENTIAL [https://<serviceBusName>.servicebus.windows.net/<queueName>] WITH

IDENTITY = 'Managed Identity', SECRET = '{"resourceid":"https://servicebus.azure.net"}';OPEN MASTER KEY DECRYPTION BY PASSWORD='<thePassword>'

CREATE DATABASE SCOPED CREDENTIAL [https://<serviceBusName>.servicebus.windows.net/<queueName>] WITH IDENTITY = 'Managed Identity', SECRET = '{"resourceid":"https://servicebus.azure.net"}';

DECLARE @response NVARCHAR(MAX);

DECLARE @url NVARCHAR(4000) = N'https://<serviceBusName>.servicebus.windows.net/<queueName>/messages';

DECLARE @Guid NVARCHAR(40) = NEWID();

DECLARE @payload NVARCHAR(MAX) = '{ "message": "Hello, world! ' + @Guid + '" }';

EXEC sp_invoke_external_rest_endpoint

@method = N'POST',

@url = @url,

@credential = [<serviceBusName>.servicebus.windows.net/<queueName>],

@payload = @payload,

@response = @response OUTPUT;

PRINT @Guid + ' ' + @response;