• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 398
  • Last Modified:


hello people,

if you'll notice, the title is a bit confusing...sorry about that...it is just that i really have no idea on what to do with my question...i just heard that these things could probably be the answer...

anyway, right now i have a client-server type application developed in vb, sql - backend.  system is installed in each workstation.  what i want to do is to have a centralized way of connecting to the database. Meaning, i need my sql connection code be separated from the vb code of the application. i would also want that file, if that'll be a file, to be on the server then the application will just have to access or read that file from the server in order to obtain the connection string that will be used to connect to the database...

---> all clients will access/read a certain file from the server then that file will return the connection string to the apps in order for it to log on to the database

i hope you get the picture :) i really need your help on this very badly...a step by step approach and a SAMPLE CODE would be very much appreciated...thanks a lot people...
  • 4
  • 4
  • 2
  • +3
5 Solutions
Julian HansenCommented:
You don't need a COM/DCOM object just to return a connection string. This can be stored in an INI file in a central place or even in the registry of the server to which you make remote registry calls (assuming the remote registry service is available)

From what you are saying in your post is that you actually want to build a 3/n tier application i.e. Iterface, business logic, backend all separate.

In such a situation your client does not access the data directly and therefore does not need to access a connection string. Rather the client app makes calls into the middle tier layer which in turn handles the database connection etc.

Whether you use COM or DCOM will depend on the type of application you are trying to build. Before going into details of how to go about it would be helpful to have some feedback on the above regarding your requirements.
i suggest that u should go for a 3 tier arch. here u can have a COM object running centrally and all the clients can access it. this COM will interact with the database and give back results to the clients. The clients will call a function with all the info needed to connect in return the COM will give back the data. It would be really good it u use MTS as implementing security would be more easy and the appl will be more effective and scalable.
You may decide to avoid this whole scenario by by using Stored Procedures.  They provide the ability to contain the functionality on the server, instead of generating and sending scripts in the VB client.  It's not secure, and a malicious VB client could still send destructive queries, but if you're just interested in centralizing functionality, this may do it.  Stored Procedures shouldn't be intimidating, either.  They're as easy as queries.

If not...

This is a scenario that is very common and has many roadblocks that have been addressed in newer architectures such as MIDAS / DATASNAP and .NET.

In a 3-tier architecture, the client can access the middle tier through webservices, sockets, MessageQueueing (MSQM) or even drop files as a sort of message queueing.  The last method is not recommended, but a very long time ago we did this and it's been working fine ever since in about 10 different systems.

We've also successfully done this with multithreaded socket servers.  We used Delphi for this and it worked much better than VB because of its multithreading capability and exception handling.

DCOM will work, but is not the easiest and is probably not best.  WebServices are very easy with .NET and sockets are very easy using a third party library (there are TONS) and VB comes with a nice one anyway.

I would recommend C# and a webservice as the middle tier (VB.NET works too, if you must).  If you have not made the .NET transition, this would still be very easy.  Download the SDK from Microsoft, then WebMatrix and SharpDevelop, or the personal edition of C#Builder from Borland.  This would require you to use ADO.NET, which is not a far stretch from ADO in its simplest form.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

GedleyAuthor Commented:
wow :) i really appreciate all your answers even though some of it i find higly technical...or its just me? :)

anyway, the common thing that i noticed is the 3-tier architecture..

actually, the requirement is simple...all i have to do is to get the connection string from a certain file like dll which resides in the server...then all my clients will use that connection string to establish a connection to the database and retrieve all the information that i need

its like using udl wherein the parameters that are needed to connect to the database is stored...the difference is, i want a single file that will be accessed by the clients...as much as possible not a udl or ini file for security purposes...then after i read it, i would be able to retrieve the parameters such as username and password, servername, databasename that i need and eventually establish a connection to the database...dont think of it like i would send the request like retrieve a certain number of records from the database, to the centralized file then that file will process the request then sends back the result to the clients...no, i dont want that cos it'll require a lot of changes in my existing codes...

just let me have a single file that will be accessed by the clients to obtain the parameters needed to connect to the database...as much as possible, a secured file...a dll may be...

i dont think i can use the stored procedure...cos how can i be able to execute it if im not logged in to the database...

anyway, thanks again...hope to hear from you guys...
If I understand your end goal correctly, you only want a connection string.

Write a simple TCP or UDP server application that accepts connections and sends the connection string to the client programs.  The connection string would reside in a text file or elsewhere so you could modify it from an outside source for updating the connection parameters.
You could implement some type of encryption into the data transfer so it would be secure.

A simple text file on the server will do this for you. Your clients would read the file from this server location and parse the connection string. You can use simple encryption to protect password information.

Keep it simple
Alternatively, you could use integrated security on the SQL Server if your environment permits. This would allow you to omit needing password to connect to the database from trusted clients.
Julian HansenCommented:

The text file option is out - refer authors comments on security.


There are a number of options - here are a couple you could use

1. Use the INI/Text file but encrypt the information in the file. You can do this by doing something simple like an exclusive OR with the Server Name, Server IP Address or Server MAC address - or all three - anyway something linked to the server that can give you some sort of encryption key.

Create a very simple DLL that has a single exported function that returns the connection string.

Neither method will be very secure because anyone who knows how it is done can replicate the access mechanism and get the connection string.

So, what now.

It depends on why you want to hide the connection string - if it is because of names and passwords contained in the string then there are other possibilities. If you were running on MSSQL you could use integrated security - I don't think My SQL offers that so that is out. There is another method that I have used in the past with a reasonable amount of success and it works like this.

You create a stored procedure that has access to read only 1 table in the database. You create a name and password that has permissions only to execute that stored procedure call the stored proc spLoginCredentials and the name and password login and password respectively. So user login with password password has rights only to run a specific stored procedure (or read from a particular view - doesn't really matter).The connection string is setup with the user name login and password password.

The stored procedure returns a name and password to be used in all future connections but in encrypted form - some form only your application knows how to decrypt.

Process is then as follows.

1. App connects to DB with (login,password) and runs stored proc or selects from view
2. Encrypted name and password values are returned
3. Close connection
4. Decrypt name and password and reestablish connection with new name and password

Advantages of this system is that anyone wanting to hack in would either have to know the encryption algorithm you are using or would have to be reasonably adept at cracking codes - not your average user. Also it means you have control over the name and password used to access the database just by changing the encrypted values in the LoginCredentials table.

I used this on a product I wrote a couple years back - it is still running and has done the job required of it.

julianH, you are funny. You say encrypted text file is out and then you suggest INI file??? And you copy my reply on integrated SQL server secuirity?? You ought pay attention when reading the questions and other replies so you don't repeat what others had said before you.

BTW, the stored procedure suggestion was worth a good laugh!
Julian HansenCommented:

1. Did not see your reference on encryption - the problem was I posted my response without reading it and with a 1 hour gap between the time I started and the time I finished it. If I had re-read it I would have deleted the comment about the text files as it was obviously no longer relevant.

2. I mentioned integrated security only to put it into context - the author specified he is using My SQL which does not support integrated security so that needed to be pointed out but again you were the first to raise the issue on integrated security and well done for that too

3.  Paying attention to what others have written - good point - one should read what others have written to avoid duplication. However, if this was a crime a significant portion of the ee community would be in prison for similar offences. Consider it a side effect of trying to answer one of many questions in a restricted time period.

4. I am glad you derived some amusement from the rest of the suggestions I made - I am not sure why - maybe you can enlighten the rest of us.

5. I am going to give you the benefit of the doubt but I do detect a somewhat derogatory tone in your response which is not in the spirit of this site. If you feel the need to pass negative comments at any posts I have made please feel free to post your email address and we can take this offline.

I find it irritating when people copy previous answers or criticize without even reading them. You made both offences so you are correct about derogatory tone. Since you recognize your fault, no hard feelings on my end.

Anyhow, you are mistaken about mySQL since he is using VB/SQL Server.

For your stored procedure suggestion to work, he would need connection string that would need to be distributed to his clients. The whole point of the question is centralizing the connection info for all clients in one place.
Julian HansenCommented:

I understand the frustration you are referring to - understand though - even though my comment was incorrect - it was never meant as a criticism. I am very sensitive about criticising others. If it came across as critcism I am truly sorry.

My SQL - too many late nights I think. I was answering another My SQL question at the same time - saw the sentence "... my sql connection ..." and got it into my head that My SQL was being used. Based on this Integrated Security would be the best option - which you did point out in your earlier post.

Centralising the connection string: there are actually two issues i) the centralisation ii) the security around the connection string. My suggestion was addressing number (ii) i.e. the connection string can be placed in open text in an INI file. The point of the solution was that if the issue was that SQL credentials would then be available in a plain text file then a way around that would be to only put credentials in the file that were benign. In esscence this reduces to the encrypting the contents of the text file - just an alternative suggestion to achieve the same end.

It seems you're still waiting for a definitive answser.  Here is an example of a file-based messaging system like the one I described.  This has a few limitations, but should work for you.  Firstly, no machine should make simultaneous requests.  Second, each machine shuold use its machine name or IP address as both the request filename and the response filename.  Lastly, all the machines should have read-write access to the queuepath shared resource.  

Building a request file should be done like this.  Create a file that looks like this:


Copy this file to the queuepath directory as "mymachine.tmp".  When it has a .tmp extension, the program will not pick it up.  You do not want the server to pick it up in the middle of copying the file, or it will crash.  When the copy is complete, rename the file from .tmp to .req.  There is code below that shows how to do this.  It's easy.  The rename is always fast enough that the program won't pick it up during the rename.  

As for the server, this is a VB standard .EXE project with a single form containing two buttons, a timer, a listbox and a label.  The rest should be obvious.  Note that the constring is hard-coded.  Change this as you require.

Option Explicit

Private Const queuepath As String = "\\myserver\c$\queuepath\"
Private Const constring As String = _
    "provider=sqloledb.1;data source=mydbserver;user id=sa;password=password;initial catalog=master"

Private Sub cmdStart_Click()
    cmdStart.Enabled = False
    Timer1.Enabled = True
    lblstatus.Caption = "Running"
    cmdStop.Enabled = True
End Sub

Private Sub cmdStop_Click()
    cmdStop.Enabled = False
    Timer1.Enabled = False
    lblstatus.Caption = ""
    cmdStart.Enabled = True
End Sub

Private Sub Form_Load()
    List1.Font.Name = "courier new"
End Sub

Private Sub Timer1_Timer()
End Sub

Private Function withslash(atext As String) As String
    If (Right$(atext, 1) <> "\") _
    And (Right$(atext, 1) <> "/") _
        withslash = atext + "\"
        withslash = atext
    End If
End Function

Private Function changefileext(afilename As String, aextension As String) As String
    Dim i As Integer
    i = InStr(afilename, ".")
    If i <> 0 Then
        changefileext = Left$(afilename, i - 1) & "." & aextension
        changefileext = afilename & "." & aextension
    End If
End Function

Private Function getkeyname(atext As String, adelimiter As String) As String
    Dim i As Integer
    i = InStr(atext, Left$(adelimiter, 1))
    If i <> 0 Then
        getkeyname = Left$(atext, i - 1)
        getkeyname = atext
    End If
End Function

Private Function getkeyvalue(atext As String, adelimiter As String) As String
    Dim i As Integer
    i = InStr(atext, Left$(adelimiter, 1))
    If i <> 0 Then
        getkeyvalue = Right$(atext, Len(atext) - i)
        getkeyvalue = ""
    End If
End Function

Private Sub doprocess()
    'prepare the search mask
    Dim path As String
    path = queuepath
    Dim requestfilename As String
    Dim responsefilename As String
    Dim tempresponefilename As String
    Dim requesttype As String
    Dim infile As Integer
    Dim outfile As Integer
    'find a file to process.  Repeat until no file found.
    Dim s As String
    'search for a file
    requestfilename = Dir(path, vbNormal)
    While True
        'if no file found, we're done.
        If requestfilename = "" Then Exit Sub
        If Right$(requestfilename, 4) = ".req" Then
            'open the file we found.
            List1.AddItem "Got Request----"
            infile = FreeFile
            Open withslash(queuepath) & requestfilename For Input As #infile
            'read the file and extract pertinent values
            While EOF(infile) = False
                Input #infile, s
                If UCase$(getkeyname(s, "=")) = "REQUESTTYPE" Then requesttype = UCase$(getkeyvalue(s, "="))
                If UCase$(getkeyname(s, "=")) = "RESPONSEFILE" Then responsefilename = getkeyvalue(s, "=")
            'create a response file.
            tempresponefilename = changefileext(responsefilename, "tmp")
            outfile = FreeFile
            Open withslash(queuepath) & tempresponefilename For Output As #outfile
            'fulfill the request
            If (requesttype = "GETCONNECTIONSTRING") Then
                Print #outfile, "CONNECTIONSTRING=" & constring
            End If
            'if (requesttype = "DOSOMETHINGELSE") Then...
            'End If...
            'close the respone file.
            Close #outfile
            'rename the response file from temporary filename to desired filename.
            'the temporary naming is very important.  This makes the file invisible to the requestor
            'until it is complete, andis fast enough that it won't "find" the file
            'before it becomes available.
            Name withslash(queuepath) & tempresponefilename As withslash(queuepath) & responsefilename
            'close the request file.
            Close #infile
            'delete the request file so we don't process it twice.
            Kill withslash(queuepath) & requestfilename
            List1.AddItem "  RequestType : " & requesttype
            List1.AddItem "  RequestFile : " & requestfilename
            List1.AddItem "  ResponseFile: " & responsefilename
            List1.ListIndex = List1.ListCount - 1
        End If
        'get the next filename
        requestfilename = Dir
End Sub

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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