SQL Connection error 1

[lilboi]

Hey guys,

I've written all my vb codes to work with ms access and now we have set up an SQL server and will be changing the connection string to connect to it remotely.

Problem is, it won't connect and i don't know why.

I have tried all combination of connection but none won't work. I have initially connected to an sql database within my intranet but when i try to access the new sql server from internet, it won't work.

We have created a database called Test, a table called Test, and field called Test. We have forwarded the router to port 1433 towards the server as well.

I have tried these combinations and all gave me the same error:

cString = "Provider=SQLOLEDB.1;Password=jp;Persist Security Info=True;User ID=John;Initial Catalog=Test;Data Source=99.99.0.999"

or

cString = "Provider=SQLOLEDB.1;Server=VCCC2;Database=Test;Uid=John;Pwd=jp;Data Source=99.99.0.999"

Error:
[Microsoft][ODBC SQL Driver][DBNETLIB]SQL Server does not exist or access denied.

I'm not sure how to set up the sql database to allow persistent security or anything. We need it to connect to the sql db or else we wasted all our time on this for nothing..

Thanks guys!

 

[johnwm]

Check out this list:

http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForSQLServer

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[Jasen]

If your code works from your intranet, but not from an external location, your problem is not the connection string. You have routing/firewall issues.

 

[lilboi]

johnwm: i looked into it and tried:

To connect to SQL Server running on a remote computer (via an IP address)

oConn.Open "Provider=sqloledb;" & _
"Network Library=DBMSSOCN;" & _
"Data Source=xxx.xxx.xxx.xxx,1433;" & _
"Initial Catalog=myDatabaseName;" & _
"User ID=myUsername;" & _
"Password=myPassword"
Where:
- "Network Library=DBMSSOCN" tells OLE DB to use TCP/IP rather than
Named Pipes (Q238949)
- xxx.xxx.xxx.xxx is an IP address
- 1433 is the default port number for SQL Server. Q269882 and Q287932
- You can also add "Encrypt=yes" for encryption

But it still didn't work. I have since then tried to connect to my sql server thru intranet, then set my router to point 1433 to sql server and tried to connect thru internet. I changed the ip and port # on my connection string and it doesn't work.

It is true that problem may not be in connection string but I already have forwarded the port to sql server 1433.

I'm still getting the same error as of now...

Any help will help us a lot!

On a side note, the program is written in visual basic and is suppose to be used in different computers all accessing the sql server thru internet. Will sql allow multiple connections thru port 1433? If not, then we have wasted 3 months on this program...

 

[fredericofonseca]

The firewall must be configured to permit port 1433 incoming (or the port numbers that SQL Server listens to on TCP/IP), and ports 1024 to 65535 outgoing.

You should speak with your network guyes to see what type of routing and firewall rules you have active, and see if some of them can be relaxed.

Although ports mentioned are up to 65535, you may need only 1024 to 4000 for example, or even less.

Ports can also be changed if required.
from

http://msdn2.microsoft.com/en-us/library/ms175483.aspx

For example, consider a computer running one default instance and two named instances of the SQL Server Database Engine. The computer is configured such that the network addresses that the three instances listen on all have the same IP address. The default instance would listen on TCP port 1433, while the other named instances could listen on TCP ports 1434 and 1954, respectively. The network administrator would then configure the firewall to forward network requests for UDP port 1434 and TCP ports 1433, 1434, and 1954 on that IP address.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[gmmastros]

SQL Server's default port is 1433, but it doesn't have to be that number. There is a way to tell what the port SQL Server uses.

On the computer hosting the database...

Start -> Run
type svrnetcn

The SQL Server Network Utility will open. On the general tab, you'll see 'Instances on this server'. If you only have 1 instance, then there will only be 1 item in the combo box. Make sure the correct instance is selected.

Next, you should see TCP/IP as an enabled protocol. There may be others listed, and you may need them there, but on my server, ONLY tcp/ip is enabled.

Next, click TCP/IP, and then click the properties button. A configuration screen will open. The port SQL Server is using (for the selected instance) will be displayed. If it's not 1433, then you've probably found your problem. Whatever port is listed needs to be forwarded through your firewall.

Also remember that multiple firewalls may need to be configured. Obviously, the firewall on your router needs to be configured to forward this port to the computer hosting the database, but that computer may also have a firewall enabled, so you'll need to configure that also.

Good Luck.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[lilboi]

Thanks for you help Frederico and George! I went thru the things you've said.

I've checked and found there is only the default instance in of sql. Also checked that port was indeed 1433. Unfortunately I am the "network guy" in our company of 2. hahaha! Anyways, i did set router to forward to port 1433 tcp/ip and 1434 udp. The server computer firewall is off as well.

I took out named pipes and found that vb won't work with it thru intranet without it but that's not the main case.

Would it matter if the client comp's firewall is on? I turned it off but I get my internet connection thru wireless(which means has router and firewall).

As for outgoing ports for sql, i tried setting inbound to port 1433 and outgoing to 1024 to 4000. It doesn't seem to let me set that much for just outgoing.

I've read threads here and it mentioned that i should be able to telnet <ip addr> 1433 but when i tried that, it wouldn't work for me. How would i go about allowing to be able to telnet to it?

 

[gmmastros]

I can only think of 2 reasons why you wouldn't be able to telnet to the SQL Server port.

1) SQL Server is not listening to that port.
2) There is a firewall blocking the port.

This is why telnetting to the port is a good troubleshooting step.

I suggest you try telnetting to the port from within your local area network. This will isolate the internet router/firewall. If you can telnet from within the lan, but not from outside the lan, then the internet router/firewall is the culprit. In this case, you may want to call customer support for the router that you own.

However, if you cannot telnet from within the lan, then you need to make that work before anything else.

** Originally, our company used a d-link router. With this router, you can forward ports. It took us several days before we realized that we needed to re-boot the router before the changes took affect because the 're-boot' router was further down and we didn't see it at first.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[lilboi]

hhmm just double check if i'm doing telnet properly here,
I open my ms-dos prompt, i type in telnet <local intranet ip> 1433

It gives me "Could not open connection to the host on port 1433: Connection Failed.

I have tried to telnet to other ports that are forwarded by the router to diff computers as well with no luck. I know my router automatically updates the port forwarding as I had previous exprience on it with other apps before.

 

[gmmastros]

On my computer....

Start -> Run
CMD

At command prompt...

Ping osiserver

C:\Documents and Settings\George.OSI>ping osiserver

Pinging osiserver [192.168.0.3] with 32 bytes of data:

Reply from 192.168.0.3: bytes=32 time<1ms TTL=128
Reply from 192.168.0.3: bytes=32 time<1ms TTL=128
Reply from 192.168.0.3: bytes=32 time<1ms TTL=128
Reply from 192.168.0.3: bytes=32 time<1ms TTL=128

Ping statistics for 192.168.0.3:
    Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
    Minimum = 0ms, Maximum = 0ms, Average = 0ms

My sql server is configured for port 1433, if I do...

telnet 192.168.0.3 3341

C:\Documents and Settings\George.OSI>telnet 192.168.0.3 3341
Connecting To 192.168.0.3...Could not open connection to the host, on port 3341:
 Connect failed

If I do....

telnet 192.168.0.3 1433

I get a blank telnet window with a blinking cursor.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[chiph]

George didn't explicitly say so, but a blank window with a blinking cursor is an indication of successly connecting via Telnet.


Chip H.


____________________________________________________________________
Donate to Katrina relief:

http://s1.amazon.com/paypage/PELYGQVJ8Q7IB/103-6821258-5919825

If you want to get the best response to a question, please read FAQ222-2244 first