(Remote) SQL database 3

[MattSTech]

Someone please tell me that I am not dreaming and I can use VB6 to connect to a web hosted (Remote) SQL database.
This seems to be such a simple thing and yet everything I am reading is indicating the db must reside on the local network?

I try to connect using the following string (with server and user names changed for their protection).

 Conn = "Provider=sqloledb;Data Source=winsql01.webserver.com,1433;Initial Catalog=master;User Id=sa;Password=superpassword"

please tell me this can be done without resorting to asp.net or vb.net...
thanks

 

[vb5prgrmr]

Yes it can, have a look at

http://www.connectionstrings.com

for the proper format of the DNS Less Connection String...



Good Luck

 

[MattSTech]

Ok, good a glimmer of hope. I am familiar with connectionstrings.com, however I could not make the connection with DNS-Less, and not. The closest thing I come up with is the composite:

strMainConn = "Driver=Composite 4.5;HOST=winsql01.webserver.com;PORT=1433;UID=sa;PWD=superpassword; DOMAIN=composite;DATASOURCE=PDSI;"

"Datasource not found and no default driver specified" is the error I get.

More digging to come, however, the SQL server links and pages are either not clear enough for me to get what is going on or it is too late for me to attempt to read anymore.

 

[MattSTech]

Well a bit more into it,

strMainConn = "Driver={SQL Server};Server=winsql01.webserver.com;" & _
                    "Address=winsql01.webserver.com,1433;Database=master;" & _
                    "UserID=sa;PWD=superpassword;Trusted_Connection=yes;"

Gives me the same error as the OP, but I think I am on the right path.
Did some more searching on tek-tips. Found a bit more information on DNS Less connections. Still not at the solution.

 

[dilettante]

I would think using ODBC would just add to your woes here, go back to OLEDB instead.

Most people won't open TCP 1433 on firewalls because of things like the Slammer Worm. SQL Server remains vulnerable just because it is a popular target. Most people will only allow this via a VPN.

It is usally better to split off your data access and business logic into a middle tier that "lives" near the database, then have your remote presentation tier use HTTP or other TCP protocols as the path between them.


Cheap Web hosting often limits your options, seldom allowing you to open arbitrary ports or run arbitrary servers exposed to the outside. That's why the use of protocols layered over HTTP have become so popular. You don't have to use SOAP, but no matter what you use you'll have to re-architect away from a monolithic model to something based on disconnected data access. Internet latency and bandwidth considerations usually make this desireable anyway.

ASP.Net isn't required but you have to implement the remote access protocol somehow.

 

[vb5prgrmr]

>Most people won't open TCP 1433 on firewalls because of things like the Slammer Worm. SQL Server remains vulnerable just because it is a popular target. Most people will only allow this via a VPN.

For SQL 2k see SP3 or SP4 (? on the numbers) but ran across it the other day and quick glance showed it had the fix for the slammer...

This is the format I find at connectionstrings.com (for 2k & 7)
[tt]
Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;
[/tt]

so I am wondering if you need to convert the http: // www. somesite .com into an IP address...



Good Luck

 

[strongm]

>please tell me this can be done

Sure it can. And your connection string looks fine to me. However, as dilettante points out, it is pretty likely that port 1433 is not open on your host's firewall

 

[MattSTech]

dilletante>
Thank you for the reply - I think you bring up excellent points. I have been told to change the default ports when I set up a local SQL server so I am familiar with the vulnerabilities). I only used 1433 because the connection string shown on the admin page of the SQL server stated 1433. (It gave me the example connection string).
I suppose it is cheap web hosting, $10 a month including a M$ SQL Server is a bit low, but I am a rookie when it comes to ASP.NET and web programming in general. I used it for simple utilities up until recently where I found a need to expand my use.

vb5prgrmr>
Thank you as well for the help and taking the time to keep with this. I will look into the use of the IP and post back as soon as I find a minute.

strongm>
Thank you for taking the time to respond.
Mentioned above to dilletante, 1433 was provided by the host (albeit on an example page). I will try the IP first but I think the example might just be for server side connections (ASP etc.). This is a titch frustrating, but understandable. Perhaps they would have ports open for outside connections, and I could ask them about this.

Regardless, I will try a few things and get back to everyone.

 

[strongm]

>I will look into the use of the IP

If there is a public DNS entry for winsql01.webserver.com (or whatever your server is actually called), then using the IP address should not make any difference ...

 

[MattSTech]

Well crud, I phoned my host and they said it was a no go for remote connections.

So in summary, my connection string would have worked fine as written in the OP, however, the server did not allow remote connections.

Now for the next question:

I would like to use a VB6 app to write items to a remote database that doesn't allow remote connections.
I feel strongly that I could figure out how to place a file in the web space (using inet or similar).
Then I suppose I could figure out how to make a script in asp.net to parse the file and make updates to the server.

I would just write the file to the web space then do a loop checking to see if it is there. Then perhaps launch the script in the internet control to activate with my VB app?

 

[dilettante]

Too bad you don't have ASP available instead of ASP.Net, because it's a better pairing with a VB6 client.

With ASP you could use ADO and disconnected Recordsets.

Using ASP.Net you're probably going to have more work to do either at the server or the client to deal with the "impedance mismatch.

 

[MattSTech]

My hosting package supports asp script, but I don't know enough about it. The only thing I know about web programming is the few tidbits I have cobbled together with asp.net.

But...

I just happen to have an old asp 3.0 (wrox) book I purchased from the bargain book store about 7 years ago and never read. (From my "Who needs to learn web programming when I've got good old VB6" days) On page 474 it talks about ADO... And to my surprise it even looks a bit like VB. I will tool with this a bit.

I will be a bit learning about this. I will post back after the weekend unless it comes to me quickly. Thanks again for the direction guys!

 

[MattSTech]

Wow. Consider my horizons expanded.
This should be pretty easy pulling the data I need now. Thank you again so much for the direction - stars all around.

<%Option Explicit%>
<HTML>
<HEAD>
<TITLE>The Name of My Page</TITLE>
</HEAD>
<BODY>

<%
  Dim adOpenForwardOnly, adLockReadOnly, adCmdTable
  adOpenForwardOnly = 0
  adLockReadOnly = 1
  adCmdTable = 2

  Dim objConn, objRS

  Set objConn = Server.CreateObject("ADODB.Connection")
  Set objRS = Server.CreateObject("ADODB.Recordset")

  objConn.Open "Provider=sqloledb;Data Source=winsql01.webserver.com,1433;Initial Catalog=master;User Id=sa;Password=superpassword"

  objRS.Open "tblWhatMyTableIsNamed", objConn, adOpenForwardOnly, adLockReadOnly, adCmdTable

  While Not objRS.EOF
    Response.Write objRS("ColumnName") & "<BR>"
    objRS.MoveNext
  Wend

  objRS.Close
  objConn.Close
  Set objRS = Nothing
  Set objConn = Nothing

%>

</BODY>
</HTML>