Remote connection to SQL Server results in "Unable to connect to the target server"

Question

Hi,

I'm trying to remote connect to SQL Server using the URL "http://MyPublicIP,1433" where I received "Unable to connect to the target server". The following are set:

• TCP\IP and Pipeline protocols and shared memory are enabled. SQL Server and browser are running.
• Remote access is allowed.
• TCP port 1433 is fixed.
• Windows and SQL Server authentication is checked.
• The TCP connection to port 1433 and UDP connection to port 1434 are firewall allowed. Same for sqlservr.exe.
• A static IP address, either for public or local, has been set.
• Port forwarding is set for port TCP 1433 and UDP 1434

Need help.

Answer 1

SQL Server does not speak HTTP, so there is no surprise that you get that error. SQL Server speaks it own protocol, TDS (Tabular Data Stream).

If you tell from where you are connecting and what you really want to do, we may be able to help you further.

Answer 2

Hi @Said El Noshokaty ,

Welcome to Microsoft Q&A!
Please check these documents to see if something has been missing and troubleshooting.
Check the process:
How to configure remote access and connect to a remote SQL Server instance with ApexSQL tools
Enable Remote Connections to SQL Server using IP address
Tutorial: Getting Started with the Database Engine--which is an official document.
Toubleshooting:
Solving Connectivity errors to SQL Server

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

To ErlandSommarskog

Hi,

I have an Access database built to capture data from a remote database (SQL Server or any other) at an anonymous customer. To test its possibility, I used the following Access subroutine to capture data on a SQL Server database located on the same machine:

Sub CaptureData()

Dim url As String, filePath As String, attempts As Integer
Dim WinHttpReq As Object, oStream  As Object
url = "http://MyPublicIP\DESKTOP-0EKQG3O\SQLEXPRESS,1433/sosdata/cargo/brooker")
filePath = "c:\sos\SQLServerExtract.txt"
attempts = 3
Set WinHttpReq = CreateObject("MSXML2.XMLHTTP.6.0")
On Error GoTo TryAgain

TryAgain:
attempts = attempts - 1
Err.Clear
If attempts > 0 Then
WinHttpReq.Open "GET", url, False
WinHttpReq.Send
url = WinHttpReq.ResponseBody

   If WinHttpReq.Status = 200 Then
      Set oStream = CreateObject("ADODB.Stream")
      oStream.Open
      oStream.Type = 1
      oStream.Write WinHttpReq.ResponseBody
      oStream.SaveToFile filePath, 2 ' 1 = no overwrite, 2 = overwrite
      oStream.Close
   End If
Else
   MsgBox ("A4. No internet connection is available.")
End If

End Sub

The captured data is then inserted in the Access database. If SQL Server does not speak HTTP as you said, then the above-mentioned subroutine cannot be used. Does this apply to all DBMS? Please advise what sort of API I may use to do the same function within Access, other than ODBC because table structures may not be the same. If API within Access is not possible, what the customer should do so that I can connect to his DBMS via HTTP?

Regards.

Answer 4

To: Seeya

Hi,

Thank you for the links you have provided. Please read my answer to ErlandSommarskog, where I explained what I want to do. All the links wont help. Sorry if I were not clear enough. Please advise alternative solutions.

Regards

Answer 5

For completeness, I should clarify one thing: you could talk with SQL Server over HTTP, if someone provides a proxy that takes the HTTP requests and relay them to SQL Server over TDS. I am not of any such products, but I would not be surprised if there are any such products.

A for other DBMS products, I have no idea. Maybe some products exposes themselves on port 80. (As a matter of fact, SQL 2005 and SQL 2008 supported SOAP requests on port 80, but that functionality was dropped.)

The normal way to contact SQL Server from Access would be ADO, I think. (I'm not an Access person). Because is a very bad idea to expose SQL Server directly on the internet, your customer would have to set up VPN connection.

For more details on writing data-access code from Access, you may be better off if you add a tag for Access to your post.