[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 380
  • Last Modified:

encypting sql connection string

I am connecting to sql server using vba and the ADODB connection method.    I am worried that even with the vba project protected someone could access the connection string, username and password.   There are so many tools on the market to unprotect vba projects that I was wondering if there are anyother options.  I would prefer to not have to prompt my user for the password.  I am also currently using the obfusc funtion to help encrypt the connection but all someone would have to do is understand the function to decrypt it.  

Thanks,
Montrof
0
montrof
Asked:
montrof
  • 6
  • 6
  • 4
1 Solution
 
Tommy KinardCommented:
What I do is ask the user for their login name and password and I put it in a binary file on their harddrive. Then when needed I read the binary file. Remember to encrypt the binary file.

HTH
dragontooth
0
 
[ fanpages ]IT Services ConsultantCommented:
dragontooth:
What I do is ask the user for their login name and password and I put it in a binary file on their harddrive. Then when needed I read the binary file. Remember to encrypt the binary file.

If an interested party can circumvent the project password, then they have the access to the routine that decrypts the binary file.
0
 
montrofAuthor Commented:
That could be the right direction.  In the office I am not worried about having to store a file locally but if they email the file out that is when the issue would arise.   So if the file was local on there drive that may work.   Could you be more specific how you create and connect to the binary file and the vba you use to read it.  

Thanks,
Montrof
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
[ fanpages ]IT Services ConsultantCommented:
Montrof:

If the workbook contained security credentials (secured with a VB[A] project password) & the file is e-mailed outside of your control, would external parties be able to gain access to your internal systems in order to use the username/password combination?

BFN,

fp.
0
 
montrofAuthor Commented:
Well it is more that they would have a network address and user name and password so if they wanted to they could gain access.  VBA project passwords are fairly easy to crack.  You can just google and find several tools and methods to unlock the VBA project.
0
 
[ fanpages ]IT Services ConsultantCommented:
Depending on which version of Microsoft Excel you are using, yes.

However, my point was that even with the user name/password for your connection string, should your database server/network firewall allow access from outside of your organisation?
0
 
Tommy KinardCommented:
@fanpages:
If an interested party can circumvent the project password, then they have the access to the routine that decrypts the binary file.

I do agree with this, but they have to work at it, they have to find the file and then decrypt it. It will not be in plain sight, keep in mind you and I are not going to do something like this, so the idea is to make them work for it. Otherwise we would need to put in a hardware lock to make it completely secure. Which can be circumvented with the code.

Well we could make the routines that read and write to the binary file a dll, this would remove that issue but can we write a dll? This would cause problems when the end user does not have the dll.

@Montorf:

I have to find it, give me a few minutes.
0
 
Tommy KinardCommented:
Below are the subs for reading and writing a binary file. The input string should be encrypted before using this sub. and of course you will need to decrypt the string after you read it. I would also recommend that you not set the directories and file name in the actual sub, pad the encrypted data with random information that only you know how to get out. example: make a string 255 characters long and at character number 57 read the user name, at character 194 read the password, have the information terminated with a character that you want so you know when to stop reading the password or username.

Make sure to go through 5 to 6 subs do something or not to the string, the more confusing it gets the harder it is for them to hack. Keep in mind also you have to maintain it. :)

Sub ReadBinary(oMyInfo As String)
    Dim mFNo As Long
    Dim DataDir As String, mFleNm As String
    DataDir = "C:\SomeDirectory\"
    mFleNm = "MySuperSecetPassWordFile.Sec"
    mFNo = FreeFile
    On Error Resume Next
    Open DataDir & mFleNm For Binary Access Read Write As #mFNo
    Get #mFNo, 1, oMyInfo
    Close mFNo
    If Err.Number <> 0 Then Err.Clear
    On Error GoTo 0
End Sub

Sub WriteBinary(iMyInfo As String)
    Dim mFNo As Long
    Dim DataDir As String, mFleNm As String
    DataDir = "C:\SomeDirectory\"
    mFleNm = "MySuperSecetPassWordFile.Sec"
    mFNo = FreeFile
    On Error Resume Next
    Open DataDir & mFleNm For Binary Access Read Write As #mFNo
    Put #mFNo, 1, oMyInfo
    Close mFNo
    If Err.Number <> 0 Then Err.Clear
    On Error GoTo 0
End Sub

Open in new window

0
 
montrofAuthor Commented:
Sorry one more question,  what do you pass into the readbinary sub to decrypt.  Could you show me also how you used it in an actual sub to call it and read in the encrypted data.
0
 
[ fanpages ]IT Services ConsultantCommented:
dragontooth:

I do agree with this, but they have to work at it, they have to find the file and then decrypt it. It will not be in plain sight, keep in mind you and I are not going to do something like this, so the idea is to make them work for it. Otherwise we would need to put in a hardware lock to make it completely secure. Which can be circumvented with the code.

Well we could make the routines that read and write to the binary file a dll, this would remove that issue but can we write a dll? This would cause problems when the end user does not have the dll.

Again, I appreciate your viewpoint, but encrypting user names/passwords, & distributing routines in Dynamic Link Libraries, just makes day-to-day support difficult & inconveniences users.

My point (again) is that ensuring no external parties can gain access to the infrastructure *should* already be a priority.
0
 
Tommy KinardCommented:
fanpages:

There were 2 posts there that were not there when I posted. I guess I took too long to type. :(

I agree with what you are saying and with your point, and with the post that I didn't read until now. Neither one of has said to save the file as a non macro enabled file before sending the file to outside personnel. This should be automatic. The database should already be secure from outside connectivity. So the only security issue should be inside meddling. :) Then the intent is to make it hard for them to get to it and not have it in plain sight. I hate the DLL suggestion but I am throwing it out there, as you said nothing but a pain to keep up with, but on the other hand it IS more secure. All we can do in the end is tell the OP what to look out for and the problems trials and tribulations. Then answer their question to the best of your ability. :)

@montrof:

Please be sure to read what fanpages and I have been discussing.
To answer your second question:
Sub Main()
    Dim UserName As String, Password As String
    Dim Newstring As String
    'set the login
    UserName = "FredDollarStore"
    Password = "FriedTaters"
    Newstring = Space(10) & ">" & UserName & "<" & String(50, 14) & ">" & Password & "<" & String(59, 12)
    'write the username and password to the file
    WriteBinary Newstring
    'read the user name and password from the file
    ReadBinary Newstring
    UserName = Mid(Newstring, 12, InStr(12, Newstring, "<") - 12)
    Password = Mid(Newstring, InStr(50, Newstring, ">") + 1, (InStr(50, Newstring, "<") - 1) - (InStr(50, Newstring, ">")))
End Sub

Open in new window

0
 
montrofAuthor Commented:
I understand it will never be fully secured.  I just want to make it as difficult as possible and hope that the method I use gets my managers approval.  

I also am getting an error when I test the Sub Main at the

UserName = Mid(Newstring, 12, InStr(12, Newstring, "<") - 12)

Open in new window


it says Invalid procedure call or argument.

Thanks again for the help,

Montrof
0
 
Tommy KinardCommented:
Montrof,

I am going thru a computer upgrade right now, I will be out of pocket until this afternoon.
0
 
montrofAuthor Commented:
Ok i got it to work.  Now my question is how do you seprate it so that you create the binary file and then all you do in you is read the file because you do not want to show how you create file.  

montrof
0
 
Tommy KinardCommented:
Sorry I took so long, it took 3 hours to copy my old computer.

The error you are getting is from a zero length string. You will need to check for that.

You will need to break the example sub apart, make a function to add them together and another function to break them apart
0
 
montrofAuthor Commented:
Ok thank you again for all the help

montrof
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 6
  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now