How to securely store Connection String details in VBA

I have an Excel Template that has hardcoded Ms Access MDB path in the VBA code used to connect to the Access tables and save, retrieve data.

I migrated the MS Access Database over to SQL Server with Integrated Authentication for the Excel Template Users.

My question is, What is the Recommend Way / Best Practice for storing the SQL Server DB connection string and retreiving it in Excel 2007 VBA to save and retrieve data?

In the past, I have done the following.

1. Use a Registry Key setting that has the Connection String. Then in the VBA, write a function that reads the registry key and returns the connection string.

2. Have a "Settings" hidden sheet within the Excel Template, with named cell for the connection string. Read the connection string in VBA by accessing that named range.

3. Use a .INI txt file that goes with the Excel template. (This is not ideal and I want to avoid this as it builds a dependency on that external file)

I don't like # 1 because I want to avoid writing to/reading from Registry if possible. # 2 feels ok, thought I am not sure if there is a better "cleaner" way for doing this.

Any thoughts?

This is what I would do safely store connection string credentials

Download and install Visual Studio Express 2012 for Windows (FREE)

Open it as Administrator and create a New Project. Select Visual C# then Class Library and rename it to HiddenConnectionString

In the Solution Explorer, rename Class1.cs to MyServer.cs

Right click your MyConnection project in the Solution Explorer and select Add Reference

Type activeX in the search box and tick the Microsoft ActiveX Data Objects 6.1 Library

Copy and paste the below code into the MyServer.cs completely replacing whatever is in the file.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Runtime.InteropServices;
using System.IO;
using ADODB;

namespace HiddenConnectionString
{
    [InterfaceType(ComInterfaceType.InterfaceIsDual),
    Guid("2FCEF713-CD2E-4ACB-A9CE-E57E7F51E72E")]
    public interface IMyServer
    {
        Connection GetConnection();
        void Shutdown();
    }

    [ClassInterface(ClassInterfaceType.None)]
    [Guid("57BBEC44-C6E6-4E14-989A-B6DB7CF6FBEB")]
    public class MyServer : IMyServer
    {
        private Connection cn;

        private string cnStr = "Provider=SQLOLEDB; Data Source=SERVER\\INSTANCE; Initial Catalog=default_catalog; User ID=your_username; Password=your_password";

        public MyServer()
        {
        }

        public Connection GetConnection()
        {
            cn = new Connection();
            cn.ConnectionString = cnStr;
            cn.Open();
            return cn;
        }

        public void Shutdown()
        {
            cn.Close();
        }
    }
}

Locate the cnStr variable in the code and update your connection string details.

Right click the *HiddenConnectionString* solution in the Solution Explorer and select Properties.

Click the Application tab on the left side, then Assembly Info and tick Make Assembly COM-Visible

Click the *Build* from the menu on the left and tick Register For COM Interop

Note: If you are developing for 64-bit Office then make sure you change the Platform Target on the Build menu to x64! This is mandatory for 64-bit Office COM libraries to avoid any ActiveX related errors.

Right click the HiddenConnectionString in the Solution Explorer and select Build from the menu.

If everything went OK then your HiddenConnectionString.dll and HiddenConnectionString.tlb should be successfully generated. Go to this path now

C:\Users\administrator\Documents\Visual Studio 2012\Projects\HiddenConnectionString\HiddenConnectionString\bin\Debug

and you should see your files.

Now open Excel and go to VBE. Click Tools and select References.

Click the Browse button and navigate to the HiddenConnectionString.tlb.

Also, add references to Microsoft ActiveX Object 6.1 Library - this is so you can use ADODB library.

Now right click anywhere in the Project Explorer window and Insert a new Module

copy and paste the below code to it

Option Explicit

Sub Main()

    Dim myCn As MyServer
    Set myCn = New MyServer

    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset

    rs.Open "Select * from [TABLE_NAME]", myCn.GetConnection

    Range("A1").CopyFromRecordset rs

    rs.Close
    myCn.Shutdown

    Set rs = Nothing
    Set myCn = Nothing

    Columns.AutoFit

End Sub

Replace the [TABLE_NAME] with an actual table name in your database.

Hit F5 or hit the green play button on the ribbon.

If everything went OK, you should now see the returned Table on your spreadsheet.

my sample:

As you can see. Adding references to your own COM-library and storing the login credentials and other sensitive data inside the compiled .dll protects your data(connection string). It's very difficult to decompile the *.dll file to get any sensible information from it. There are various coding techniques to protect your *.dll even more but I am not going to go into details now. This itself achieves what you asked for.

myCn.GetConnection returns the ADODB.Connection object that was initialized inside the referenced COM library. No Excel user will be presented with the connection string or sensitive data (actually nobody else neither).

You can modify the C# code to accept parameters from VBA i.e. login, password, initial catalog, query to execute etc... if you have users with different privileges on the instance of your SQL Server it wouldn't be a bad idea to allow people to log in.

Note: there is no error handling added in the C# code and VBA. I would strongly recommending working on it if you're planning to use the technique I have described above.

How about storing it under CustomDocumentProperties?

Note: I am not sure, if the workbook (based on a given template) will inherit the property defined using CustomDocumentProperties in the template.