To homepage To homepage
Solved

Using App Registration for Authentication on Azure SQL DB

  • 27 February 2024
  • 5 replies
  • 170 views
J
Rank

I was looking into using a managed identity (App Registration) to authenticate TimeXtender with Azure SQL DB resources, but can't get it to work. I couldn't find much documentation on the support site either.

I created a login on the database using T-SQL. I can also login with SSMS using the Azure Active Directory - Service Principal method:
 

I'm trying by using the Active Directory Password Authentication method in the Connection Settings dialog.
 

Testing the data source in TimeXtender gives an non-specific error:

Service request failed: One or more errors occurred. (System.Exception)

Details:

Service request failed: One or more errors occurred. (System.Exception)
Module: TimeXtender.ODX.Engine
TimeXtender.ODX.Engine.ODXFaultException
   at TimeXtender.ODX.Engine.ODXEngine.SendServiceRequest[C,T](WcfServerSettings serverSettings, Func`3 func)
   at TimeXtender.DataManager.EditDataSourceCommand.<Dialog_OnTestConnection>b__12_0()
   at TimeXtender.DataManager.ConnectingThread.ExecuteConnectingThread(Object dummy)

Service request failed: One or more errors occurred. (System.Exception)
Module: timeXtender
TXModelInterface.ExceptionWrapperException
   at TimeXtender.DataManager.ConnectingThread.HandleError()
   at TimeXtender.DataManager.ConnectingThread.Execute(String title, Int32 progressSteps, List`1 actions)
   at TimeXtender.DataManager.ConnectingThread.ExecuteFastAction(String title, Action action, IWin32Window parentForm, CancelBehaviors cancelBehavior, ErrorBehaviors errorBehavior, String callerName)
   at TimeXtender.DataManager.EditDataSourceCommand.Dialog_OnTestConnection(Object sender, EventArgs e)

Has anyone got this to work?

icon

Best answer by rory.smith 28 February 2024, 10:32

View original

5 replies

rvgfox
Rank
Userlevel 3
Badge +1

@JogchumSiR I’m using “SQL Server Authentication” because the DB it is not in the domain...

J
Rank

@rvgfox
Thnx for the suggestion. We are using SQL Server Authentication now and it's working fine but i want to increase security since SQL Server Authentication is the lesser secure one and our standard is using windows authentication at least.

For on-premise sources normal windows auth is working fine because we have the ODX Service running under a domain account and we have given the required permissions to that domain account. However, for Azure SQL DB sources we need to use Azure Active Directory and that has different behavior than normal AD and I can't get that to work currently...

rory.smith
Rank
Userlevel 5
Badge +7

Hi @JogchumSiR ,

TimeXtender does not support Entra - Service Principal auth (yet) as far as I know. Note that Azure SQL DB does not mention it in its own documentation: https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-overview?view=azuresql . Note here that database principals and server principals are not the same as service principals.

On the generic SQL 16 / 2022 documentation (https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/azure-ad-authentication-sql-server-overview?view=sql-server-ver16&viewFallbackFrom=azuresqldb-current) we find: 

  • Only SQL Server 2022 (16.x) on-premises with a supported Windows or Linux operating system, or SQL Server 2022 on Windows Azure VMs, is supported for Microsoft Entra authentication.

This is rather confusing to parse as you obviously demonstrate this working from SSMS. I think it would be good if TX supported the Service Principal and Managed Identity options for those resources that support this. I know it is possible to use AAD / Entra password in combination with Entra accounts that have been excepted from MFA to use them as service accounts.

J
Rank

Thnx @rory.smith for the extensive explanation. Maybe @Christian Hauggaard can tell us if this kind of authentication is already somewhere on the roadmap?

For now i will try and look into the last option you mentioned if i can get that to work.

rory.smith
Rank
Userlevel 5
Badge +7

Hi @JogchumSiR ,

 

coincidentally there is something new in preview for Azure SQL DB : https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-microsoft-entra-create-users-with-nonunique-names?view=azuresql  this allows you to specify a login from an object id which may allow you to use pass auth. I have not tried and this is preview functionality, but it may actually work without needing TX to change anything.

Reply


Terms and ConditionsCookie settings