data connector, hide the Password?

[dlorenc]

I use an OLAP data connector to front-end excel pivot tables to SQL... I use a SQL User ID/PW for read-only access from these 'analytic front-end' tools.... as it is a read-only ID, I've never really considered the ID/PW to be sensitive...but one of my security pukes asked if I could hide the PW...?...can I?...

excel 2007; olap data connection string:
Provider=SQLOLEDB.1;Password=MerryXmas#2010;Persist Security Info=True;User ID=ScoreCardReader;Initial Catalog=ScoreCard;Data Source=ilsqlc84;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=D47782307;Use Encryption for Data=False;Tag with column collation when possible=False


this is exposed from the data/connections/workbook connections/properties

while this approach works very well for buidling these excel front-ends (which are placed in a sharepoint document library with access controlled by the sharepoint site permissions), once the ID/PW is exposed, any one can access the DB using other tools (such as SSMS)...

again, it is read-only, but showing a ID/PW seems to rub security people the wrong way...

 

[xenou]

I think you could construct your string so that the password must be entered by the user (i.e., in an input box that pops up when you run the query). Otherwise, it is correct that passwords stored in VBA are not safe. Depending on your network environment, you can sometime access servers with windows authentication - but if you're network guys aren't already suggesting that then maybe it's not possible in your situation.

 

[Ruddles]

Sorry if I'm asking a stupid question but are you using VBA at all?

 

[dlorenc]

no..not using vba at all...

I am distributing rudimentary analysis tools (pivot tables) to our functional managers (so we need to keep it simple, distributing passwords is not such a good idea). We found by embedding the connection string in the excel connector, and putting the excel file in a sharepoint site, and controlling access to the sharepoint site...I solve an admin PITA..

- access to the data (by controlling access to the sharepoint site) is controlled using sharepoint admin permissions (much easier than submitting requests to our AD admins, or our DBA's...

The data is not 'sensitive'... but I do want folks to concentrate on their own data...so I filter the excel reports for a functional manager's organization, then post THEIR version of the excel pivot tool on that organization's sharepoint subsite....

the issue was noted that a more sophisticated user could review the connector, and with the ID/PW use other tools (crystal, SSMS...) gain access to data for other functional teams...again, not a big concern...but something I would prefer to protect against...

sounds like no way to hash the current PW in excel connectors..mm?

 

[Ruddles]

Sorry, I was going to suggest 'obfuscating' the password and using the obfuscated string in your code, then de-obfuscating it just before you needed it.

I don't know any other way of hiding the password.

 

[dlorenc]

because we are not using vb..does not mean we cannot...

please give me some idea of the approach to 'obfuscating' in vb...?..

 

[Ruddles]

You process the password through a bit of VBA which converts it into a completely different string. You can then hard-code the string into your VBA because if anyone sees it, they won't be able to use it because it's not the real password. Then when you need to use the password in your code, you run it through the reverse process and that returns the original string.

You can make the code as simple or as complex as you wish - but if someone sees the code they may have enough knowledge to reverse-engineer the process and recover the password. It doesn't really encrypt, it just adds an extra hurdle for people to overcome if they want to access it illicitly. On the other hand, it may satisfy your ndatabase security gurus!

Try this: create a new workbook and open the VBE (Alt-F11), then go Insert > Module. paste this code in the code window:-

Function Obfusc(oWord As String, nCrypt As Boolean) As String
 
  Dim iPtr As Integer
  Dim iByte As Byte
 
  For iPtr = 1 To Len(oWord)
    If nCrypt Then
      iByte = Asc(Mid(oWord, iPtr, 1)) + 99 + iPtr
    Else
      iByte = Asc(Mid(oWord, iPtr, 1)) - 99 - iPtr
    End If
    Obfusc = Obfusc & Chr(iByte)
  Next iPtr
 
End Function

(This is an extremely simple routine - you might want to make it more complex!)

In the Immediate window (Ctrl-R) type ?obfusc("MerryXmas#2010",true) and hit Enter. This will return the obfuscated version of "Hello World!" which is "±ÊØÙáÁ×Ìß� Ÿ¡¡".

Now type ?obfusc("±ÊØÙáÁ×Ìß� Ÿ¡¡",false) and hit Enter. (You'll probably have to copy & paste that!) This will return the original string.

So in actual use, you'd get the obfuscated string manually by typing ?obfusc("MerryXmas#2010",true) and pasting the obfuscated string "±ÊØÙáÁ×Ìß� Ÿ¡¡" into your code like this:-

password = obfusc("¬±ÊØÙáÁ×Ìß� Ÿ¡¡",False)
conn_str = "Provider=SQLOLEDB.1;Password=" & password & ";Persist Security Info=True... etc"

That's the general idea.

 

[dlorenc]

works as advertised..pretty slick actually... thanks for the help!

 

[dlorenc]

no joy....

after setting the data connector to use the new function...(with save password checked)...excel presents a window requesting the sql ID/PW...this window apparantly validates the ID/PW (which has to be entered without the function)..then actually resets the ID PW in the connector file....which effectively overwrites the manual ID/PW entry I made (using the function)...this leaves the connector file with the ID/PW without the function in the connector file...

*sigh*...