"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

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 a...
Read more

Does Sitecore 9 Marketing Automation Operations Service (ma-ops) require “xdb.processing.pools” connection string?

-
Image
Someone reported to me about the error System.Configuration.ConfigurationErrorsException: A connection string with the name 'xdb.processing.pools' is not configured. from their ma-ops server. When I first noticed that error, I immediately thought of configuring that connection string, however, Sitecore documentation on ma-ops ( https://doc.sitecore.com/developers/90/platform-administration-and-architecture/en/configure-a-marketing-automation-operations-service.html ) does not mention the need of having the connection string for “xdb.processing.pools”. While I was working on another seemingly unrelated task (installing Sitecore Commerce 903 in Azure), I found a serendipity where the installation guide of that SXC 903 ( http://commercesdn.sitecore.net/SitecoreXC_9.0/Installation-Guide/9.0.3/Sitecore_Experience_Commerce_Installation_Guide_for_Azure_9.0.3.pdf ) actually mentions this in the step 3 of the section “ 5.4. Enable Marketing Automation ”, as s...
Read more