Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to:
SQL Server - Windows only
Azure SQL Managed Instance
This article covers how to configure a Master Data Services (MDS) database on a managed instance.
To prepare, you need to create and configure an Azure SQL Managed Instance and configure your web application machine.
Create a managed instance with a virtual network. See Quickstart: Create a SQL Managed Instance for details.
Configure a Point-to-Site connection. See Configure a Point-to-Site connection to a VNet using native Azure certificate authentication: Azure portal for instructions.
Configure Microsoft Entra authentication with SQL Managed Instance. See Configure and manage Microsoft Entra authentication with SQL for details.
Install a Point-to-Site connection certificate and VPN to ensure that the machine can access the managed instance. Refer to Configure a Point-to-Site connection to a VNet using native Azure certificate authentication: Azure portal for instructions.
Install the following Roles and Features:
Roles:
Note
Don't install WebDAV Publishing
Features:
Next, you install and configure Master Data Services.
Use the SQL Server setup installation wizard or a command prompt to install Master Data Services.
Open Setup.exe
, and follow the steps in the installation wizard.
Select Master Data Services on the Feature Selection page under Shared Features. This action installs:
Connect the Azure Virtual Network to ensure that you can connect to the managed instance.
Open the Master Data Services Configuration Manager and then select Database Configuration in the left pane.
Select Create Database to open the Create Database Wizard. Select Next.
On the Database Server page, complete the SQL Server instance field, and then choose the Authentication type. Select Test Connection to confirm that you can use your credentials to connect to the database via the chosen authentication type. Select Next.
Note
xxxxxxx.xxxxxxx.database.windows.net
.Your authentication must contain the "sysadmin" rule for managed instances.
Type a name in the Database name field. Optionally, to select a Windows collation, clear the SQL Server default collation check box and select one or more of the available options. For example, Case-sensitive. Select Next.
In the User name field, specify the Windows account of the default super user for Master Data Services. A super user has access to all functional areas and can add, delete, and update all models.
Select Next to view a summary of the settings for the Master Data Services database. Select Next again to create the database. You'll see the Progress and Finish page.
After the database is created and configured, select Finish.
For more information about the settings in the Create Database Wizard, see Create Database Wizard (Master Data Services Configuration Manager).
On the Database Configuration page in the Master Data Services Configuration Manager, choose Select Database.
Select Connect, choose the Master Data Services database and then select OK.
In Master Data Services Configuration Manager, select Web Configuration in the left pane.
In the Website list box, choose Default Web Site, and then select Create to create a web application.
Note
If you select Default Web Site, you'll need to separately create a web application. If you choose Create new website in the list box, the application is automatically created.
In the Application Pool section, enter a different user name, enter the password, and then select OK.
Note
Make sure that the user can access the database with the Active Directory Integrated authentication that you recently created. Alternatively, you can change the connection in web.config
later.
For more information about the Create Web Application dialog box, see Create Web Application Dialog Box (Master Data Services Configuration Manager).
On the Web Configuration pane in the Web application window, select the application you've created, and then choose Select in the Associate Application with Database section.
Select Connect and choose the Master Data Services database that you want to associate with the web application. Select OK.
You've finished setting up the website. The Web Configuration page now displays the website you selected, web application you created, and the Master Data Services database associated with the application.
Select Apply. You'll see the Configuration Complete message. Select OK in the message box to launch the web application. The website address is http://server name/web application/
.
To connect the managed instance database to the web application, you need to change the other authentication type.
Find the web.config
file under C:\Program Files\Microsoft SQL Server\150\Master Data Services\WebApplication
. Modify the connectionString to change the other authentication type to connect to the managed instance database.
The default authentication type is Active Directory Integrated
as shown in the following sample connection string:
<add name="MDS1" connectionString="Data Source=*****.*****.database.windows.net;Initial Catalog=MasterDataServices;Integrated Security=False;Connect Timeout=60;Authentication="Active Directory Integrated"" />
MDS also supports Active Directory password authentication and SQL Server authentication, as shown in the following sample connection strings:
Active Directory password authentication
<add name="MDS1" connectionString="Data Source=*****.*****.database.windows.net;Initial Catalog=MasterDataServices;Integrated Security=False;Connect Timeout=60;Authentication="Active Directory Password" ; UID=bob@example.com; PWD=MyPassWord!" />
SQL Server authentication
<add name="MDS1" connectionString="Data Source=*****.*****.database.windows.net;Initial Catalog=MasterDataServices;Integrated Security=False;Connect Timeout=60;User ID=UserName;Password=MyPassword!;" />
Install the SQL Server 2019 Cumulative Update. Master Data Services will be updated automatically.
You might get the error: The client version is incompatible with the database version
after installing SQL Server 2019 Cumulative Update.
To fix this issue, you need upgrade the database version:
Open the Master Data Services Configuration Manager, and then select Database Configuration in the left pane.
On the Database Configuration page in the Master Data Services Configuration Manager, choose Select Database.
Choose the Master Data Services database that you associated with the web application. Select Connect, and then select OK.
Select Upgrade Database… .
In the Upgrade Database Wizard, select Next on the Welcome page and on the Upgrade Review page.
Select Finish after all tasks are complete.
Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayTraining
Module
Migrate SQL Server workloads to Azure SQL Managed Instance - Training
Migrate SQL Server workloads to Azure SQL Managed Instance
Certification
Microsoft Certified: Azure Database Administrator Associate - Certifications
Administer an SQL Server database infrastructure for cloud, on-premises and hybrid relational databases using the Microsoft PaaS relational database offerings.
Documentation
Overview - SQL Server Master Data Services
Learn about the key data organization and management features of Master Data Services. Master Data Services enables you to manage a master set of your data.
Installation and Configuration - SQL Server Master Data Services
Learn how to install Master Data Services on a Windows Server 2012 R2 computer, configure the MDS database and website, and deploy the sample models and data.
Database Requirements - SQL Server Master Data Services
Use Master Data Services Configuration Manager to create and configure the Master Data Services database, which stores all master data.