Passwordless connection for Azure SQL in Cayosoft Guardian

Advanced database configuration

Summary

This article provides step-by-step instructions to configure Guardian to use System-Managed Identity (SMI) for connecting to Azure SQL databases.

Prerequisites

1. Cayosoft Guardian is running on an Azure VM.
2. Databases are migrated to Azure SQL:
   1. If only the History database is migrated, skip the Configuration database steps.
   2. If both databases are migrated, ensure they are on the same Azure SQL server.

3. Latest SQL Server Management Studio (SSMS) installed on the Guardian host machine. Download it here.

   TIP: Assume that the database for configuration is called  Cayo.Guardian.Config and the database for change history is called Cayo.Guardian.History".

Configuring Guardian with SMI for Azure SQL

To enable SMI on the Azure VM:

1. Go to the Azure portal and navigate to the VM where Cayosoft Guardian is running.
2. Navigate to Security>Identity.
3. Enable System-Assigned Managed Identity (SMI).
4. Copy the Object ID of the SMI for later use.

 

To configure SMI in Azure SQL:

1. Connect to Azure SQL using Microsoft Entra MFA.
   
   
   

2. Create a Login and User for SMI by executing the following queries on the master database:
   
   
   
   Copy

   CREATE LOGIN [<GuardianVMname>] FROM EXTERNAL PROVIDER
   
   WITH OBJECT_ID = '<objectID>';
   
   CREATE USER [<GuardianVMname>] FROM EXTERNAL PROVIDER
   
   WITH OBJECT_ID = '<objectID>';

3. Replace <GuardianVMname> with a unique name (e.g., Guardian VM name).

4. Replace the Object ID of the SMI.

5. Configure access for Configuration Database (if migrated) by executing the following queries on the Configuration Database:
   
   
   

   Copy

   CREATE USER [<GuardianVMname>] FROM EXTERNAL PROVIDER
   
   WITH OBJECT_ID = '<objectID>';
   
   ALTER ROLE db_owner ADD MEMBER [<GuardianVMname>];

6. Configure Access for History DB (if migrated) by executing the same queries on the History Database:
   
   
   

   Copy

   CREATE USER [<GuardianVMname>] FROM EXTERNAL PROVIDER
   
   WITH OBJECT_ID = '<objectID>';
   
   ALTER ROLE db_owner ADD MEMBER [<GuardianVMname>];

7. Once the login and users for SMI are configured, check if SMSS can connect to the Azure SQL under MSI - connect again with the Microsoft Entra Default authentication.

   

To update Cayosoft Guadian configurations:

1. Open Services (services.msc).

2. Locate and stop the Cayosoft Guardian service.

3. Navigate to C:\Program Files\Cayo Software\Guardian\.

4. Make a copy of appsettings.json for backup.

5. Open appsettings.json in Notepad as Administrator to edit appsettings.json.

6. Locate the DatabaseSettings section.

7. Update Connection Strings for Configuration Database (if migrated):

   • Remove User ID and Password from the ConnectionString.

   • Add Authentication=Active Directory Default.

   Example:
   
   

   Copy

   "ConnectionString": "Data Source=<your-server-name>.database.windows.net;Initial Catalog=<your-configuration-database-name>;Authentication=Active Directory Default;Encrypt=True"

   
   Here, Data Source is your Azure SQL server, Initial Catalog is your configuration database name. In case your History Database was migrated, add one more connection string called 'HistoryConnectionString'.

8. For History Database (if migrated) add a new HistoryConnectionString with the same format:
   
   

   Copy

   "HistoryConnectionString": "Data Source=<your-server-name>.database.windows.net;InitialCatalog=<your-history-database-name>;Authentication=Active Directory Default;Encrypt=True"

9. The final DataBaseSettings (if both Configuration Database and History Database were migrated) section should look like this:
   

   Copy

   "DataBaseSettings": { "ConnectionString": "Data Source=<your-server-name>.database.windows.net;Initial Catalog=<your-configuration-database-name>;Authentication=Active Directory Default;Encrypt=True;
   "HistoryConnectionString": "Data Source=<your-server-name>.database.windows.net;Initial Catalog=<your-history-database-name>;Authentication=Active Directory Default;Encrypt=True" }

10. Start Cayosoft Guardian service. In case it fails to connect to the databases, stop Cayosoft Guardian service, restore appsettings.json and start the service again.