How to set up Server for Microsoft 365 Admin Center Search & Intelligence

Question

I've gone through and created an app registration, created an identity to the SQL Server and assigned Directory Readers permission to the Azure SQL Server (on a VM). Created the contained user and can connect to the SQL Server on the VM via the Client ID and secret via the Microsoft Entra Service Principal Authentication.

However, when attempting to create the Data Source (Microsoft 365 Admin Center) in the Search & Intelligence > Data Source > Azure SQL Database --> I've tried adding the Public IP Address, the DNS to the Server field but to no avail.

How should the Server text be constructed?

I've entered the Server string as: Public IP Address, DNS. Preceded it with tcp:, added port 1433

Answer 1

The format you use for the Server field when adding your Azure SQL Database is crucial. Since you've tried using both the Public IP Address and the DNS, and even went ahead with the technical approach of prefixing with "tcp:" and specifying the port, you're definitely on the right track.

The standard format for this field should indeed start with "tcp:", followed by your server's address (either the DNS name or the IP address), and then, you should specify the port number after a comma. So it looks something like this: tcp:your_server_address,1433.

If you've already followed this format and it's still not cooperating, here are a few things you might want to double-check:

1. Ensure that your Azure SQL Server's firewall is configured to allow connections from the IP addresses that the Microsoft 365 services use. This might involve adding the IP addresses to the allowed list on your SQL Server's firewall settings.
2. In some cases, the full server name (fully qualified domain name) is needed. For Azure SQL, this typically ends in .database.windows.net. Make sure you're using the complete name in the Server field.
3. Sometimes, it's not just about the format but also ensuring all the details in your connection string are correct. Double-check the credentials and other details you're using.
4. Microsoft's documentation often has hidden gems of information. If there's a specific guide for integrating Azure SQL Database as a data source in the Microsoft 365 Admin Center, it might have additional clues.

If after all these checks you're still hitting a wall, it might be worth reaching out to Azure support.