How to Connect to a Sandbox (UAT) Database in Dynamics 365 Finance & Operations

Microsoft Dynamics 365 Finance & Operations (D365 F&O) is a powerful enterprise solution that helps businesses streamline their operations. However, troubleshooting issues in D365 F&O can be challenging if the root cause isn’t visible on a form.

One of the most effective ways to diagnose problems is by connecting to the UAT (Sandbox) database and querying tables directly.

This blog will walk you through:
How to retrieve SQL connection details from LCS (Lifecycle Services)
How to enable firewall access to allow a secure connection
How to connect to the D365 UAT database using SQL Server Management Studio (SSMS)

Why Connect to the UAT Database?

Diagnose Issues: Querying the database allows you to inspect data and troubleshoot errors that aren’t visible in the front-end UI.
Microsoft-Managed Environments: In sandbox/UAT environments, remote desktop access is restricted, making database queries essential for analysis.
Test Before Deployment: Ensures that all configurations and data changes work as expected before going live.

Step 1: Retrieve SQL Connection Details from LCS

To connect to a D365 F&O UAT database, you must obtain SQL connection details from Lifecycle Services (LCS). Follow these steps:

Go to Lifecycle Services (LCS):

• Open lcs.dynamics.com
• Log in with your credentials.

Select Your Project:

• Choose the project related to the UAT environment you want to access.

Find the UAT Environment:

• Click on the relevant environment.
• Select “Full Details” to view additional settings.

Request Database Access:

• Navigate to Manage Environment.
• Click on the “Request Access” button.
• Wait a few minutes and refresh the page.

Find Database Connection Info:

• After approval, a new section “Database Accounts” will appear.
• This contains the Server Name, Database Name, Username, and Password needed for connection.

Step 2: Enable Firewall Access for Your IP Address

By default, the D365 UAT database is secured behind a firewall. You must add a rule to allow access from your machine.

Go to the LCS “Full Details” page for your UAT environment.
Select: Maintain > Enable Access.
Add a Firewall Rule:

• Click “+” to add a new rule.
• Enter a name (e.g., “My SSMS Connection”).
• Find your public IP address by searching “What is my IP” in Google.
• Copy and paste this IP address into the Source Address Prefix field.
• Confirm & Save the Rule.

Note: The firewall rule expires after 8 hours, so you may need to re-add it later.

Step 3: Connect to the UAT Database Using SQL Server Management Studio (SSMS)

The best tool for connecting to the database is Microsoft SQL Server Management Studio (SSMS).

Launch SSMS and Open the Connection Dialog

• Open SSMS on your machine.
• If the connection dialog doesn’t appear, go to View > Object Explorer and click “Connect > Database Engine”.

Enter Connection Details from LCS

• Server type: Set to Database Engine.
• Server name: Copy the Server Name from the Database Accounts section in LCS.
• Authentication: Select SQL Server Authentication.
• Login: Copy the Username from LCS.
• Password: Copy the Password from LCS.

Set Database Name in Connection Properties

• Click “Options” before connecting.
• Go to Connection Properties.
• In Connect to Database, enter the Database Name from LCS.

Click ‘Connect’ to Establish the Connection

• If successful, you will see the Object Explorer with both the server and database listed.
• If you encounter errors, verify the firewall settings and try again.

Key Takeaway

Direct Access to Data: Enables in-depth troubleshooting by querying database tables directly.
Secure and Controlled Access: LCS-managed firewall rules ensure data security.
Easy Setup: The process takes only a few minutes to complete.

By following these steps, you can quickly and efficiently connect to your D365 F&O UAT database and retrieve critical data for testing and issue resolution.

We hope you found this blog useful, and if you would like to discuss anything, you can reach out to us at transform@cloudfonts.com.

Need help troubleshooting your D365 environment? Let us know in the comments!