"Only active directory users can impersonate other active directory users" when accessing an Azure SQL Database

-
Not long ago I faced the following error.

System.Data.SqlClient.SqlException (0x80131904): Only active directory users can impersonate other active directory users.
   at System.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__180_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.Tasks.Task.Execute()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
...so on...

Everything looked correct (such as the connection string containing the credential to connect the database was correct and the user seemed to have the correct permissions as well) therefore, I spent some time understanding further about SQL.

I did find some MSDN article talking about “EXECUTE AS” however, I still didn’t really know the actual issue and I couldn’t find many other available online articles related to this issue at that time, so hopefully, this article could save someone else’s time.

Eventually, I found out that there is a part in Azure which shows us the detail of our SQL server, such as:
Overview of SQL Server

The above error will occur if the database’s owner is set to be the same with the field “Active Directory admin” and the error can be avoided by using the user in “Server admin”.

You can check the database’s owner by opening the SSMS application to connect to your database server, do right click on the related database, and click “Properties” to see what is the current “Owner” of the database.

One of the ways to change it is to run a SQL query as follows:
ALTER AUTHORIZATION ON DATABASE::YourDatabaseName TO YourDbOwner

Comments

Post a Comment

Popular posts from this blog

Added Contact Is Not Shown in List Manager in a Scaled Sitecore XP 9

-
There was a time when I needed to help someone from finding what’s wrong with List Manager in a scaled SXP 9 (the exact version was Sitecore Experience Platform 9.0 rev. 180604 (9.0 Update-2) ). New contacts added into List Manager does not appear even though the blue color message box around the top area says: “ The contact has been added to the list, and will be available after indexing. ” Looking at the Sitecore logs, there are errors such as: 4244 17:14:27 ERROR [ListManagement]: Failed to get count of contacts for 86c048c4-761d-47c8-814f-30344a5dcac8 contact list. The error has been occurred: The HTTP response was not successful: InternalServerError Exception: Sitecore.XConnect.XdbSearchUnavailableException Message: The HTTP response was not successful: InternalServerError Source: Sitecore.Xdb.Common.Web    at Sitecore.Xdb.Common.Web.Synchronous.SynchronousExtensions.SuspendContextLock[TResult](Func`1 taskFactory)    at Sitecore.XConnect.Client.XConnectSync
Read more

Windows could not start the Sitecore Xconnect Search Indexer - {xConnectInstance}-IndexWorker service on Local Computer. Error 1064: An exception occurred in the service when handling the control request.

-
Image
I was doing upgrade from Sitecore 9 Initial Release to Sitecore 9 Update 1. I felt somehow confident that I have followed all the steps of upgrade guide correctly, but when I reached to the step in resuming service (page 47 in the document: “Start the Sitecore XConnect Search Indexer Windows service” ), I faced the following error: “Windows could not start the Sitecore Xconnect Search Indexer - {xConnect Instance}-IndexWorker service on Local Computer. Error 1064: An exception occurred in the service when handling the control request.” Based on MSDN , this error means: “ While handling a control request, the service attempted to access an illegal address. ” I then tried to run this application manually from the folder containing it ( C:\inetpub\wwwroot\<xConnectInstance>\App_data\jobs\continuous\IndexWorker ). It is clear that this error refers to the connection string which I obtained by copying and pasting directly (my mistake during installation
Read more

EXM Throws NonCriticalException "Failed to download string content" with Nested Exception of "403 Forbidden" in Azure Web App

-
Image
 Upon previewing email content in Azure Web App, one could get the following error: 9668 03:27:56 ERROR Exception: Sitecore.EmailCampaign.Model.Web.Exceptions.NonCriticalException Message: Failed to download string content, URL: https://testazure-cm.azurewebsites.net/?sc_itemid=%7B229384AB-6279-42BC-925F-1D28C0227C03%7D&sc_lang=en&sc_device=%7BFE5D7FDF-89C0-4D99-9AA3-B5FBD009C9F3%7D&ec_database=master&ec_id=96F0189C036942DDA62BCE8D68C6D1C7&ec_message_id=583137BAD51D4491AD8D07451BCC9000, Use IIS Credentials: True, UserAgent: ECM Dispatch Source: Sitecore.EmailCampaign    at Sitecore.Modules.EmailCampaign.Core.WebContent.DownloadString(String url, Boolean useIisCredentials, String userAgent)    at Sitecore.Modules.EmailCampaign.Messages.WebPageMail.GetMessageBody(Boolean preview)    at Sitecore.EmailCampaign.Cm.MessageInfo.FillContentEditorInfo() Nested Exception Exception: System.Net.WebException Message: The remote server returned an error: (403) Forbidden. Source:
Read more