There are times when something is not working as expected in a Microsoft Dynamics D365 F&O environment and it would helpful to look at the underlying data that may not be visible on a form. In this article I will show you how to you can connect to a test D365 database. And how to enable just-in-time database access.
Why Connect to A Test D365 Database?
Once you have connected to a D365 SQL database, you can query tables in that database, and hopefully understand what may be causing your issue in the Test environment.
Perhaps the data you expected is not showing in a form. Or perhaps a batch job is not processing records in a staging table because the records are not in the correct status.
Often times, there is information that underlying form code is looking at that you is not necessarily shown on the front end form. Therefore, being able to query the tables directly can be very helpful to someone troubleshooting the issue.
How Is This Different Than A Cloud Hosted Dev Environment?
In a Microsoft managed sandbox, test, and acceptance testing environments, you are not allowed to remote desktop into the machine. Therefore instead, you need to use sql management studio from another machine to connect to a test D365 database. In order to do this, you will need two things.
- You will need to know the sql connection information to be able to connect to a test d365 database from SQL Management Studio.
- You will need to given access to connect to the database.
Microsoft Documentation
Microsoft recently published an article on the steps to connect to a test D365 database. And you can find those instructions here. However, I thought it would still be good to explain this process in more detail, step by step.
Retrieve The Connection Information from LCS
In order to get the SQL connection information needed to connect to a test D365 database, you will first need to open Life Cycle Services (LCS). Go to lcs.dynamics.com.
Sign-in. Next, select the desired project from the list.
On the main screen, you may see a list of Microsoft hosted Sandbox, Test, or Acceptance Test environments. These are the environments we are interested in connecting to this way. Unlike Cloud hosted environments, these environments cannot be remoted into. And so you cannot access their database directly. Instead, there are some additional steps.
Click on ‘Full Details’ underneath the project node.
Once you are on the details page, you will need to request access. Under the first section, Manage environment, you will see the following form to request access.
Fill out the information, and then click the ‘Request Access’ button.
Afterwards, you will receive a dialog that looks like this.
Click Ok to dismiss the message.
Afterwards, wait a few minutes, then click ‘refresh’ on your browser to reload the page. You should now see a new section labeled ‘Database accounts’. This section provides the information on how to connect to a Test D365 Database.
Install Or Use Microsoft SQL Server Management Studio
You will need this connection information in just a minute. But first, it would be helpful to know where to put each piece of information.
In order to connect to a Test D365 database, the most common tool to use is Microsoft SQL Server Management Studio. You can download and install this tool for free. You can run it on your own local machine. Or, if you have a Cloud Hosted Development environment in your LCS project, SQL Server Management Studio (SSMS) is installed by default.
After installing or locating the program, start SQL Server Management Studio (SSMS). You will be prompted with a dialog that looks like this.
This dialog is asking for the information needed to connect to the SQL Server Database. The great thing is that the database does not need to be installed on the same machine as SSMS. You only need to make sure the computer running SSMS has access to connect to the SQL Server Database.
If you do not see the above dialog, look for the ‘Object Explorer’ window. Then click the ‘Connect>Database Engine’ button. If you do not see the ‘Object Explorer’ window, select View>Object Explorer from the menu.
In the dialog shown, set the ‘Server type’ to ‘Database Engine’. And set the ‘Authentication’ drop down to ‘SQL Server Authentication.’
Connect To A Test D365 Database
Now, we are ready to use the connection information we were given in LCS.
First, the SQL Server\Database Name column. This actually contains two pieces of information that is helpful to separate out. The text is in the format of <SQL Server>\<Database Name>. There is a backslash between the two pieces of information.
Copy the ‘SQL Server‘ portion of this string into the ‘Server name‘ field in SSMS.
Next, click the ‘Options‘ button. The dialog will change to show more tabs. The ‘Connection Properties‘ tab will automatically be selected. Enter the ‘Database Name‘ portion of the string into the ‘Connect to a database‘ field.
Click the ‘Options <<‘ button again to return to go back to the original dialog view. Or click on the ‘Login’ tab.
Secondly, locate and copy the ‘User name‘ string from the ‘Database accounts‘ section in LCS. Copy this value into the ‘Login‘ field in SSMS.
Thirdly, locate and copy the ‘Password‘ string from the ‘Database accounts‘ section in LCS. Copy this value into the ‘Password‘ field in SSMS.
The dialog form should now look like this. You can optionally check the ‘Remember password’ checkbox. This will save the password so that if you close and reopen SSMS you may not need to enter in the password again.
Finally, we are ready to connect to a Test D365 database. We have entered all of the required information. Click the ‘Connect’ button.
Surprise! There is a good chance that you will receive this error message.
This message means that even you have entered the SQL Server access information, there is still a firewall that is preventing you from accessing the database. Let’s look at how to correct this next.
Add an inbound allow rule
The firewall provides an added later of security to the database. Which is a good thing. But in this case, we need to tell the environment that it is ok for the computer running SQL Server Management Studio to connect to the database.
In LCS, on the ‘full details’ page of the environment that contains the database you wish to connect to, select ‘Maintain>Enable access‘.
A dialog will pop up that will look like this. If you are interested in more in depth information about the environments, you can find it here. Click the ‘+‘ button to add a new rule.
Another dialog will be shown that will look like this.
The ‘Service’ field will be automatically be set to ‘AzureSQL’
Set the ‘Name’ field to anything you would like. This is a description of what this firewall rule is for. So it could be ‘Peter’s Work Computer’. It can be any text.
Set the ‘Source address prefix‘ to the public IP address of the computer running Microsoft SQL Server Management Studio.
There are many ways of retrieving the public IP address of the computer. I recommend opening an internet browser, and then going to https://whatsmyip.com/
In the header you will see “Your Public IP Address <IP Address>”. Copy the IP address, and enter it into the ‘Source address prefix‘ field, in the dialog. The dialog should now look similar to this. Click the ‘Confirm’ button.
Next, you will get a message similar to this. The system is letting you know that the Firewall Rule you have setup will automatically expire after 8 hours. If you need access after this time, you will need to setup the Firewall Rule again.
In the underlying dialog, you will now see that your firewall rule has been added.
Connect Again
Finally, you can try connecting to the SQL database again in SSMS by clicking the ‘Connect’ button again. If you did everything correctly, you will see the server and database in the Object Explorer. You can expand the database node to view the tables and fields. You can also click the ‘New Query’ button to open a SQL code editor. Using SQL, you can query the data in the tables contained in the database.
Conclusion
If you are experiencing an issue in a TEST D365 environment, often times you may need a backup to be taken of the database. Then the database needs to be restored to a Development environment so a developer can debug the code to understand what is happening. These steps take time. Sometimes a person can look at the underlying data and understand the issue without performing all of these steps. Also certain data issues can be resolved with direct access to the database. So knowing how to connect to a TEST D365 Database can very helpful.
Thanks for your valueable information.
Thank you! The firewall rule in LCS did it for me, i forgot that one.
Is there a way to connect to a dev box SQL DB from another external machine ?