VBA ADODB Connection - SqlConnectionStringBuilder Class

Question

Background

I have an Excel application that connects to SQL Server using ADODB Connection via VBA. This ADODB Connection contains credentials that are hard coded. I know this is not best practice.

Issue

It has come to my attention that there are some users that are using this method to access password protected VBA modules, and retrieve connection credentials. Which they use it to access the server.

Possible Solution?

I am considering possible solutions to hide the connection string from VBA or set up a secure connection string using this method.

I wonder if it is possible to build this class with the credentials built in, so that in the VBA we do not hard code the credentials, rather just refer to this connection class?

Anyone here has experience with this?

I have looked at this solution. However, I am using a 2017 version of Visual Basic and I am unable to follow the step-by-step guide. In particular, I was unable to find the activeX reference.

I would really appreciate any help with this.

Many thanks.

Have you tried using a [DSN](https://en.wikipedia.org/wiki/Data_source_name) and also restrict the server to only allow the operations you need to have access? — user7393973, Aug 22 '18 at 12:35
@user7393973 - no. Can you please provide me with an example how DSN is used in VBA? — Oday Salim, Aug 22 '18 at 12:55
Search for ODBC Data Source to setup the DSN and then use the name only when you open the ADODB connection. — user7393973, Aug 22 '18 at 13:12
Quick question - will users have to install this ODBC on their machines before using the app? — Oday Salim, Aug 22 '18 at 14:06
If I'm not mistaken it comes with Windows. Try searching "ODBC Data Source" in the menu/file system. — user7393973, Aug 22 '18 at 14:08

VBA ADODB Connection - SqlConnectionStringBuilder Class

0 Answers0