How to securely store Connection String details in VBA

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 ExplicitSub 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.AutoFitEnd 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.