Hi all,

Sorry for the inconvenience caused. The issue is due to a product bug in PowerServer 2021.  If you are connecting to SQL using Windows Authentication, please manually make the following two changes, then the connection will be successful:

1. Set the Integrated Security to True in the Database Configuration > Advanced settings.

2. In the Web API solution deployed from the PowerServer project, find the AppConfig > Applications.json file in the ServerAPIs project, and modify the value of OtherOptions to: “Integrated Security=True”.

Best regards, Julie

Hi Hannu,

For the Windows Authentication:
Thanks for reporting the issue, we will set Integrated security to true when Windows Authentication is selected to avoid the issue.
BTW, sorry to let you know that though the configuration here supports Windows Authentication, PowerServer doesn’t support it, but we will consider supporting it in the future.

For the connection issue:
Please note that PowerServer supports direct connection to SQL Server through Native Client, OLE DB, ADO.NET but not ODBC driver.

If there is no transaction-to-cache mapping configured for the app, either statically or dynamically, you can make direct connections with the databases from the application client. Please refer to the article below for details.
https://docs.appeon.com/ps2021/Making_database_connections_from_the_app_client.html

Although it is still supported to connect to the database using the database connection information specified in PowerScript, you have securer options. That is, implement the connection via connection caches:

• Create the database caches, and then specify which cache each transaction object shall use for connecting to the database in the PowerServer project. Or,
• Create the database caches, and then specify which cache each transaction object shall use for connecting to the database in PowerScript.

Please refer to the article below for details.
https://docs.appeon.com/ps2021/working_with_database_connections.html

Using database caches, you can well protect the sensitive database connection information, because it is no longer necessary to keep the database connection information (including user ID, password, and DB connection string, etc.) at the client side.

If the issue remains, please provide a simple PB case to reproduce the issue for more study, thanks.

Regards
ZhaoKai

Hi, 

I found two things causing problems.

1. Even though the TCP-IP was enabled with SQL server, the default port was empty after installation:

2. On PowerServer if you want to use Windows authentication, you need to use Advanced setting 

and enable Integrated security

With these corrections I was able to connect SQL server from DBeaver and PowerServer interface.

But my final problem still exist. I can run my app from PB IDE and it connects to SQL server through ODBC without problem (Using both Wndows authentication and SQL server authentitcation)

But when I try to run the same app through PowerServer with same ini-file, no go.

Should I use MSOleDBSQL-driver instead of ODBC or what else could be wrong.

BR, Hannu

Hannu

Can u try in the installation without instance name ?   Snapdevelop could not connect to an sqlserver with instance name.   

Hi, Hannu -

Why do you think this issue has something to do with the SQL Server Agent feature? I see no evidence of that in the information you've posted. There are many features of SQL Server that are disabled in the Express Edition.

SQL Server Agent can be used for several things, but it is primarily intended as a means of scheduling and executing jobs in SQL Server.

Here is a link to a comparison of the various SQL Server editions for 2019:

https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-version-15?view=sql-server-ver15

The Express Edition is intended primarily for individual instruction and learning purposes. You may need to upgrade your SQL Server 2019 to an edition with greater/more capabilities and features. Does Appeon state any edition limitations for using PowerServer with SQL Server databases?

Hi Hannu,

To ensure that Windows Authentication works properly, please download another utility like DBeaver and ensure you can use Windows Authentication from there. If you can, then please proceed to open a Support Ticket about this issue.

Regards,
Francisco

Hi Hannu;

   I have the same restriction on my test PC's. Even though I was able to start the "SQL Agent", the same error occurs as you are reporting. However, using "SQL Authentication" with a UserID & PWD, works great. You could be correct on the "Express" version's limitations.

Regards ... Chris