How to grant read access to a function app on a Synapse serverless database

Question

How to grant read access to a function app on a Synapse serverless database

Razeen 0

I'm struggling to execute queries against a Synapse serverless database from a function app using managed identity auth.

The SQL I've tried to create the user for that function app is as follows:

Attempt [1]

Database: <DB Name>

CREATE USER [<Function-App Name>] FROM EXTERNAL PROVIDER
ALTER ROLE [db_datareader] ADD MEMBER [<Function-App Name>]

Attempt [2]

Database: master

CREATE LOGIN [<Function-App Name>] FROM EXTERNAL PROVIDER

Database: <DB Name>

CREATE USER [<Function-App Name>] FROM LOGIN [<Function-App Name>]
ALTER ROLE [db_datareader] ADD MEMBER [<Function-App Name>]

The error I get on either is:

Cannot find the CREDENTIAL 'Managed_Identity', because it does not exist or you do not have permission.

Could you please provide a list of correct steps to grant access?

Nandan Hegde 36,716 Reputation points MVP Volunteer Moderator

2023-12-22T03:36:14.3433333+00:00

Can you please confirm whether the Managed identity is enabled in the App function? And you are able to see that entity within Entra ID under all applications?

And also what permission you have on the Synapse serverless?

This would help us debug better
Razeen 0 Reputation points

2023-12-22T08:17:45.1333333+00:00

Sorry, I think you got confused with my question layout. The "Create User" statement attempts were successfully executed (implying that MI is enabled), but the failure was encountered by the function app when it tried to query a table in the serverless pool.

Not sure what permission you're referring to? I've assigned the "db_datareader" role as indicated in my question.
Nandan Hegde 36,716 Reputation points MVP Volunteer Moderator

2023-12-22T08:30:36.4066667+00:00

Thanks for clarifying as based on your query it meant that you were unable to add the function within the database.

Can you please share the code within the App function via which you are trying to connect to database and read the data
Razeen 0 Reputation points

2023-12-22T08:37:11.2833333+00:00
I use a connection string defined as follows:

Server=tcp:<synapse-server>-ondemand.sql.azuresynapse.net;Initial Catalog=<database>;Persist Security Info=False;Authentication=Active Directory Default; MultipleActiveResultSets=False; Encrypt=True; TrustServerCertificate=False; Connection Timeout=30;

An extract of the code using that:

using var conn = new SqlConnection(_connectionString); var command = conn.CreateCommand(); command.CommandText = sql; conn.Open(); using var reader = command.ExecuteReader();
Razeen 0 Reputation points

2024-01-08T08:02:15.5566667+00:00

@Nandan Hegde Do you have the solution to the above problem?
Langer Andreas 0 Reputation points

2024-01-09T09:10:07.9933333+00:00

Would also be interested in the solution. Same Issue.

Your answer

Nandan Hegde 36,716 Reputation points MVP Volunteer Moderator

2023-12-22T03:36:14.3433333+00:00

Can you please confirm whether the Managed identity is enabled in the App function? And you are able to see that entity within Entra ID under all applications?

And also what permission you have on the Synapse serverless?

This would help us debug better
Razeen 0 Reputation points

2023-12-22T08:17:45.1333333+00:00

Sorry, I think you got confused with my question layout. The "Create User" statement attempts were successfully executed (implying that MI is enabled), but the failure was encountered by the function app when it tried to query a table in the serverless pool.

Not sure what permission you're referring to? I've assigned the "db_datareader" role as indicated in my question.
Nandan Hegde 36,716 Reputation points MVP Volunteer Moderator

2023-12-22T08:30:36.4066667+00:00

Thanks for clarifying as based on your query it meant that you were unable to add the function within the database.

Can you please share the code within the App function via which you are trying to connect to database and read the data
Razeen 0 Reputation points

2023-12-22T08:37:11.2833333+00:00

I use a connection string defined as follows:

Server=tcp:<synapse-server>-ondemand.sql.azuresynapse.net;Initial Catalog=<database>;Persist Security Info=False;Authentication=Active Directory Default; MultipleActiveResultSets=False; Encrypt=True; TrustServerCertificate=False; Connection Timeout=30;

An extract of the code using that:

using var conn = new SqlConnection(_connectionString); var command = conn.CreateCommand(); command.CommandText = sql; conn.Open(); using var reader = command.ExecuteReader();
Razeen 0 Reputation points

2024-01-08T08:02:15.5566667+00:00

@Nandan Hegde Do you have the solution to the above problem?
Langer Andreas 0 Reputation points

2024-01-09T09:10:07.9933333+00:00

Would also be interested in the solution. Same Issue.

Share via

How to grant read access to a function app on a Synapse serverless database

Your answer