Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

How to set up this in AAS #22374

Closed
sgiovinetti opened this issue Jan 14, 2019 — with docs.microsoft.com · 10 comments

Comments

Copy link

@sgiovinetti sgiovinetti commented Jan 14, 2019 — with docs.microsoft.com

Is there a way to setup the Application intent in AAS Direct Query connection to SQL?


Document Details

Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

@Mike-Ubezzi-MSFT

This comment has been minimized.

Copy link
Contributor

@Mike-Ubezzi-MSFT Mike-Ubezzi-MSFT commented Jan 14, 2019

@sgiovinetti Thank you for your interest in Azure cloud services. I am investigating this and will update you when I have more information.

@Mike-Ubezzi-MSFT

This comment has been minimized.

Copy link
Contributor

@Mike-Ubezzi-MSFT Mike-Ubezzi-MSFT commented Jan 14, 2019

@sgiovinetti It looks like the Azure SQL Database connection string property ApplicationIntent is a Read Scale-Out property and not applicable to Azure Analysis Services, however, if you are connecting to a SQL data source that is a Read Scale-Out deployment that is supporting Azure Analysis Services DirectQuery Tabular data models, and the connection string is dictated by the client in one of the formats that is outlined here: Connect to a read-only replica, it might be possible but, in almost all cases, DirectQuery is applicable only when you are connecting to Azure Analysis Services. Do you have an example connection string you are using for your solution?

@sgiovinetti

This comment has been minimized.

Copy link
Author

@sgiovinetti sgiovinetti commented Jan 15, 2019

@Mike-Ubezzi-MSFT: yes my scenario is exactly what you have described "you are connecting to a SQL data source that is a Read Scale-Out deployment that is supporting Azure Analysis Services DirectQuery Tabular data models".

My question is how to create the correct connection string in AAS. In AAS I have the following. I tried inserting the json applicationintent property ("applicationIntent": "Readonly") inside the "address" and "connectionDetails" json object but without success. The update command runs successfully but then as soon as you browse the AS model you receive an error of type Login Failed.

Any clue how to modify this json for make it run?

{ "create": { "parentObject": { "database": "XXXX" }, "dataSource": { "type": "structured", "name": "SQL/XXXX database windows net,1433;XXXX", "connectionDetails": { "protocol": "tds", "address": { "server": "YYYY.database.windows.net,1433", "database": "XXXX" }, "authentication": null, "query": null }, "options": { "connectionTimeout": "P150D" }, "credential": { "AuthenticationKind": "UsernamePassword", "kind": "SQL", "path": "YYYY.database.windows.net,1433;XXXX", "Username": "ZZZZ", "EncryptConnection": true } } } }

@Mike-Ubezzi-MSFT

This comment has been minimized.

Copy link
Contributor

@Mike-Ubezzi-MSFT Mike-Ubezzi-MSFT commented Jan 15, 2019

@sgiovinetti Thank you for confirming the scenario. Let me work with a few individuals internally to see if there is a way to accomplish this.

@Mike-Ubezzi-MSFT

This comment has been minimized.

Copy link
Contributor

@Mike-Ubezzi-MSFT Mike-Ubezzi-MSFT commented Jan 15, 2019

@sgiovinetti Can you provide additional details, including the specific steps that were executed along with commands passed, that support this statement:

The update command runs successfully but then as soon as you browse the AS model you receive an error of type Login Failed.

I have the product group engaged and we are looking for specific details. Also, can you or have you attempted to us Azure Active Directory with your solution? Is that possible to set-up and retry? This is a big ask I know, but if there is any chance to try this...thanks.

@sgiovinetti

This comment has been minimized.

Copy link
Author

@sgiovinetti sgiovinetti commented Jan 16, 2019

@Mike-Ubezzi-MSFT : yes we are using AAD for AAS and DB Account for SQL. It is not possible to use AAD in SQL. This is a production DB.
We discovered the issue about Login Failed but now looks like the model is not reading from the read-only replica. Do you know how to check this from AAS?

Here I reproduce the steps we did.

  1. We open the SQL Server management studio and connect to the AAS using AAD account that is admin of AAS.

  2. We expand the connection folder and we click over the SQL connection entry.

  3. We script the connection as a "Create or replace" and we obtain the following json

{ "CreateOrReplace": { "parentObject": { "database": "XXXX" }, "dataSource": { "type": "structured", "name": "SQL/XXXX database windows net,1433;XXXX", "connectionDetails": { "protocol": "tds", "address": { "server": "YYYY.database.windows.net,1433", "database": "XXXX" }, "authentication": null, "query": null }, "options": { "connectionTimeout": "P150D" }, "credential": { "AuthenticationKind": "UsernamePassword", "kind": "SQL", "path": "YYYY.database.windows.net,1433;XXXX", "Username": "ZZZZ", "EncryptConnection": true } } } }

  1. We modify the json inserting the applicationIntent in the address json schema (we guessed that because there is no documentation on that: this is what we expect from your side. We added also the Password item at the end. We discovered that this was generating the issue about the Login Failed. It seems that the script connection in SQL Management Studio somehow remove this part probably for security reasons. So finally the script result this way.

{ "CreateOrReplace": { "parentObject": { "database": "XXXX" }, "dataSource": { "type": "structured", "name": "SQL/XXXX database windows net,1433;XXXX", "connectionDetails": { "protocol": "tds", "address": { "server": "YYYY.database.windows.net,1433", "database": "XXXX", "applicationIntent": "ReadOnly" }, "authentication": null, "query": null }, "options": { "connectionTimeout": "P150D" }, "credential": { "AuthenticationKind": "UsernamePassword", "kind": "SQL", "path": "YYYY.database.windows.net,1433;XXXX", "Username": "ZZZZ", "Password": "XYZ", "EncryptConnection": true } } } }

  1. We press F5 and run the script

  2. We receive a success response

  3. We navigate the model using the MDX visual functionality in SQL Management Studio

  4. Now the model works but we cannot determine if the query are really sent to SQL readonly replica or to the readwrite SQL replica. How we can check that from within AAS?

Is the syntax we guessed in point 4 the correct ones?

Thanks.

@Mike-Ubezzi-MSFT

This comment has been minimized.

Copy link
Contributor

@Mike-Ubezzi-MSFT Mike-Ubezzi-MSFT commented Jan 19, 2019

@sgiovinetti There is an interal dialogue that has finally gained some traction and what I have is the following:

When you set MultiSubnetFailover=true, we will also set ApplicationIntent=ReadOnly.

I will follow-up with additional details but, at the moment, this is what I have. You can try setting both properties in the json and test this. Please update this discussion. Thank you.

@sgiovinetti

This comment has been minimized.

Copy link
Author

@sgiovinetti sgiovinetti commented Jan 21, 2019

@Mike-Ubezzi-MSFT : thank you. Setting MultiSubnetFailover:true into the options:{...} part works fine.

@Mike-Ubezzi-MSFT

This comment has been minimized.

Copy link
Contributor

@Mike-Ubezzi-MSFT Mike-Ubezzi-MSFT commented Jan 23, 2019

Providing additional information from internal discussion:
Note that setting MultiSubnetFailover=true is no longer strictly required when connecting to a multi-subnet availability group. Since .NET 4.6.1, omitting MultiSubNetFailover when connecting to a multi-subnet AG causes only a brief ~0.5sec delay if the primary replica is not active on the subnet of the IP address returned first in the list from DNS.

Details here: SqlConnectionStringBuilder.TransparentNetworkIPResolution Property

So if you don’t want AppicationIntent=ReadOnly, you can omit them both.

@Mike-Ubezzi-MSFT

This comment has been minimized.

Copy link
Contributor

@Mike-Ubezzi-MSFT Mike-Ubezzi-MSFT commented Jan 23, 2019

We will now proceed to close this thread. If there are further questions regarding this matter, please comment and we will gladly continue the discussion.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
3 participants
You can’t perform that action at this time.