Azure function - connect to Azure SQL database using Active Directory Interactive authentication

Question

I'm using SqlClient in my Azure function. Currently when I try to create a new instance passing the connection string, I receive the following message

Keyword not supported: 'authentication'

My connection string is

server=tcp:mydbserver.database.windows.net;database=mydb;UID=AnyString;Authentication=Active Directory Interactive

My azure function has 'Identity' setting enabled.

My other .NET apps running as AppService are working excellent connecting to the same DB, but they use EntityFramework

I have tried to remove Authentication=Active Directory Interactive and also add the following line to the connection

 connection.AccessToken = new Microsoft.Azure.Services.AppAuthentication.AzureServiceTokenProvider().GetAccessTokenAsync("https://database.windows.net/").Result;

But I'm just getting different error messages like Login failed for user '' or Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

Thanks!

By 'Identity' do you mean Managed Identity?

PerfectlyPanda
– PerfectlyPanda

2020-03-31 17:28:21 +00:00
Commented Mar 31, 2020 at 17:28 — PerfectlyPanda
– PerfectlyPanda, Commented Mar 31, 2020 at 17:28
Yes, managed identity

vily
– vily

2020-03-31 18:50:30 +00:00
Commented Mar 31, 2020 at 18:50 — vily
– vily, Commented Mar 31, 2020 at 18:50
Please check my answer to see if it helps.

Jack Jia
– Jack Jia

2020-04-03 02:39:05 +00:00
Commented Apr 3, 2020 at 2:39 — Jack Jia
– Jack Jia, Commented Apr 3, 2020 at 2:39

SIDHU · Accepted Answer · 2022-05-27 02:15:13Z

1

Make sure you use Microsoft.Data.SqlClient not System.Data.SqlClient namespace

answered May 27, 2022 at 2:15

SIDHU

702 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2020-06-20 09:12:55Z

0

I got success with the following steps:

1. Provision an Azure Active Directory administrator for your Azure SQL Database server.

Note: Please ensure that you set a user as the AAD administrator for your Azure SQL Server.

2. Connect to your Azure SQL Database with the AAD account in step 1. You can use SSMS or just connect to your database from the portal.

3. Add service principal and assign a role

CREATE USER [Azure_AD_principal_name] FROM EXTERNAL PROVIDER;
EXEC sp_addrolemember 'db_datareader', 'Azure_AD_principal_name';

4. Code sample

string sqlResource = "https://database.windows.net/";
string tenantId = "e4c9ab4e-****-****-****-230ba2a757fb";
string serverName = "{server}.database.windows.net";
string databaseName = "{database}";

var token = new AzureServiceTokenProvider().GetAccessTokenAsync(sqlResource, tenantId).ConfigureAwait(false).GetAwaiter().GetResult();

string sqlConnectionString = String.Format("Data Source=tcp:{0},1433;Initial Catalog={1};Persist Security Info=False;Connect Timeout=30;Encrypt=True;TrustServerCertificate=False", serverName, databaseName);


using (SqlConnection connection = new SqlConnection(sqlConnectionString))
{
    connection.AccessToken = token;
    using (SqlCommand command = new SqlCommand("SELECT Distinct TABLE_NAME FROM information_schema.TABLES", connection))
    {
        connection.Open();
        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine(reader[0]);
            }
        }
    }
}

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Apr 2, 2020 at 12:07

Jack Jia

5,5391 gold badge14 silver badges14 bronze badges

1 Comment

Jack Jia Over a year ago

In your case, Azure_AD_principal_name should be the managed identity name of your VM. Or, you may add your managed identity service principal to a security group, and use the group name as Azure_AD_principal_name, then all members in that group will be able to connect to your Azure SQL database.

PramodValavala · Accepted Answer · 2020-04-02 08:04:24Z

0

I believe the connection.AccessToken property is the only way of doing this in .NET Core as per this conversation.

As for the error you are seeing with this, is probably because a user for the managed identity has not been created in the database. Follow the steps in this section for that.

Here is the SQL Script for reference

CREATE USER [<identity-name>] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [<identity-name>];
ALTER ROLE db_datawriter ADD MEMBER [<identity-name>];
ALTER ROLE db_ddladmin ADD MEMBER [<identity-name>];
GO

where <identity-name> is the name of the managed identity configured in Azure AD.

answered Apr 2, 2020 at 8:04

PramodValavala

6,6701 gold badge16 silver badges31 bronze badges

Collectives™ on Stack Overflow

Azure function - connect to Azure SQL database using Active Directory Interactive authentication

3 Answers 3

Comments

1. Provision an Azure Active Directory administrator for your Azure SQL Database server.

2. Connect to your Azure SQL Database with the AAD account in step 1. You can use SSMS or just connect to your database from the portal.

3. Add service principal and assign a role

4. Code sample

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1. Provision an Azure Active Directory administrator for your Azure SQL Database server.

2. Connect to your Azure SQL Database with the AAD account in step 1. You can use SSMS or just connect to your database from the portal.

3. Add service principal and assign a role

4. Code sample

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related