1

I have setup Managed Identity on my App Service and given the database access. I have a console app and used Microsoft.Data.SqlClient 3.0.1 as a nuget package that I test the connection as follows:

string ConnectionString1 = @"Server=demo-server.database.windows.net; 
Authentication=Active Directory Managed Identity; Encrypt=True; Database=DEMO";
using (SqlConnection conn = new SqlConnection(ConnectionString1))
{
    conn.Open();
}

But get the error ‘Invalid value for key authentication’ Can anyone help with this ?

Updated code:

 static void Main()
    {
        string str = @"Server=demo-server.database.windows.net; 
                 Authentication=Active Directory Default; Database=DEMO";
        string qs = "SELECT OrderID, CustomerID FROM dbo.Orders;";
        CreateCommand(qs, str);
    }
    private static void CreateCommand(string queryString,
        string connectionString)
    {
        using (SqlConnection connection = new SqlConnection(
                   connectionString))
        {
            SqlCommand command = new SqlCommand(queryString, connection);
            command.Connection.Open();
            command.ExecuteNonQuery();
        }
    }
}

UPDATE 2: Working code

     string ConnectionString =  @"Server=demo-server.database.windows.net,1433;Authentication=Active Directory Default; Encrypt=True;Database=DEMO";
            using (Microsoft.Data.SqlClient.SqlConnection conn = new Microsoft.Data.SqlClient.SqlConnection(ConnectionString))

            //conn.Open();

            using (Microsoft.Data.SqlClient.SqlCommand command = new Microsoft.Data.SqlClient.SqlCommand("SELECT OrderID FROM dbo.Orders;", conn))
            {
                command.Connection.Open();
                using (Microsoft.Data.SqlClient.SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine("\t{0}", reader.GetString(0));

                    }
                }
            }

It seems its how i was calling the using clause

Jennifer
  • 31
  • 6

1 Answers1

0

You should follow the guidance in the link: https://learn.microsoft.com/en-us/azure/app-service/tutorial-connect-msi-sql-database?tabs=windowsclient%2Cef%2Cdotnet and take a look here in case you use a user-assigned managed identity (UAMI) https://learn.microsoft.com/en-us/sql/connect/ado-net/sql/azure-active-directory-authentication?view=sql-server-ver16

You did not state if you use UAMI or SAMI, if you use UAMI, then:
"For a user-assigned managed identity, the client id of the managed identity must be provided when using Microsoft.Data.SqlClient v3.0 or newer."

Also for the UAMI, you can try to use "User ID =" the Object Principal ID instead of the ClientID. User ID=[PrincipalId]

Update

using (SqlConnection connection = new SqlConnection(connectionString))
{
   connection.AccessToken = token.Token;
   SqlCommand command = new SqlCommand(queryString, connection);
   command.Connection.Open();
   command.ExecuteNonQuery();
}

https://github.com/MicrosoftDocs/azure-docs/issues/103115

Niclas
  • 1,069
  • 4
  • 18
  • 33
  • I am using System Assigned Managed Identity. – Jennifer Jan 31 '23 at 20:36
  • Did you complete all the steps mentioned here? https://learn.microsoft.com/en-us/azure/app-service/tutorial-connect-msi-sql-database?tabs=windowsclient%2Cef%2Cdotnet – Niclas Jan 31 '23 at 23:32
  • Yes .. I referenced Microsoft.Data.SqlClient but get an error , System.AccessViolationException: 'Attempted to read or write protected memory. This is often an indication that other memory is corrupt.' – Jennifer Jan 31 '23 at 23:44
  • I have added the code that i test with in the question. It gives the error on the following line : command.Connection.Open(); – Jennifer Jan 31 '23 at 23:49
  • Have you tried to use this as well? I have updated my comment. https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection.accesstoken?view=dotnet-plat-ext-7.0 – Niclas Feb 01 '23 at 00:29
  • I have managed to connect without using tokens . I will post my updated code I prefer not to use tokens as im trying to make minimal change to current code base. Which will mean changing reference from System.Data.SqlClient to Microsoft.Data.SqlClient only – Jennifer Feb 01 '23 at 01:48