SQL Master Data Services 2019 - Website keeps prompting for login

Question

I have two SQL 2019 servers running Master Data Services which are continually prompting for credentials when accessing the MDS website. I can't find an MDS logfile to see what is failing but I've reviewed all the articles I can find without success:

• check that login account has access to MDS
• check that Windows Authentication is enabled on the MDS website
• ensure that Negotiate is the top Windows Authentication provider (I've also tried the other way around without success)

Does anyone have any ideas:

• What else I can try?
• Where the root cause might be logged?

Thanks.

Answer 1

Apologies for the long delay in response on this but we were working with Microsoft to resolve the problem.

This may be a niche issue but the resolution (after logging with Microsoft) was as follows:

1. SPNs on reporting services service account had to be updated to HTTPS/... as opposed to HTTP/... as they were originally
2. Remove the RSWindowsNegotiate option from the reporting service configuration file

Item 1 fixed the MDS issue but broke reporting services - it exhibited the same "looping credentials" problem

Item 2 fixed the newly-created reporting services problem

Answer 2

Hi @Peter Bishop ,

Welcome to Microsoft Q&A!
You can take a look at the second link provided by ShivamKumar and post any new information as soon as it becomes available. If there is no information and after you do those checks any problem is not solved, here is a feedback site link for you. You can post this phenomenon there and maybe your problem will be solved in the next update. After all, every Microsoft update will consider the needs of customers and fix bugs.

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi @Peter Bishop

There should be creds in credential manager for windows authentication to work without prompts for login again and again, make sure you checked both tabs(Web credentials and Windows Credential) in cred manager.
Also try using another browser (Internet Explorer or Chrome as it is supported by 2019 version) and see if the issue stays and if not update and reset your edge browser from settings and retry to see if that changes anything.
Goto start and search for "Internet options" open it and select content tab and then click on "Clear SSL State" and then try again and see if that works.
Also check this is enabled Internet Options > Advanced > Enable Integrated Windows Authentication
If things work on different browser but not on edge and nothing else resolves the issue try this:
Go to IIS > sites > YourMdsSite >double click on authentication > Windows authentication > Providers > Move NTLM up on the list

If this doesn't work either may be you would like to try enable tracing as per the second link provided in the very first comment and see the logs to find out what's going on.

Also try to right click in your webpage window and select Inspect Element and then Content Tab and see what's happening during the site is loading , if there are any redirects that are getting blocked or resources which are being prevented from getting loaded, may be due to strict tracking prevention settings enabled in your edge browser.

I listed almost everything I could think of at the moment and some relevant info found over internet too I hope this helps.

Regards,
Shivam

Answer 4

In case this helps, the MDS server has an HTTP SPN associated with the service account which is running Power BI; this is different from the MDS service account. To see if that was the root cause of the problem, we changed the service account of MDS to be the same as that of Power BI. This didn't solve the problem.

Colleague tracing network traffic did capture this:

----------

KDC_ERR_C_PRINCIPAL_UNKNOWN

Similar to KDC_ERR_S_PRINCIPAL_UNKNOWN, KDC_ERR_C_PRINCIPAL_UNKNOWN means the domain controller does not know which client principal it should use to encrypt the ticket. The difference here is that instead of a missing or duplicate SPN, there is a missing or duplicate User Principal Name (UPN).

To resolve this, determine if the requestor has the correct UPN. If so, then determine if there is a principal with a matching UPN. If there is a match, look for a duplicate UPN.
You may be scratching your head on the duplicate UPN part because if you try to add/modify a principal that has a duplicate UPN in Active Directory Users & Computers (ADUC), it will block you from doing this. Active Directory does not actually enforce the uniqueness of User Principal Names, but it leaves that up to the application. ADUC checks for duplicates, but other utilities like adsiedit.msc and ktpass.exe do not.

----------

Not sure if that'll help. Would be nice to know what missing UPN it's looking for.

Answer 5

Hi @Peter Bishop

Thanks for providing more information, (tried posting this as comment but it seems there is a limit on comment character and this exceeded by 1600 chars so posting as another answer)

Did you happen to change or update your password recently via AD directly (not via windows user interface prompt ) as you forgot it or it got expired recently and needed to be renewed? and the machine or computer system from where you changed it was not restarted and just being hibernated, if so you may be having an left over old session somewhere still open? can you please try killing/logging out all your old sessions restart your computer system (not production server just home computer or your jump machine which is assigned to you which you use to connect in organization).

Also can you check the date time on machines you are using to login ,if they are correct and in sync with difference of not more than 5 minutes(Timezones difference excluded) ?

Is your user account getting locked again and again in middle of session recently ? If yes this can happen if "Enforce user logon restrictions" is set to true—the default setting—and the account expiration date passes.

Can you try resetting your password and see if that fixes this issue? Link

Also cross check if these resolutions work for your case : Link
It says :

you need to add the requesting accounts to the Windows Authorization Access Group. By default, this group has the required access on all user and computer accounts. If you have also changed the permissions of Windows Authorization Access Group, you need to construct the permissions or use a custom group to grant the permissions.

Enabling kerbros event logging for getting more information on whats actually happening: Link
You can also face this issue in case you are part of large number of security groups, for more information and resolution : Link
Another link if NTLM is happening when kerbros should happen : Link

You can try to fix the issue using the tool provided by @Seeya Xi-MSFT