I am trying to use Active Directory with Password when trying to connect to sql azure and get the data. I am connecting using C# with .Net framework 4.6. But the problem is I am observing lot of latency when fetching the records(more than 2-3 seconds per call). I am trying to access these records with a Rest Api and have to connect to the server with backend.
The question is, is this latency expected? Do we have any performance overhead over using Active Directory based authentication compared to normal sql server authentication?
The server is in P3 pricing tier.
The authentication path should impact how long it takes to connect. It should not impact row retrieval time. You should examine any tracing in your app tier (REST is not an efficient method to retrieve lots of rows) to see what you can do to improve performance, batch results, or otherwise see what is slowing things down.
Within the data tier, you can examine the performance of queries using the query store to see how long these take to execute in the data tier: https://learn.microsoft.com/en-us/sql/relational-databases/performance/best-practice-with-the-query-store?view=sql-server-2017