How to connect Sandbox (UAT) database through LCS and SSMS in D365 FO

Microsoft Dynamics 365 Finance and Operations (D365 F&O) is a powerful tool that can help businesses streamline their operations. 

When problems occur, it can be difficult to identify the root cause without having access to the underlying data, which may not be apparent on a form.

In this article, we will explore the process of connecting the Sandbox (UAT) database in D365 FO.

By connecting to a D365 SQL database, it becomes possible to query the tables within the database and pinpoint the source of a problem in a test environment.

Accessing the machine via remote desktop is prohibited in Microsoft-managed sandbox or UAT environments.

To connect to a test D365 database, it is necessary to utilize SQL Management Studio from a different device. 

To gain entry into the UAT database, you will need SQL connection details to establish a connection with a test D365 database via SQL Management Studio, and Access to connect to the database.

Connection Information from LCS

To obtain the necessary SQL connection details for establishing a connection with a D365 testing database,

Step 1: You can find Open Life Cycle Services (LCS) by visiting lcs.dynamics.com.

Step 2: logging in and choosing the project you want from the provided options.

Step 3: Select  the environments we are interested in connecting to. To view complete information about the project, click on the 'Full Details' option located below the project node.

Step 4: navigate to the "Manage Environment" section where you can find a form to request access.

Step 5: Fill out the information and then click the "Request Access" button. 

Next, allow a couple of minutes to pass before reloading the page

Step 6: Click Ok

Step 7: then click ‘refresh’ on your browser to reload the page.

A newly-added segment titled "Database accounts" should now be visible to you. 

This section provides information on how to connect to a test D365 database.

Add Firewall Rule

Step 1: While the firewall offers an extra level of protection to the database, it's necessary to inform the system that the computer running SSMS is authorized to establish a connection with the database in this scenario.

Step 2: To connect to a database in LCS, navigate to the "Full Details" page of the environment where the database is located and choose "Maintain > Enable Access.

Step 3: A dialog will pop up, and you can click the "+" button to add a new rule. 

Assign any desired value to the 'Name' field and enter the IP address of the virtual machine used to access this database as the designated address.

There are multiple methods available to obtain the public IP address of a computer.

Step 4: you may search my ip in search to obtain the IP address and then copy. Then, enter it into the "Source Address Prefix" field in the dialog. Click the "Confirm" button.

Step 5: Next,  firewall rule will expire automatically after eight hours. Therefore, if you require access beyond that timeframe, you must create a new firewall rule.

Use Microsoft SQL Server Management Studio

The preferred method to connect with a D365 testing database is by using Microsoft SQL Server Management Studio.

By default, SSMS is pre-installed in the cloud-hosted development environment of your LCS project.

After the installation process, launch SSMS and a prompt will appear, requesting the necessary details to establish a connection with the SQL Server Database.

In case the dialog box doesn't appear, you can locate the 'Object Explorer' window and then press the 'Connect>Database Engine' button.

To view the 'Object Explorer' window, go to the menu and select View>Object Explorer.

In the dialog box, set the ‘Server type’ to ‘Database Engine’ and the ‘Authentication’ drop-down to ‘SQL Server Authentication.’ 

We are now prepared to utilize the connection information that was provided to us in LCS.

Copy the ‘SQL Server’ portion of the \ string into the ‘Server name’ field in SSMS.

SQL server \Database name actually contains two pieces of information that are separated out with "Backslash\". 

The initial piece of information denotes the name of the server, followed by a backslash and then the name of the database.

Enter Server name and Login and password.

Find the 'User name' and 'Password' strings in the 'Database accounts' section of LCS. Then, enter these values in the 'Login' and 'Password' fields in SSMS correspondingly.

Next, click the ‘Options’ button.

Enter the ‘Database Name’ portion of the string into the ‘Connect to a database field in the ‘Connection Properties' tab.

In case you encounter an error, you can verify the firewall rule and then click the 'Connect' button.

After adding the firewall rule, attempt to connect to the SQL database again by selecting the 'Connect' button.

Provided that all the steps were executed accurately, the Object Explorer should display both the server and database.

In summary, the ability to establish a connection to a UAT database through SSMS can prove to be a beneficial skill while resolving problems in a TEST setup.

You can rapidly and effortlessly establish a connection with a UAT database and retrieve its data by adhering to the instructions provided in this blog post.