Solved

How to Programmatically Create SQL Connection String When Using Port Forwarding To Second SQL Server

Posted on 2015-02-23
5
105 Views
Last Modified: 2015-02-23
I have used port forwarding to connect to a server running SQL Server 2008 R2 Express. It works great. All I have to do is forward port 1433 to port 1433 on the server and set all the firewall and configuration things right. Now, I want to be able to have a second server running SQL Server Express and be able to route to that server by forwarding port 8081 to an appropriate port on the second server, probably 1433 as well. The port number is not really important here. The problem is the connection string. I need to be able to set the server, userid and password programmatically based on user input. I wrote a small test program to work out the string. If I have the port in the url (e.g., www.rkulp.us:8081) then the string is invalid. So, can I do this and, if so, how?
Public Class Form1
    Dim strConn As String = ""
    Dim gMsg As String = ""
    Private Sub btnTestConnection_Click(sender As System.Object, e As System.EventArgs) Handles btnTestConnection.Click
        strConn = "Data Source="
        strConn += Trim(txtServer.Text) + ";Initial Catalog=HouseholdInventory;Persist Security Info=True;User ID="
        strConn += Trim(txtLoginName.Text) + ";Password=" + Trim(txtPassword.Text)
        Dim TestConn As New SqlClient.SqlConnection
        TestConn.ConnectionString = strConn
        Try
            TestConn.Open()
            btnTestConnection.Text = "Successful Connection"
            btnTestConnection.BackColor = Color.Green
        Catch ex As Exception
            gMsg = "The following error occurred trying to build the connection:" + vbCrLf + ex.Message
            If Not IsNothing(ex.InnerException) Then gMsg += vbCrLf + "Inner Exception:" + vbCrLf + ex.InnerException.Message
            gMsg += vbCrLf + "Connection String: " + vbCrLf + strConn
            MsgBox(gMsg)
            btnTestConnection.Text = "Connection Failed"
            btnTestConnection.BackColor = Color.Red

        End Try
        btnReset.Enabled = True
    End Sub

    Private Sub btnReset_Click(sender As System.Object, e As System.EventArgs) Handles btnReset.Click
        btnTestConnection.BackColor = Color.LightGray
        btnReset.Enabled = False
        btnTestConnection.Text = "Test Connection"
    End Sub
End Class

Open in new window

0
Comment
Question by:rkulp
  • 3
  • 2
5 Comments
 
LVL 15

Expert Comment

by:gplana
Comment Utility
So if I understand well, the problem is the port number. You should just to use a comma to specify it on the connection string. Something like:

mycomputer.test.xxx.com,1234

So in your case, suppossing that you have the port on the StrPort variable:

Public Class Form1
    Dim strConn As String = ""
    Dim gMsg As String = ""
    Private Sub btnTestConnection_Click(sender As System.Object, e As System.EventArgs) Handles btnTestConnection.Click
        strConn = "Data Source="
        strConn += Trim(txtServer.Text) + "," + strPort + ";Initial Catalog=HouseholdInventory;Persist Security Info=True;User ID="
        strConn += Trim(txtLoginName.Text) + ";Password=" + Trim(txtPassword.Text)
        Dim TestConn As New SqlClient.SqlConnection
        TestConn.ConnectionString = strConn
        Try
            TestConn.Open()
            btnTestConnection.Text = "Successful Connection"
            btnTestConnection.BackColor = Color.Green
        Catch ex As Exception
            gMsg = "The following error occurred trying to build the connection:" + vbCrLf + ex.Message
            If Not IsNothing(ex.InnerException) Then gMsg += vbCrLf + "Inner Exception:" + vbCrLf + ex.InnerException.Message
            gMsg += vbCrLf + "Connection String: " + vbCrLf + strConn
            MsgBox(gMsg)
            btnTestConnection.Text = "Connection Failed"
            btnTestConnection.BackColor = Color.Red

        End Try
        btnReset.Enabled = True
    End Sub

    Private Sub btnReset_Click(sender As System.Object, e As System.EventArgs) Handles btnReset.Click
        btnTestConnection.BackColor = Color.LightGray
        btnReset.Enabled = False
        btnTestConnection.Text = "Test Connection"
    End Sub
End Class
  

Open in new window


Hope it helps.
0
 
LVL 1

Author Comment

by:rkulp
Comment Utility
Thanks for your quick reply. I still get the same error. I think the problem really is the www.rkulp.us:8081 which is not the server name, just the place I point to that is then forwarded to the server. Anyway, here is the error:

error-message.png

It may be that this can't be done. If so, I can restrict the use to only the situation where there is only one server.  This is intended to be on the first use screen where the user puts in the server, userid and password which is then used to modify the config file for future use. It is unlikely a user would have this problem but I have it now.
0
 
LVL 15

Accepted Solution

by:
gplana earned 500 total points
Comment Utility
Have you tried to change the colon (:) by a comma (,) ?
0
 
LVL 1

Author Comment

by:rkulp
Comment Utility
gplana,
After setting SQLServer to respond to port 8081, configuring the firewall likewise and changing the colon to comma, it worked. Thanks so much.
0
 
LVL 15

Expert Comment

by:gplana
Comment Utility
My pleasure! Happy to know that it worked.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

762 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

7 Experts available now in Live!

Get 1:1 Help Now