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

[VasylV]

Hi 

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?

[Pstork1]

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.

[VasylV]

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-c...

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

[Pstork1]

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.

[VasylV]

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.

[v-siky-msft]

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

[VasylV]

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.

[v-siky-msft]

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

[VasylV]

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?

[VasylV · Accepted solution]

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.