BuyLog In
Ticket T1209670
Visible to All Users

Problem with connecting XAF Web App developed with XPO to Azure SQL Server

created a year ago (modified a year ago)

I am developing a Web Service using XAF. Initially, I developed it with XAF's ASP.NET Core Blazor, but since it does not yet support features like Pivot Chart/Table, I switched to Web Forms and continued development.

As ASP.NET Web Forms does not support Entity Framework, I recreated the Data Model using XPO.
When attempting to move the application to Azure App Service and Azure SQL for testing, I encountered an issue with connecting to Azure SQL. (I have a personal paid account, so there are no restrictions on Azure usage.)
Even after trying various options in the ConnectionString, the issue persists.

To investigate the problem, I created a toy project with a simple data model and performed various tests below;

  1. Connecting using SQL Server Explorer in Visual Studio 2022: Successfully connected to Azure SQL.

  2. XAF ASP.NET Core Blazor + EF: Successfully connected to Azure SQL.

  3. XAF ASP.NET Core Blazor / Web Forms + XPO: Encountered Azure SQL connection errors.
    Various trials…
    3-1. "ConnectionString": "Pooling=false;Server=tcp:[server name].database.windows.net,1433;Initial Catalog= [db name];User Id=[id name]@[server name];Password=[password];Connection Timeout=300"
    The error occurred:
    Type: ArgumentException
    message: Keyword not supported: 'xpodatastorepool'.
    …
    InnerException is null

3-2. "ConnectionString": "Pooling=false;XpoProvider=MSSqlServer;Server=[server name].database.windows.net,1433;Initial Catalog= [db name];User Id=[id name]@[server name];Password=[password];Connection Timeout=300"
The error occurred:
Type: UnableToOpenDatabaseException
Message: Unable to open database. Connection string: …
…
Error Number:18456,State:1,Class:14'

3-3. System.Data.SqlClient version update: Not resolved
3-4. Install the latest version of Microsoft.Data.SqlClient: Not resolved
3-5. .NET version change (8 -> 6/7) : Not resolved
3-6. DevExpress version change ( 23.2.3 -> 23.1.7 ) : Not resolved

To rule out the possibility of mishandling Cloud Services, I tested the same issues on AWS RDS SQL Server.
** I successfully connected to AWS RDS SQL Server by adding the options Encrypt=true;TrustServerCertificate=true.
4. SQL Server Explorer in Visual Studio 2022: Successfully connected to AWS RDS SQL Server
5. XAF ASP.NET Core Blazor + EF: Successfully connected to AWS RDS SQL Server
6. XAF ASP.NET Core Blazor / Web Forms + XPO: Successfully connected to AWS RDS SQL Server

However, I failed to connect to Azure SQL Server.
How can I connect the XAF Web App developed with XPO to Azure SQL Server?

Comments (1)
Dennis Garavsky (DevExpress) a year ago

    Hello.

    Have you managed to connect your XAF app to SQL Azure? If not, please check out the following security-related change in Microsoft SQL Server - XPO - UnableToOpenDatabaseException ("The certificate chain was issued by an authority that is not trusted") occurs if the connection string does not contain "Encrypt=False" in the ORM Data Model Wizard/Designer. It is highly likely that it is related to your situation. Thanks.

    Sign in to comment on this post

    Answers approved by DevExpress Support

    created a year ago

    Hello,

    Unfortunately, we are not Azure specialists and can't help you much with such questions. XPO uses a database's related API (like the 'System.Data.SqlClient.SqlConnection' class) to connect to a database and manipulate with data. So, usually, if XPO fails to connect to a database with a connection string, it usually means that a non-XPO connection with the same connection string (like 'new SqlConnection(connetionString);') also fails.

    We have the following thread that describes how to connect XPO to SQL Azure: How to: Connect to SQL Azure. If it doesn't help, try to isolate the issue:

    • Connect a console app with XPO to Azure;
    • Connect an ASP.NET WebForms app with XPO (and without XAF) to Azure;
    • Connect an ASP.NET WebForms XAF app with XPO to Azure.

    Based on the step where the connection fails, you will be able to diagnose the issue further. If your research shows that it is our code that causes the issue, please describe how to reproduce it and we will research it on our side.

    Thanks,
    Andrey

      Sign in to comment on this post

      Disclaimer: The information provided on DevExpress.com and affiliated web properties (including the DevExpress Support Center) is provided "as is" without warranty of any kind. Developer Express Inc disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

      Confidential Information: Developer Express Inc does not wish to receive, will not act to procure, nor will it solicit, confidential or proprietary materials and information from you through the DevExpress Support Center or its web properties. Any and all materials or information divulged during chats, email communications, online discussions, Support Center tickets, or made available to Developer Express Inc in any manner will be deemed NOT to be confidential by Developer Express Inc. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.