• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 528
  • Last Modified:

connection string

Hi,
I woulld like to use excel to connect to SQL and pull data in.  What I would like to know is how can I put the connection string in an excel add in?  I want it hidden.  If that not the way to do it.  what is the correct way?  Keep in mind the excel spread sheet should have the ability to update the data via a refresh button and again the connection string is never exposed.
0
mark1per
Asked:
mark1per
  • 4
  • 3
1 Solution
 
weellioCommented:
i was thinking about your question from earlier and remember that i threw this together for some people at work, it may add some insight and answer your question

Password1 is the password
use the "unhide" macro to see the hidden sheet.


sql1.xls
0
 
jobprojnCommented:
weellio, that is a really cool file.  Thanks for sharing.
0
 
mark1perAuthor Commented:
That is great and works perfect.  thanks
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
jobprojnCommented:
Mark1Per, I have the same issue.  I need a simple way to be able to hide connection strings from end-users in Excel.  I'm working on Excel files that contain multiple connections, and all have parameters.  To have to manage all of this in VBA is cumbersome and time consuming.

There are many articles written (including by Microsoft) about the importance of securing connection strings.  If this is so important they should provide us with a simple way of hiding the string in Excel.

I'm currently searching for solutions to this problem myself.  If I discover a method other than through solid VBA I'll post here.
0
 
mark1perAuthor Commented:
great thanks much appreciated as will I.
0
 
jobprojnCommented:
Mark1Per,  if you're using Excel 2003 you can disable the "Data Properties" and "Edit Query" menus by inserting the below code into a standard VBA module.  

This doesn't appear to work in Excel 2007.  I'm currently looking for this same type of solution for 2007. Will let you know what I find.
Sub DisableMenu_DataProperties()
' Excel 2000 - 2003
    Dim Ctrl As Office.CommandBarControl
    For Each Ctrl In Application.CommandBars.FindControls(ID:=1951)
        Ctrl.Enabled = False
    Next Ctrl
End Sub
 
 
Sub DisableMenu_EditQuery()
' Excel 2000 - 2003
    Dim Ctrl As Office.CommandBarControl
    For Each Ctrl In Application.CommandBars.FindControls(ID:=1950)
        Ctrl.Enabled = False
    Next Ctrl
End Sub

Open in new window

0
 
jobprojnCommented:
The simplest solution I could come up with for 2007 is to disable access to the DataRangeProperties, Connections, and ConnectionProperties menu buttons in the ribbon.  Modifications to the ribbon in Office 2007 is to be done via the 'Office 2007 Custom UI Editor'.  Here are the instructions:

1. Download and install the 'Office 2007 Custom UI Editor' open source software.  This can be found at http://openxmldeveloper.org/archive/2006/05/26/CustomUIeditor.aspx.  If you'd rather link to it directly from Microsoft's site go to the following microsoft page and scroll to the very bottom.  http://msdn.microsoft.com/en-us/library/cc508991(office.11).aspx.  In the section 'Additional Resources' you will see a link to 'UI Editor.

2. After installed, open the UI Editor and choose File / Open and select the Excel file that you want to disable the Connection Properties buttons.

3.  In the main browser screen of the UI Editor type in the XML code you see in the below code snippet.

4. Click the 'Checkbox' icon to ensure the code works, then click the Save icon.

5. Now open your Excel file and the buttons that lead to the connection strings are disabled.

Only problem with this method of securing the connection string, users can get around it by 'Copying' all the cells within the connected data range, and then paste those cells into a new workbook.  By pasting they not only copy the data, but they also copy the connection string info associated with it.   Oh well, I suppose if you search hard enough you'll always find a way around security.

Good luck.
<customUI onLoad="ribbonloaded" xmlns="http://schemas.microsoft.com/office/2006/01/customui">		
 
  <commands>
    <command idMso="DataRangeProperties" enabled = "false" onAction="DisableDataRangeProperties"/>
    <command idMso="Connections" enabled = "false" onAction="DisableConnections"/>
    <command idMso="ConnectionProperties" enabled = "false" onAction="DisableConnectionProperties"/>
  </commands>
 
</customUI>

Open in new window

0
 
mark1perAuthor Commented:
Cool thanks
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now