As our readers are probably already aware, Remote Desktop Protocol (RDP) access has been removed  from environments, and you will no longer be able to RDP into the environment virtual machines from Lifecycle Services (LCS). This also means these virtual machines can no longer be used to connect to the database which is useful for ad-hoc queries and troubleshooting. With these changes, we have to use the just-in-time (JIT) database access which allows you to create a new type of temporary firewall rule in LCS so you can connect from anywhere.

In the following steps, we will be connecting to the database using SQL Server Management Studio (SSMS). In case you need a link to download a copy, you can find it here. I’ve included walk throughs for adding JIT database access for both Microsoft managed and self-service environments. You can determine which type you have from the environment details screen for that environment in LCS.

Adding JIT database access in a Microsoft managed environment:

  1. From your LCS project, click Full details on the non-production environment you want to add JIT database access to:

  2. Click on the Maintain drop down menu and then click Enable access.



  3. Click the AXDB database from the database drop down list.
  4. Enter the IP Address where your connection will originate from (in my case, I will be connecting from my local desktop using SQL Server Management Studio) and then click Confirm.

  5. After it processes, you will get a notification that the action was completed successfully. Note that this access expires in 8 hours, so you will have to request this access each time you need JIT access to the database after it expires. This access will also be overwritten if the database gets replaced by a database movement operation.


Click OK to close out of the notification where you will see a firewall rule screen and your new database access rule with expiry date. Click Close.

  1. You can now connect to the database from the IP address that you entered above. I will walk through how to connect from SQL Server Management Studio but first we need to locate the server, database, and user login information from LCS.

    From the LCS environment details screen, scroll down to the Database Accounts section where you will find a list like below. We will be referencing this information in the next steps.



    Launch SQL Server Management Studio and select Database Engine for the server type. Then select SQL Server Authentication for the authentication option and enter the desired username as the Login and the Password from LCS.

    From the Database Access screen in LCS, expand the SQL Server\Database Name column to fully view the server and database information. We’re looking for the AXDB database which has the ‘(AXDB)’ at the end of the database name.



    Everything before the ‘\’ is the server name and everything between the ‘\’ and ‘(AXDB)’ is the database name. Do not include the ‘\’ or ‘(AXDB)’ text when copying and pasting the server or database names.

    For the server name, you must add the .database.windows.net domain name to the server you copy from the above location if connecting from the public space. It will look like ‘spartan-srv-nam-d365opsprodx.database.windows.net’, except it will be different with your server name.



    Click the Options button and then the Connection Properties Enter the database name from LCS into the Connect to database field (remember not to include the ‘\’ or ‘(AXDB)’ text in the database name).



  2. Click Connect and you should successfully connect and see the database in the Object Explorer.

 

Adding JIT access in a self-service environment

If you happen to be a newer customer to F&O, your environment type may be self-service instead of Microsoft managed. Enabling JIT database access to these environments is like Microsoft managed environments, but there is an additional step where you must request JIT access from the environment details page above the database access section. This additional step also creates your database access credentials that you will use to access the database.

  1. To enable JIT database access in a self-service environment:

    From your LCS project, click Full details on the non-production environment you want to add JIT database access to:

  2. Click on the Maintain drop down menu and then click Enable access.



  3. Click the ‘+’ icon to add a new firewall rule. AzureSQL should be selected as the Service. Next, enter a Name for the rule, and enter the IP address you will be connecting from in the Source address prefix Then click Confirm.



  4. After it processes, you will get a notification that looks like the screenshot below. Note that this access expires in 8 hours, so you will have to request this access each time you need JIT access to the database after it expires. This access will also be overwritten if the database gets replaced by a database movement operation.

    Click OK to close out of the notification where you will see a firewall rule screen and your new database access rule with expiry date. Click close.



  5. You should be back at the Manage environment screen in LCS. Scroll down to the Database Accounts section to request JIT database access.



    Select a Reason for access (read vs write access is noted next to the option) and enter a note on why you are requesting the access in the Details text box. Click Request Access and you will receive the following notification. Click

  6. Your new credentials to access the database will populate under the Database Access section. These credentials also expire after 8 hours or if the database gets replaced by a database movement operation. This also has the SQL Server name and database name needed in future steps.



  7. Once you have your credentials, you can now connect to the database from the IP you entered in the firewall rule created earlier. I will walk through how to connect from SQL Server Management Studio.

    Launch SQL Server Management Studio and select Database Engine for the server type. Then select SQL Server Authentication for the authentication option and enter the desired username as the Login and the Password from LCS.

    From the Database Access screen in LCS, expand the SQL Server\Database Name column to fully view the server and database information.



    Everything before the ‘\’ is the server name and everything after the ‘\’ is the database name. Do not include the ‘\’ text when copying and pasting the server or database names.

    For the server name, you must add the .database.windows.net domain name to the server you copy from the above location if connecting from the public space. It will look like ‘spartan-srv-nam-d365opsprodx.database.windows.net’, except it will be different with your server name.



    Click the Options button and then the Connection Properties Enter the database name from LCS into the Connect to database field (remember not to include the ‘\’ text in the database name).



  8. Click Connect and you should successfully connect and see the database in the Object Explorer.

 

You can click here for a link to the JIT database access documentation.

Thanks for reading and please drop a comment to let me know what you think. Let’s connect on Twitter or Linkedin (links are in my profile) and I hope you share or like my blog if this was helpful. If you have any questions or suggestions for blogs you’d like to see in the future please feel free to leave your suggestions below.