Monitor Azure SQL Database using Service Principal

hcuk94

Looking to monitor a PaaS SQL Azure Server/Database, but the only option for authenticating looks to be SQL Authentication, is that right.
We have this off currently in line with MS Recommendations, is there any way we can connect using an Azure Service Principal instead?

Thanks!

Alex B

Hi @hcuk94 ,

Currently there is not a way to do this other than providing the server admin created when creating the Azure SQL Server the database resides on using SQL Auth. 

One of the teams will be looking into authenticating to the entities using Azure AD in the near future and so this may be considered in that and I've mentioned this post to them.

You should also vote on the Uservoice suggestion https://sqlmonitor.uservoice.com/forums/91743-suggestions/suggestions/44685457-provide-aad-authentication-mode-to-connect-to-azur and comment any specifics like the use of Azure Service Principals as well.

Kind regards,

Alex

hcuk94

Thanks @Alex B - frustrating as this goes against Microsoft's security best practices and will require us to weaken our Azure SQL security posture in order to monitor the server.

We don't currently have a server admin account since the server is AAD auth only. I will need to reconfigure it and create an admin account.

In the meantime I will upvote the uservoice suggestion - thanks!

Alex B

Hi @hcuk94,

The team are planning to implement Azure Active Directory Password and Azure Active Directory Integrated Authentication methods.  Looking through some information on the Azure Service Principal and from what you've said, I think that may cover your need, but I'm not well versed with it, so do let me know if that is not the case - and if not, what the use case of that is over the two methods we are planning to implement.

Kind regards,
Alex

hcuk94

hi @Alex B

Thanks for your help earlier in the year. We've updated to the versions which now include AAD Password and Integrated authentication methods, but this still doesn't allow for Service Principal auth to be used.

In the ODBC driver this is achieved using the Authentication=ActiveDirectoryServicePrincipal type as referenced here: https://learn.microsoft.com/en-us/sql/connect/odbc/using-azure-active-directory?view=sql-server-ver16

I also note that RedGate has implemented Service Principal auth in Flyway through the JDBC driver (https://documentation.red-gate.com/fd/sql-server-184127608.html) - it would be great if the same could be done for SQL Monitor.

To expand on the Service Principal authentication, this is Microsoft's advised way to create a service account for situations such as this. Their docs explain more here: https://learn.microsoft.com/en-us/azure/active-directory/architecture/govern-service-accounts

The key paragraph: "We do not recommend user accounts as service accounts because they are less secure. This includes on-premises service accounts synced to Microsoft Entra ID, because they aren't converted to service principals. Instead, we recommend managed identities, or service principals, and the use of Conditional Access."

If Redgate could consider SP auth as a future enhancement that would be great. If the Microsoft drivers are used then it should be an easy implementation.

Thanks!
Henry

Alex B

Hi Henry,

Thank you for following up here and apologies that the functionality which was implemented didn't help in this case. 

I've fed this back to the team for consideration and you should also raise it on the SQL Monitor Uservoice here https://sqlmonitor.uservoice.com/forums/91743-suggestions/filters/top 

Kind regards,
Alex