validate jwt tokens inside stored procedure

Bert Sachs (DB) 0

I have an azure api (web api) that access an azure sql database. The access to azure sql is secured by the user managed identity of the api service.

As an additional security layer i want to pass the bearer access token to a stored procedure and validate. Is there any SQL Server built-in functionality to validate an AzureAD access token and extract claims and scopes?

regards,

BSDB

Shweta Mathur 27,456 Reputation points Microsoft Employee

2023-03-31T06:42:03.29+00:00

Hi @Bert Sachs (DB) ,

Just checking in to see if the below answer helped. If this answers your query, please don’t forget to click "Accept Answer" which might be beneficial to other community members reading this thread. And, if you have any further query do let us know.

1 answer

Alfredo Revilla - Senior Freelance SWE, SWA, IAM 27,016 Reputation points

2023-03-29T04:40:40.4433333+00:00

Hello @Bert Sachs (DB) , Azure AD access token are validated by Azure SQL Server, otherwise any could be accepted. Additional authorization rules are applied based on Azure SQL roles and permissions. Using managed identities is already a security best practice.

Let us know if you need additional assistance. If the answer was helpful, please accept it and rate it so that others facing a similar issue can easily find a solution.
Please sign in to rate this answer.
Bert Sachs (DB) 0 Reputation points

2023-03-29T22:36:01.12+00:00

Hi Alfredo,

thanks for your reply. My concern is that if the web api get compromised and is running malicious code, the malicious code could access sql server with "unsecure" parameters.

I am already restricting access to sql server to a certain database schema that exposes only some stored procedures.

My use case:

User opens SPA and signs-in -> SPA calls Web API with bearer token -> Web API extracts user (email) of bearer token -> Web API calls stored procedure with user as parameter to get only the data of the user.

As long as my Web API don't get compromised everything is fine. But i would like to pass a bearer token to sql server that gets decoded and extracts the user instead of passing the user as a plain text. This way i could guarantee that the call to the stored procedure is an authorized call. If not it won't return any data.

As said, i see it as an additional security layer. Restricting access to (user) managed identitites is best practice and the cloud equivalent to on-prem service accounts.

regards,

BSDB

Alfredo Revilla - Senior Freelance SWE, SWA, IAM 27,016 Reputation points

2023-03-29T23:54:19.4933333+00:00

Got it but no, Azure SQL Server does not support reading the access token. You would've to (re)attach it as a SQL param which would be pretty much the same as sending plain text. That being said, your best bet is to use security best practices when designing, coding, deploying and hosting your API. Solutions such as Azure API Management can you help you take care of the latter items.
Sign in to comment