1. Hannu Pikkarainen
  2. PowerServer
  3. Wednesday, 8 September 2021 13:11 PM UTC

Hi,

I'm testing PB & Power Server 2021 with MS SQL Server 2019 Express. Both Windows and SQL server authentication are enabled.

I have some odd behaviour and problems while connecting my database from PowerServer (from PB both authentication methods works fine). 

I open database configuration from Web APIs tabpage and edit configured cache.

If Authenication is set to SQL server, testing connection gives a Succesfull response. If I change authentication to Windows, it gives error :

I just discovered that Express version of MS SQL server is not allowed to run SQL agent. Could that be the reason for this? Or do you have any other ideas.

When I try to make the connection from cloud app, WMI API creates the session but resukts then into error:

"AppName": "costallocation",
"NamespaceName": "CostAllocation",
"SecureSession": "eyJ0aW...FwSE5OUU9rckFtQT09In0=",
"Version": "1.0",
"RequestId": "77172B3D-ADD7-4b11-85FB-D691E818B92C",
"Transaction": null
}
}).
dbug: PowerServer[0]
Validate Session Id: '9D550A32-A03A-45EF-B9C9-F9363DA9E2E1' Session Status: 'Created' Session Last Visit Time: '8.9.2021 12.51.58'
fail: PowerServer.Api.ServerApiController[0]
'PowerServer.Api.ServerApiController.ConnectAndCreateTransactionAsync (PowerServer.Api)' get result ({
"IsSuccess": false,
"ErrorCode": 0,
"ErrorMessage": "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SNI_PN11, error: 25 - Connection string is not valid)",
"Version": "1.0",
"RequestId": "77172B3D-ADD7-4b11-85FB-D691E818B92C",
"AppName": "costallocation",
"Session": {
"ErrCode": 0,
"ErrMsg": null,
"SessionId": "9D550A32-A03A-45EF-B9C9-F9363DA9E2E1"
},
"Type": 7,
"Transaction": {
"TransactionId": null,
"SQLCode": -1,
"SQLDBCode": -1,
"SQLErrText": "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SNI_PN11, error: 25 - Connection string is not valid)",
"SQLNRows": 0,

Could this be caused by the not available SQL agent?

BR, Hannu

Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 8 September 2021 14:06 PM UTC
  2. PowerServer
  3. # 1

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

Comment
  1. Hannu Pikkarainen
  2. Wednesday, 8 September 2021 21:49 PM UTC
Does anybody know what does that actual MS SQL Server agent do? Is it a kind of middleman between the client and the server? When do we need it?



BR, Hannu
  1. Helpful
  1. Chris Pollach @Appeon
  2. Wednesday, 8 September 2021 22:55 PM UTC
  1. Helpful
There are no comments made yet.
Francisco Martinez @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 8 September 2021 14:27 PM UTC
  2. PowerServer
  3. # 2

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

Comment
There are no comments made yet.
John Fauss Accepted Answer Pending Moderation
  1. Wednesday, 8 September 2021 22:30 PM UTC
  2. PowerServer
  3. # 3

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?

 

Comment
There are no comments made yet.
Sivaprakash BKR Accepted Answer Pending Moderation
  1. Saturday, 11 September 2021 10:59 AM UTC
  2. PowerServer
  3. # 4

Hannu

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

Comment
  1. Sivaprakash BKR
  2. Saturday, 11 September 2021 11:00 AM UTC
May be the same issue is there in Powerserver too.
  1. Helpful
There are no comments made yet.
Hannu Pikkarainen Accepted Answer Pending Moderation
  1. Monday, 13 September 2021 13:33 PM UTC
  2. PowerServer
  3. # 5

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

Comment
There are no comments made yet.
Kai Zhao @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 14 September 2021 06:43 AM UTC
  2. PowerServer
  3. # 6

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

Comment
There are no comments made yet.
Julie Jiang @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 15 September 2021 08:10 AM UTC
  2. PowerServer
  3. # 7

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

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.