Creating Secure Excel SQL Queries

Sometimes it’s easier (lazier?) to use an Excel query for simple reports. One problem with this is that the query authentication properties are viewable to anyone who has access to the report. While this is like teaching a dog algebra for the average user, an advanced user could, conceivably, use this connection data to create their own, unauthorized queries.

While there is no way that I know of to hide the query information, you can create a set of protected procedures that:

adds the authentication (connection string)
runs the query
then removes it again.

This is a simple example of what it could look like:

First you’ll want to create a new VBA module in your Excel worksheet with procedures that look something like this:

Sub AddConnect()
'this will add the connection string to
'all connections in the active workbook
For Each CN In ActiveWorkbook.Connections
CN.ODBCConnection.BackgroundQuery = False 'this line is optional
CN.ODBCConnection.Connection = _
"ODBC;DRIVER={Progress OpenEdge 10.1C Driver};" & _
"UID=YOURUSERNAME;PWD=YOURPASSWORD;" & _
"HOST=YOURHOST;PORT=YOURPORT;DB=YOURDATABASE;" & _
"DefaultIsolationLevel=READUNCOMMITTED"
Next
End Sub

Sub RemoveConnect()
'this will remove the connection string from
'all connections in the active workbook
For Each CN In ActiveWorkbook.Connections
CN.ODBCConnection.Connection = "ODBC;"
Next
End Sub

Sub ListConnections()
'this will list all query connection strings
'in the active workbook
For Each CN In ActiveWorkbook.Connections
MsgBox (CN.ODBCConnection.Connection)
Next
End Sub

Sub RefreshAll()
'this will refresh all the data connections in the active workbook
AddConnect 'add the connection strings
ActiveWorkbook.RefreshAll 'refresh all connections
RemoveConnect 'remove the connection strings
End Sub