Q. How to connect a virtual table to an Azure SQL database using SQL Server Management Studio (SSMS).
You can go through the steps of how to create a SQL database in Microsoft Azure and how to connect to the database in SQL Server Management Studio in the below video:
This is the 3rd part of the video “Virtual Entity Integration with SQL Database in Azure in MS Dynamics 365”
We have seen how to create a Virtual table in Dynamics 365 and do the mapping in part 1. Also, how to create an Azure SQL database from scratch in part 2.
In this part, we will see how to connect a virtual table to an Azure SQL database using SQL Server Management Studio (SSMS).
For the next part- you should have already created a Virtual table in D365, done mapping and created the Azure SQL database, and connect with SQL Server Management Studio (If not then please follow part 1 and part 2)
The whole process is divided into 3 parts/videos
1. Virtual Entity Mapping Part 1
2. Creation of Azure Database and connecting in SQL Server Mgmt Studio Part 2
3. Integration into MS Dynamics 365
Step 1: Go to make.powerapps.com, click on More from the left pane, and select connections.
If your organization already has connections, they would appear here and you could use them to create virtual tables.
Step 2: Select the data source you want to connect to, there are many connectors listed here but in this case, I'm selecting SQL Server.
Step 3: Select your SQL Authentication type - SQL Server Authentication.
Step 4: Provide additional information about your server name(it should be in your Azure SQL database overview section), database, and login credentials. The gateway credential is only needed if you're pointing to an on-premises SQL Server. Click Create.
Step 5: After the connection is created, a connection reference is needed so Dataverse can communicate through the connection.
Go to solutions on the left-hand navigation bar, Inside of your solution, click New, More, and select Connection Reference.
Step 6: Name your reference, select the connector you're using, in this case, SQL, and then you will see your connection in the drop-down list. Select it and click Create.
Step 7: Now, we will need to configure the virtual table in the advanced settings of your organization. Click on the gear icon on the upper right of the screen, and select Advanced Settings. In the top navigation bar, click the Settings drop-down and pick Administration,
Step 8: Select Virtual Entity Data Sources. Click the New button on the screen and select Virtual Data Provider from the drop-down list, and click OK.
Step 9: Name your virtual table source and select the connection reference you just created.
Note- If you're creating a SQL connection, you do not need to populate the Dataset Value field.
Click Save and return to the Maker portal.
(Asynchronous jobs are used to complete the connections to the data source this may take a few minutes.)
Step 10: Open your solution where you’ve added the connection reference and open or create any table. The Data Source dropdown will contain the new options which you added recently.
(An asynchronous job is run to connect to the table and retrieve all the column data and metadata needed for the virtual table. This can take a few minutes.)
