Solved

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

Posted on 2015-02-23
5
122 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 15

Expert Comment

by:gplana
ID: 40626580
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
ID: 40626679
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
ID: 40626767
Have you tried to change the colon (:) by a comma (,) ?
0
 
LVL 1

Author Comment

by:rkulp
ID: 40626798
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
ID: 40626834
My pleasure! Happy to know that it worked.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard 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.
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

751 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