Add Security Protections for Data Extraction

Inevitably, data sharing is essential for business activities. While we are encouraging the data exchange and sharing among users for the insights and analysis, the data security is always on my concern.

The ignorance or lack of awareness of data security could lead to business disaster, with consequential penalties, legal accuses, and reputation risk that the organization needs to bear with.

The Get & Transform Data Wizard

First, let's talking about what is Get & Transform Data Wizard.

According to Microsoft's definition:

Excel includes a powerful set of features called Get & Transform, which provides fast, easy data gathering and shaping capabilities. Get & Transform enables you to connect, combine, and refine data sources to meet your analysis needs.

In another way, the Get & Transform Data Wizard in Excel is a powerful tool, which allows the users to extract the data from various connected sources.

Having said that, by delegating the privilege to users to access the connected sources, how could we ensure there is at least some degree of security is in place? The fact is we don't want users to abuse the rights or conveniently sharing this great ability to get data with other unauthorized users?

---

The Excel ODBC Query File

We are not going to spend much time about how we could extract data using Get & Transform Wizard since you can get more details from the hyperlink of Microsoft's definition or other articles online, such as below:

Import data from external data sources (Power Query)

Importing Data To Excel Using ODBC

However, to quickly demonstrate and establish a data extraction, let's say from MS SQL Server, we could define an Excel ODBC Query ( .dqy) file like this:

XLODBC
1
DRIVER=SQL Server;SERVER=YourServer;UID=UserID;PWD=Password;APP=Microsoft Office;WSID=;DATABASE=YourDB
exec Sp_Test

It could connect to any data source that you preferred, and whether it's linked to a stored procedure or a simple Select SQL statement.

For illustration purposes, basically, this stored procedure Sp_Test is generating some random records.

CREATE PROCEDURE [dbo].[Sp_Test]
(
    @Para1 int = 10000
)
AS
BEGIN
    SET NOCOUNT ON;
    
    WITH Numbers AS (
        SELECT 1 AS SerialNo
        UNION ALL
        SELECT SerialNo + 1
        FROM Numbers
        WHERE SerialNo < @Para1
    )
    SELECT *, concat('Test Data ', SerialNo) Field1, CONVERT(varchar(255), NEWID()) Field2
    FROM Numbers
    OPTION (MaxRecursion 0)

END

Make sure you provided the correct credentials in your Excel ODBC Query file, and then you could double click for the execution.

Voilà! And you should able to generate the records and see it in Excel directly.


Note: Excel will probably prompt you for Security Notice, we just click Enable to proceed.


In addition, due to the nature of random records being generated for this illustration, you would see new random records being displayed when you click the Refresh or Refresh All option in Excel.

---

Now, let's talk about Protection Measures

There could be at least three areas we could hardening with security protection:

1. File Open Protection
2. Custom User Form Protection
3. Data Source User Control

1. File Open Protection

The File Open Protection is easy to configure.

When we try to save the Excel file, we could go to the Tools > General Options in the Save As dialog for the configuration.


Another dialog will prompt out, and we just fill in the Password needed, whether to open or to modify.


Additionally, it would be useful to read these articles for the protection and security implementation in Excel.

Protect an Excel file

Protection and security in Excel

2. Custom User Form Protection

Before we proceed further, you have been noticed that when you click on the Refresh or Refresh All option, the data listed will be refreshed. The reason being, there is a connection established to the data source.

You should manage to find more details when you click on the Queries & Connections option under the Data tab.


Have you ever think that it's a potential security risk if we are allowing the users to know this info, especially the details on Connection String and Command Text?


Between the convenience of providing the data versus the security concern, we need to straight a balance.

To cover this part, what I would do is to control the refresh of data via a User Form.

There are several steps we need to follow.

a) Design a User Form


Note: This is just for illustration purposes. So, you could actually design a more complex form to suit your requirement.

b) Insert the follow codes

For the form's initial setup, we probably need to declare some const variables:

Private Const cConnStr As String = "ODBC;DRIVER=SQL Server;SERVER=<<Server>>;UID=<<UserID>>;Pwd=<<Password>>;APP=Microsoft Office;WSID=;DATABASE=<<Database>>"
Private Const cServer As String = "YourServerLocation"
Private Const cDB_Schema As String = "YourDBSchema"
Private Const cCommandText As String = "exec Sp_Test"

After that, let's try to construct the "Ok" button's click event. First, we would do the validation for the input fields.

'Validate inputs
 txtUserID.Text = Trim(txtUserID.Text)
 If txtUserID.Text = "" Then
     MsgBox "Please provide the User ID", vbCritical, "User ID Required"
     txtUserID.SetFocus
     Exit Sub
 End If
    
 If txtPwd.Text = "" Then
     MsgBox "Please provide the Password", vbCritical, "Password Required"
     txtPwd.SetFocus
     Exit Sub
 End If

We would need to construct a genuine connection string based on the user's inputs:

Dim ConnStr As String

'Update the Connection String with settings and inputs
 ConnStr = Replace(cConnStr, "<<Server>>", cServer)
 ConnStr = Replace(ConnStr, "<<UserID>>", txtUserID.Text)
 ConnStr = Replace(ConnStr, "<<Password>>", txtPwd.Text)
 ConnStr = Replace(ConnStr, "<<Database>>", cDB_Schema)

Then, let's try to refresh the Connection

'Refresh Connection
   
 Application.DisplayAlerts = False
   
 On Error GoTo Err
   
 Dim conn As ODBCConnection
 For i = 1 To ThisWorkbook.Connections.Count
     Set conn = ThisWorkbook.Connections(i).ODBCConnection
     With conn
         .BackgroundQuery = False
         .Connection = ConnStr
         .CommandText = cCommandText
         .Refresh
           
         'Reset back the Connection and CommandText to Dummy values
         .Connection = "ODBC;Dummy"
         .CommandText = "Hello World :)"
     End With
       
     'We only refresh one Connection for this illustration, so quit if got more than one connection
     Exit For
 Next i
   
 Application.DisplayAlerts = True

Then complete codes of the User Form is shown as follows:

Private Const cConnStr As String = "ODBC;DRIVER=SQL Server;SERVER=<<Server>>;UID=<<UserID>>;Pwd=<<Password>>;APP=Microsoft Office;WSID=;DATABASE=<<Database>>"
Private Const cServer As String = "YourServerLocation"
Private Const cDB_Schema As String = "YourDBSchema"
Private Const cCommandText As String = "exec Sp_Test"

Private Sub cmdOk_Click()
    Dim ConnStr As String
    
    'Validate inputs
    txtUserID.Text = Trim(txtUserID.Text)
    If txtUserID.Text = "" Then
        MsgBox "Please provide the User ID", vbCritical, "User ID Required"
        txtUserID.SetFocus
        Exit Sub
    End If
    
    If txtPwd.Text = "" Then
        MsgBox "Please provide the Password", vbCritical, "Password Required"
        txtPwd.SetFocus
        Exit Sub
    End If
    
    'Update the Connection String with settings and inputs
    ConnStr = Replace(cConnStr, "<<Server>>", cServer)
    ConnStr = Replace(ConnStr, "<<UserID>>", txtUserID.Text)
    ConnStr = Replace(ConnStr, "<<Password>>", txtPwd.Text)
    ConnStr = Replace(ConnStr, "<<Database>>", cDB_Schema)
    
    'Refresh Connection
    
    Application.DisplayAlerts = False
    
    On Error GoTo Err
    
    Dim conn As ODBCConnection
    For i = 1 To ThisWorkbook.Connections.Count
        Set conn = ThisWorkbook.Connections(i).ODBCConnection
        With conn
            .BackgroundQuery = False
            .Connection = ConnStr
            .CommandText = cCommandText
            .Refresh
            
            'Reset back the Connection and CommandText to Dummy values
            .Connection = "ODBC;Dummy"
            .CommandText = "Hello World :)"
        End With
        
        'We only refresh one Connection for this illustration, so quit if got more than one connection
        Exit For
    Next i
    
    Application.DisplayAlerts = True
    
    Exit Sub
Err:
    Application.DisplayAlerts = True
    MsgBox Err.Description, vbCritical, "Error"
End Sub

Before we start the test, you may find that it's no way to call the User Form from the Excel front end. Well, this can be simply resolved by creating a simple macro to call the form out.

Sub CallForm()
    Dim frm As frmLogin
    Set frm = New frmLogin
    frm.Show vbModal
End Sub

By doing the above, you should able to find the subroutine CallForm in your Macro list.


Click the Run button to launch the form for testing.

c) Test the result

As you clicked on the "Ok" button, you should able to see the data is refreshed.


Let's try to review the Connection Properties now.


Well, that looks good for us to hide the confidential details of Connection String and Command Text!

3. Data Source User Control

This would be the additional part that we need to control since we are passing the real SQL Server User Credentials for User Authentication.

So again, proper control is paramount. The User Credentials and its control management should be reviewed periodically.

As a general rule of thumb, only give the required permissions to perform the task. For example, giving the role of db_datareader versus db_owner could make a huge impact in case the user authentication is compromised.

Note: You need to grant the EXECUTE permission for the User Credentials that passing through the User Form, else you may get a permission denied error.

---

To summarize all, we can see the potential for such integration between Excel with SQL Server (or any other possible data sources) for data extraction in a more secured way.

I hope this article is useful to you. Have fun trying it out yourself!