0

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.

Improve this question
5
  • Have you tried using a DSN and also restrict the server to only allow the operations you need to have access? Commented Aug 22, 2018 at 12:35
  • @user7393973 - no. Can you please provide me with an example how DSN is used in VBA? Commented Aug 22, 2018 at 12:55
  • Search for ODBC Data Source to setup the DSN and then use the name only when you open the ADODB connection. Commented Aug 22, 2018 at 13:12
  • Quick question - will users have to install this ODBC on their machines before using the app? Commented Aug 22, 2018 at 14:06
  • If I'm not mistaken it comes with Windows. Try searching "ODBC Data Source" in the menu/file system. Commented Aug 22, 2018 at 14:08
Related questions
3
Error 3709 when connecting thru ADODB.connection to SQL Server 2008 in Excel
0
Excel VBA - connection string to SQL Server 2008
4
VBA New Database Connection
Load 7 more related questions Show fewer related questions

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.