{"id":1127,"date":"2022-01-05T17:53:27","date_gmt":"2022-01-05T15:53:27","guid":{"rendered":"https:\/\/www.flip-design.de\/?p=1127"},"modified":"2022-01-05T17:53:27","modified_gmt":"2022-01-05T15:53:27","slug":"continuous-deployment-of-azure-analysis-services-cubes-part-2-sql-authentication","status":"publish","type":"post","link":"https:\/\/www.flip-design.de\/?p=1127","title":{"rendered":"Continuous Deployment of Azure Analysis Services Cubes \u2013 Part 2; SQL Authentication"},"content":{"rendered":"\n<p>In my last blog post, I\u2019ve described how you can deploy SSAS cubes to different stages with the Tabular Editor by using a OATUH authentication for the data source. This post describes a cube by using a SQL authenticated connection. Firstly, I\u2019ve created a cube by using a SQL Auth connection. Visual Studio does not save the password for the connection inside the BIM file. But you can add this, by adding this parameter inside the BIM file:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2022\/01\/image-7.png\"><img decoding=\"async\" loading=\"lazy\" width=\"945\" height=\"556\" src=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2022\/01\/image-7.png\" alt=\"\" class=\"wp-image-1128\" srcset=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2022\/01\/image-7.png 945w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2022\/01\/image-7-300x177.png 300w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2022\/01\/image-7-768x452.png 768w\" sizes=\"(max-width: 945px) 100vw, 945px\" \/><\/a><\/figure>\n\n\n\n<p>But you must awa43e of, that the password is stored as plain text inside your code. You don\u2019t have the possibility to encrypt it. After deploying the cube, I created s pipeline inside Azure DevOps like the other from Part 1.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2022\/01\/image-8.png\"><img decoding=\"async\" loading=\"lazy\" width=\"945\" height=\"556\" src=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2022\/01\/image-8.png\" alt=\"\" class=\"wp-image-1129\" srcset=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2022\/01\/image-8.png 945w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2022\/01\/image-8-300x177.png 300w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2022\/01\/image-8-768x452.png 768w\" sizes=\"(max-width: 945px) 100vw, 945px\" \/><\/a><\/figure>\n\n\n\n<p>The opposite to the other pipeline is, that we have a step to edit the data source.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2022\/01\/image-9.png\"><img decoding=\"async\" loading=\"lazy\" width=\"945\" height=\"556\" src=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2022\/01\/image-9.png\" alt=\"\" class=\"wp-image-1130\" srcset=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2022\/01\/image-9.png 945w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2022\/01\/image-9-300x177.png 300w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2022\/01\/image-9-768x452.png 768w\" sizes=\"(max-width: 945px) 100vw, 945px\" \/><\/a><\/figure>\n\n\n\n<p>This PowerShell script will add the password to the connection string:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$AlterDatasourceCommand = @\"\r\n{\r\n  \"createOrReplace\": {\r\n    \"object\": {\r\n      \"database\": \"Cube\",\r\n      \"dataSource\": \"SQL\/sqlpl001 database windows net;sql01\"\r\n    },\r\n    \"dataSource\": {\r\n      \"type\": \"structured\",\r\n      \"name\": \"SQL\/sqlpl001 database windows net;sql01\",\r\n      \"connectionDetails\": {\r\n        \"protocol\": \"tds\",\r\n        \"address\": {\r\n          \"server\": \"sqlpl002.database.windows.net\",\r\n          \"database\": \"sql01\"\r\n        },\r\n        \"authentication\": null,\r\n        \"query\": null\r\n      },\r\n      \"credential\": {\r\n        \"AuthenticationKind\": \"UsernamePassword\",\r\n        \"kind\": \"SQL\",\r\n        \"path\": \"sqlpl002.database.windows.net;sql01\",\r\n        \"Username\": \"ok\",\r\n\t\t\"Password\": \"XXXXX\",\r\n        \"EncryptConnection\": true\r\n      }\r\n    }\r\n  }\r\n}  \r\n\"@    \r\n\r\n$myPassword = \u2018XXXXX'\r\n$myUsername = 'philipp@plenz.onmicrosoft.com'\r\n\r\n$password = ConvertTo-SecureString $myPassword -AsPlainText -Force\r\n$credential = New-Object System.Management.Automation.PSCredential ($myUsername, $password)\r\n\r\nInvoke-ASCmd -Server asazure:\/\/eastus.asazure.windows.net\/aspltest -Database Cube -Query $AlterDatasourceCommand -Credential $credential\r\n\n<\/code><\/pre>\n\n\n\n<p>If you need to script your connection, you can right click to your connection and choose the Replace option. But here you must also add the password option.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2022\/01\/image-10.png\"><img decoding=\"async\" loading=\"lazy\" width=\"971\" height=\"608\" src=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2022\/01\/image-10.png\" alt=\"\" class=\"wp-image-1131\" srcset=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2022\/01\/image-10.png 971w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2022\/01\/image-10-300x188.png 300w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2022\/01\/image-10-768x481.png 768w\" sizes=\"(max-width: 971px) 100vw, 971px\" \/><\/a><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>In my last blog post, I\u2019ve described how you can deploy SSAS cubes to different stages with the Tabular Editor by using a OATUH authentication for the data source. This post describes a cube by using a SQL authenticated connection. &hellip; <a href=\"https:\/\/www.flip-design.de\/?p=1127\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[1],"tags":[],"_links":{"self":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/1127"}],"collection":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1127"}],"version-history":[{"count":1,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/1127\/revisions"}],"predecessor-version":[{"id":1132,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/1127\/revisions\/1132"}],"wp:attachment":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1127"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1127"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1127"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}