Solved

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

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Modal Popup Extender control 1 18
Count with a subquery showing details 10 42
cannot connect to sqlserver 8 25
Text file into sql server 5 21
In this article I will describe the Backup & Restore 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.
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 tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

785 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