Connection to Excel , protect password or access to a table only

Question

Hi,

I have an olap & tabular cube but the request has become to operational with user wanting to view so many records, now im thinking of a view in excel
But how do i hide the credentials from user or can i create a group that can only access a specific table or other options please?

Regards

Answer 1

OK, so you want to expose selected data to (some) users, but you don't want them to be able to connect directly to SQL Server.

To be able to hide a password in the way you are describing, you need to have three tiers. SQL Server, the client and a middle tier. The password would be stored on the middle tier in a file (or whatever) that is not publicly exposed. A middle tier can be a full-fledged application server, it can be a web server or it can even be as primitive as Terminal Server or Citrix, where the user can do nothing else but running the application.

But if users want to run Excel on their on desktop or laptop, there is no way you can hide any password, as I said in my initial post. Or more precisely, there is no way you can store it so that users cannot find it. But you can of course use various forms of security by obscurity to make it more difficult to find. But that will not stop someone who is dead set on it.

So that is the presumption you need to work with. If you give them an Excel macro with a hard-coded user, no matter where you store the password, you should grant that hard-coded user access to the view, and nothing more beyond that.

Then again, I am not sure that I understand why you feel compelled to keep people out. Do these analysts have access on their own to the SQL Server instance? What can they access that way? Again, fi all they have permission to is this view, they can't do anything more.

Answer 2

You can't hide the credentials from the user. You can possibly obfuscate them, if it runs the user space, the user must be able to decrypt the password, or else the application cannot use it.

If you plan to use a user inside the Excel view, you can restrict its permission easily:

CREATE LOGIN ExcelUser WITH PASSWORD = 'EveryoneIWillKnowThisEventually'
CREATE USER ExcelUser
GRANT SELECT ON SpecificTable TO ExcelUser

Recall, that by default a new user has access to nothing at all.

Answer 3

Hi,
Are you saying that i create the connection in excel with dbuse "exceluser" with dpassword in connection but user only has read access to sepcific table?
exmaple perhaps please?

An odbc file?

Thank you

Answer 4

Hi,

Users normally connect to olap & tabular cubes via odbs connection files residing on sharepoint
Now the cube has become to operational and fails ats it too datacentric

We have an issue where a few in IT was writing scripts off the data warehouse hardociding paswords, then some analytics guys were using the credentials to extract data from sql dw on their own

i want to gt the the "cube" sql table view out but not disclose the password, can i give specific users access to a group that only has read access to a view or table
or what other options are there > an odbs file perhaps too but not disclosing the password or using the connecton file to query other tables

please help

Answer 5

sorry, i meant odc . the excel connection

i need to make it look like a cube but its a table view in a pivot