1

I am pulling data from SQL Server Analysis service into Excel. I want Connection String To be encrypted. When I go to Data->Connection properties and click on save Password, I am able to see the password. Can any one Suggest me how to encrypt password?

Suggestion are highly appreciable.

Thanks in advance!!

Chandu Nandina
  • 11
  • 2
  • another option is to create a minimal access user on the server and use that username password – usmanhaq Aug 08 '20 at 16:39
  • @usmanhaq - Even in that case, the password will be visible right? – Chandu Nandina Aug 10 '20 at 12:54
  • Yes the password be visible but even if it is visible and misused it would not have much access – usmanhaq Aug 11 '20 at 04:37
  • @usmanhaq - I can use that as an option. But when the file is shared to the external users, they will download the data right. Are there any other ways to do it? Can we encrypt the odc (office data connection file)? – Chandu Nandina Aug 11 '20 at 20:23

1 Answers1

0

use Encrypt Password property in the connection string:

Specifies whether a local password is to be used to encrypt local cubes. Valid values are True or False. The default is False.

You can get more detail from the below article:

https://learn.microsoft.com/en-us/analysis-services/instances/connection-string-properties-analysis-services?view=asallproducts-allversions

Jalpa Panchal
  • 8,251
  • 1
  • 11
  • 26
  • Hello @jalpa, Thanks for your response!! I have tried that and below is the connection string as of now: **Provider=MSOLAP.8;Password=xxxxx;Persist Security Info=True;User ID=xxxx;Initial Catalog=xxxxx;Data Source=https://xxxxx/OLAP/msmdpump.dll;Encrypt Password=True;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Update Isolation Level=2** However, I can still see the password. I have also tried by deselecting the "Save password" but then it is prompting to enter the password. – Chandu Nandina Aug 10 '20 at 12:50