Solved

encypting sql connection string

Posted on 2013-05-13
16
326 Views
Last Modified: 2013-05-15
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
Comment
Question by:montrof
  • 6
  • 6
  • 4
16 Comments
 
LVL 14

Expert Comment

by:Tommy Kinard
ID: 39163082
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
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39164119
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
 
LVL 1

Author Comment

by:montrof
ID: 39164309
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
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39164429
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
 
LVL 1

Author Comment

by:montrof
ID: 39164509
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
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39164531
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
 
LVL 14

Expert Comment

by:Tommy Kinard
ID: 39164534
@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
 
LVL 14

Accepted Solution

by:
Tommy Kinard earned 500 total points
ID: 39164594
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 1

Author Comment

by:montrof
ID: 39164706
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
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39164708
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
 
LVL 14

Expert Comment

by:Tommy Kinard
ID: 39165099
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
 
LVL 1

Author Comment

by:montrof
ID: 39165170
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
 
LVL 14

Expert Comment

by:Tommy Kinard
ID: 39165421
Montrof,

I am going thru a computer upgrade right now, I will be out of pocket until this afternoon.
0
 
LVL 1

Author Comment

by:montrof
ID: 39166033
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
 
LVL 14

Expert Comment

by:Tommy Kinard
ID: 39167822
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
 
LVL 1

Author Comment

by:montrof
ID: 39167831
Ok thank you again for all the help

montrof
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now