Receiving error message when trying to connect with Master Data Services Add-in For Microsoft Excel outside the domain where MDS is configured

Receiving error message when trying to connect with Master Data Services Add-in For Microsoft Excel outside the domain where Master Data Services is configured:

 

Error messages:

If Windows Authentication is enabled on IIS side, you will receive the following error message on Excel side:

"The HTTP request is unauthorized with client authentication scheme ‘Negotiate’. The authentication header received from the server was ‘Negotiate, NTLM’.”

If Basic Authentication is enabled on IIS side, you will receive the following error message on Excel side:

“The HTTP request is unauthorized with client authentication scheme Negotiate. The authentication header received from the server was Basic realm=domain.”

 

Environment:

-  SQL Server 2012 with MDS installed;

-  Installed the Master Data Services Add-in For Microsoft Excel on a computer outside the domain where resides the MDS database and web application. To install the Master Data Services Add-in For Microsoft Excel , you can use the following link : https://go.microsoft.com/fwlink/?LinkId=219530    or  you can use the link on the front page of your Master Data Manager;

-  Configured a connection in Excel and disabled 'Anonymous Authentication' in IIS to not allow unauthenticated users to access MDS. When you try to connect from Excel outside the domain, having enabled on IIS : Basic Authentication or Windows Authentication you receive the error messages described above;

-  If it is enabled 'Anonymous Authentication' in IIS, it allows all users to access MDS, so everybody can do modification with no authentication and there are no error message (but the connection is not secure) in Excel connection;

-  It is not possible to introduce credentials for an account outside the domain using Microsoft Excel in ‘Run as …’ section;

You cannot connect to MDS using the Master Data Services Add-in For Microsoft Excel , with an account outside the domain (Workgroup not trusted domain). In Excel you cannot introduce any credentials.

 

Connection Test steps:

As a possible connection test you can verify if the following steps works on your environment:

-  In IIS Manager, enabled only ‘Windows Authentication’ for the web site - on the web site level not the Service folder level (this is enabled automatically along with Anonymous authentication)’

-  On the computer outside the domain opened the Master Data Manager and then, in the popup to introduce the account and password was introduced the account that is in the same domain an MDS and his password;

-  Afterwards you have the possibility to save the credentials by checking the ’Save Credentials’ options on the same popup;

-  Connected successfully to Master Data Manager ;

-  On the same computer where you have Master Data Services Add-in For Microsoft Excel , go to Control Panel- User Account- Manage your credentials –Windows credentials and there have to be also the credentials you introduced above. If they are not, please introduce them manually also the URL for the Master Data Manager;

- Afterwards, open the Excel, where you already installed the Master Data Services Add-in For Microsoft Excel, and in the Connect tab - Manage connections, create the new connection using the correct URL and there was no need to introduce the credentials;

 

Depending on the environment and other configurations settings it is possible that the steps for the connection test did above, not to work on your environment.

Comments

• Anonymous
  January 13, 2015
  Has anyone found a resolution to this?
• Anonymous
  March 02, 2015
  What i found works.
  log on the the mds server via the web browser and make sure to check the option to save password. (This add the requisite credentials to the local computer credential manager for Excel Mds connector/plugin to reference on connecting)
  
  Add your plugin and test connection.
  This should work.
• Anonymous
  October 28, 2015
  I wonder why the Excel add-in does not allow user id and pw to be passed. Security issue?
• Anonymous
  March 02, 2016
  This adding of the windows credentials to the client machine, helped me fixed this in my Azure setup without activating a domain. Thanks