Enhanced Microsoft SQL Server connector cannot authenticate to On-Premise Sql Server

(0) Share

Report

Posted on by VasylV

I have switched on "Allow Enhanced Microsoft SQL Server connector" and after this my app stopped working.

The app uses On-Premise Sql Server with Sql Server authentication.
All requests to data source returns:

{
 "duration": 155.77,
 "size": 237,
 "status": 401,
 "headers": {
 "Cache-Control": "no-cache,no-store",
 "Content-Length": 237,
 "Content-Type": "application/json",
 "Date": "Wed, 27 Nov 2019 10:51:50 GMT",
 "expires": -1,
 "pragma": "no-cache",
 "strict-transport-security": "max-age=31536000; includeSubDomains",
 "timing-allow-origin": "*",
 "x-content-type-options": "nosniff",
 "x-frame-options": "DENY",
 "x-ms-apihub-cached-response": true,
 "x-ms-request-id": "7b74b9f9-33a2-4af5-8bb9-1bd6b952c94a",
 "x-ms-user-agent": "PowerApps/3.19111.33 (Web AuthoringTool; AppName=4a938564-807e-49cb-bb49-eea971aa03d4)",
 "x-ms-client-request-id": "7b74b9f9-33a2-4af5-8bb9-1bd6b952c94a"
 },
 "body": {
 "status": 401,
 "message": "Permission Exception.\r\n inner exception: Internal error: dataSourceCredentials\r\nclientRequestId: 7b74b9f9-33a2-4af5-8bb9-1bd6b952c94a",
 "source": "sql-eus2.azconn-eus2.p.azurewebsites.net"
 }
}

When switch that feature off the app works fine - there is no problem with data sources.

Should I configure something else to make it works with "Enhanced Microsoft SQL Server connector"?
Does "Enhanced Microsoft SQL Server connector" works with On-Premise Sql Server?

Categories:

Building Power Apps

I have the same question (0)

All responses (10)

Answers (1)

Pstork1 68,697 Most Valuable Professional on at

Like (0)

Report

Two things.

1) When you turn on that switch in settings it should give you an error if you have any existing SQL connectors in the App. That switch can only be turned on when the app doesn't already have SQL connectors. Did you remove and re-add the connectors after turning that feature on?

2) The enhanced connector adds support for Azure AD accounts, which doesn't mean anything for an on-premises installation. So I don't think its designed to work with on-premises SQL.

What enhancement are you hoping to get from the connector? All the enhancements are designed for a cloud environment.

Was this reply helpful? Yes No
VasylV 30 on at

Like (0)

Report

Thank you for your answer.

1. Yes I have removed all data sources before turning that feature on then recreated them, otherwise it doesn't allow switch that flag

2. Enhanced connector also adds support timezone independent support for datetime and datetime2 which we needs as we develop on azure sql server but deploy to On-Premise sql server.

Also I assume that Enhanced connector is new version of the current connector and it just brings new feature and eventually replace old one. Isn't my assumption correct?
There is no information that it is design only for cloud and doesn't work with On-Premise sql server.
This more looks like bug in the connector.

Actually there is no much information about Enhanced Connector all I can find is this blog post https://powerapps.microsoft.com/en-us/blog/azure-active-directory-authentication-in-the-sql-server-connector/

I would appropriate if you point me on some other documentation.

Was this reply helpful? Yes No
Pstork1 68,697 Most Valuable Professional on at

Like (0)

Report

I agree there isn't much information. Its my understanding it really only adds two things, Azure AD logins and timezone independent support. Neither of those mean anything outside Azure. I'm not aware of any improvements to the underlying connector.

Was this reply helpful? Yes No
VasylV 30 on at

Like (0)

Report

I think it would be really useful to have consistent support datetime for both On-Premise and Azure Sql Server.
I hope PowerApps team will fix Enhance Connector to work with On Promise Sql.

Was this reply helpful? Yes No
v-siky-msft on at

Like (0)

Report

Hi @VasylV ,

Can you manage to create the connection? Which request did you call from the SQL? Could you share more codes or screenshots?
I have made a test on my side, but I can't reproduce your issue. I can manage to connect to On-premise SQL DB by SQL server or window authentication after I switched on "Allow Enhanced Microsoft SQL Server connector" .
I recommend you to delete the connection, and change window authentication to re-create the connection again. If this doesn't make sense, please post more details.
Best regards,
Sik

Was this reply helpful? Yes No
VasylV 30 on at

Like (0)

Report

Yes I created connection to On-premise SQL DB using Sql Server authentication
and it works fine with PowerApps when "Allow Enhanced Microsoft SQL Server connector" is off.
But when switch the option on it stop working - all requests to DB contains authentication error.
This happens also in environment of our customer so this is not problem with my environment only.

Unfortunately I cannot check Windows Authentication my machine is no in domain and is doesn't work.

Was this reply helpful? Yes No
v-siky-msft on at

Like (0)

Report

Hi @VasylV ,

First, this feature does work with On-Premise SQL Server, and it works fine on my side, so I believe something weird in your SQL connection.
Then, the error shows Status 401 error. Generally, status 401 means the Unauthorized Error, so my suggestions are as follows:
1. Remove all possible sql conections, which are linked to the app. Take care. The SQL Connection should all be deleted. Sometimes the System hangs and does not clear a sql connection.
2. Uninstall On-premises data gateway,
3. After this step, take a little break. Maybe the background needs some time.
4. Clear Browser cache, re-install the gateway, add the connection threw the Power Apps Portal (View/Connection/Add Connection).
5. Create a new canvas app, connect to SQL database and check if your issue still remain.
Best regards,
Sik

Was this reply helpful? Yes No
VasylV 30 on at

Like (0)

Report

Hi @v-siky-msft

Thank you for your answer.

You are right it works with new application fine but I still cannot make it work for my existing application.
I have tried to remove and recreate all datasource and this haven't solve the problem.
Also I have gone through application settings and change them to default but this doesn't fix that problem.

Any ideas what else can be wrong with my app?

Was this reply helpful? Yes No
Verified answer

VasylV 30 on at

Like (0)

Report

I have resolve this issue with existing app - the problem was in these 2 settings, they were off in my app.
After I have switched them on and then removed and added again all Sql Server Data Source it starts working correctly with On-Premise Sql Server with Enhanced Sql Connector.

Looks like some bug in PowerApps Studio - it should warn that this settings also should be on.

Was this reply helpful? Yes No
v-siky-msft on at

Like (0)

Report

Hi @VasylV ,

Glad to hear that you are able to resolve it. These preview feature may also be an obstacle in our way.
Please click "Accept as Solution" if the post answered your question so that others may find it more quickly.
Best regards,
Sik

Was this reply helpful? Yes No