Solved

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

Posted on 2015-02-23
5
117 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
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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Import Data from Multiple Text Files in Excel 12 64
Finding the IIS version 5 22
VS 2015 is giving [No relevant source lines] 2 30
How can I use this function? 3 13
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
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…

821 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