Hiding Connection String in excel

[Naderus]

Dear Experts,

we are facing in our organization an audit issue with excel as most of our reports are connected directly to SQL server.
the risk that has been raised by audit is that in the connection string and the user name and password are visible.

we are following this method for our automation reports as when we generate a report and we connect this report to SQL the user will be able to refresh it once the data in SQL is refreshed.

the message in connection string is shown as below:
PS: i will put fake user id and IP

connection string:
Provider=SQLOLEDB.1;Password=FAKEPASSWORD;Persist Security Info=True;User ID=xx_fake ;Initial Catalog=BI;Data Source=100.10.65.85,8966;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=W1111111;Use Encryption for Data=False;Tag with column collation when possible=False

my question is there a way that we can keep this connection but either hide it or encrypted.

thank in advance.
Regards,
Nader

 

[mole999]

You can lock the VBA, that takes care of casual viewers.

You could deny access on your SQL to unknown users / MAC Address (I think) so it can't be machine hopped

Ensure that you only share books with authorised users

You might think of using a very secure sheet as the source for the connection, so its not sat in the VBA

 

[Naderus]

Thank you but i don't think the above will fix the issue.

 

[mole999]

so what does the audit team suggest

 

[VBA Geek]

Thank you but i don't think the above will fix the issue.

What about having a userform pop up where the user has to input Username and Password which then you will use to build up the connection string. Then when you try to connect to the database you can first test if your program successfully establishes the connection or if you get a wrong user id/password error.

 

[Naderus]

They didn't suggest anything they are just highlighting this issue as a critical issue, since it does not compile with the organization policy and everyone can see the connection information.

 

[SoumyaGanapathi]

Hi Nader,

Use C# code for your connection, which encrypts your connection string and helps you from unauthenticated access.

Hope the below provided link would help you.

https://stackoverflow.com/questions/3217014/how-to-securely-store-connection-string-details-in-vba



Thanks,