Unable to send message from Azure SQL to Service Bus

Question

Unable to send message from Azure SQL to Service Bus

Spencer Thompson (Network) 0

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.

Oury Ba-MSFT 21,121 Reputation points Microsoft Employee Moderator

2024-07-05T18:15:58.6+00:00

@Spencer Thompson (Network) Thank you for reaching out.

It seems like you’re trying to send messages to Azure Service Bus from Azure SQL Server using Managed Identity, but you’re encountering an error.

Did you come across this example? https://free.blessedness.top/en-us/azure/service-bus-messaging/service-bus-dotnet-how-to-use-topics-subscriptions?tabs=passwordless

Regards,

Oury
Oury Ba-MSFT 21,121 Reputation points Microsoft Employee Moderator

2024-07-10T22:49:23.7466667+00:00

@Spencer Thompson (Network) I haven't heard back from you since my last comment and wanted to follow up on the above issue.

Were you able to make any progress?

Did you Add Microsoft Entra user to Azure Service Bus Owner role?

https://free.blessedness.top/en-us/azure/service-bus-messaging/service-bus-python-how-to-use-queues?tabs=passwordless#add-microsoft-entra-user-to-azure-service-bus-owner-role

You might also want to check this stack Overflow related question https://stackoverflow.com/questions/68133295/send-message-to-azure-servicebus-from-sql

2 answers

Your answer

Oury Ba-MSFT 21,121 Reputation points Microsoft Employee Moderator

2024-07-05T18:15:58.6+00:00

@Spencer Thompson (Network) Thank you for reaching out.

It seems like you’re trying to send messages to Azure Service Bus from Azure SQL Server using Managed Identity, but you’re encountering an error.

Did you come across this example? https://free.blessedness.top/en-us/azure/service-bus-messaging/service-bus-dotnet-how-to-use-topics-subscriptions?tabs=passwordless

Regards,

Oury
Oury Ba-MSFT 21,121 Reputation points Microsoft Employee Moderator

2024-07-10T22:49:23.7466667+00:00

@Spencer Thompson (Network) I haven't heard back from you since my last comment and wanted to follow up on the above issue.

Were you able to make any progress?

Did you Add Microsoft Entra user to Azure Service Bus Owner role?

https://free.blessedness.top/en-us/azure/service-bus-messaging/service-bus-python-how-to-use-queues?tabs=passwordless#add-microsoft-entra-user-to-azure-service-bus-owner-role

You might also want to check this stack Overflow related question https://stackoverflow.com/questions/68133295/send-message-to-azure-servicebus-from-sql

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"

}

Oury Ba-MSFT 21,121 Reputation points Microsoft Employee Moderator

2024-08-15T16:13:49.1566667+00:00

Thank you Spencer Thompson (Network) for sharing with the community.

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this!

Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to "Accept " the answer. Accepted answers show up at the top, resulting in improved discoverability for others.

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;

Spencer Thompson (Network) 0 Reputation points

2025-09-24T10:02:45.25+00:00

Something strange has happened. I know I used this code to setup a credential that is used to send messages the Service Bus from inside SQL Server. However, now this code no longer works. I need help again because I need to setup a new Credential for a new Service Bus Queue and I am unable to use the credential doesn't actually send the message and I don't understand why..

56D2B880-4278-4DB8-AE34-84916885E475 {"response":{"status":{"http":{"code":401,"description":"SubCode=40100: Unauthorized : Unauthorized access for 'Send' operation on endpoint 'sb://netservice.servicebus.windows.net/netservice-portaljobupdates/messages'. Tracking Id: 6e89c3f5-095a-475a-a839-c651f4673cb8_G15"}},"headers":{"HTTP/1.1 401 SubCode=40100":"Unauthorized : Unauthorized access for 'Send' operation on endpoint 'sb://netservice.servicebus.windows.net/netservice-portaljobupdates/messages'. Tracking Id: 6e89c3f5-095a-475a-a839-c651f4673cb8_G15","Date":"Wed, 24 Sep 2025 10:02:00 GMT","Content-Length":"0","Server":"Microsoft-HTTPAPI/2.0","Strict-Transport-Security":"max-age=31536000"}}}

Share via

Unable to send message from Azure SQL to Service Bus

2 answers

Your answer