System DSN using IP address

Remote connections are not enabled by default so any reference to anything other than (local) or the local server name will not work. Read the following to enable remote connections.

http://support.microsoft.com/kb/914277

Cheers,
Tommie

Also you do not need the leading backslashes.  203.11.2.1\SQLEXPRESS is a valid SQL name just like MYSERVER\SQLEXPRESS

make sure that SQLEXPRESS is the correct instance name. you can see the instance name in control panel - administrative tasks - services-

is there any way I can post my screen captures here

Not sure but I know you can upload pics to a site like picasaweb.google.com and I know you can paste a link. I have not tried posting a picture directly.

guys I have tried pretty much everything  but no luck

Ah, there is one more trick you can try.  Wrap the IP and instance in solid brackets. [203.11.2.1\SQLEXPRESS]  I have found a few bugs with SQL in regard to this.

tried as well didnt work, is there any way to confirm what SQL service I am running coz mine is just saying my PC name which is "KASHIFPC"

OK, did you enable remote connections for your installation of SQL 2005?  NOTE: It is disabled by default.

If you haven't you need to go to Programs | Microsoft SQL Server 2005 | Configuration Tools | SQL Server 2005 Surface Area Configuration.

Select the 'Surface Area Configuration for Services and Connections'

You will see the Remote Connections option.  Select it and enable remote connections.

Hi Tedbilly i have tried that didnt work

Can you do the following:

Show me the DSN string you are using to connect.
On the command line run 'tracert <SQL Server Name or IP>'

sorry ted for late response - I was away
do you want me to run this on my client or on computer.
I am trying to connect to my server using the IP address 203.1.1.1.
I am using ODBC Manager to create system DSN, is there any way I can export the DSN String

any ideas any one

Sorry, I had marked the email as read and forgot about this one.  Anyway I figured it out!  I was trying to use 127.0.0.1 and had the same error until . . .

I fired up the SQL Server Configuration Manager.  I then selected 'Protocols for MSSQLSERVER'.  I then doubled clicked on TCP/IP and selected Enable on the first tab.  I also selected IP Addresses (the second tab) then enabled port 127.0.0.1 so you will have to do the same except for port 203.1.1.1

Thanks tedbilly for your help
I went to TCP IP Properties | IP Addresses
There are 3 IP Settings - Does it seem ok to you?
=====================================
IP1
Active: Yes
Enabled: Yes
IP Address: my IP Address (Static) 203.1.1.145
TCP Dynamic Ports:0
TCP Port:<<<BLANK>>>
=====================================
IP2
Active: Yes
Enabled: Yes
IP Address: 127.0.0.1
TCP Dynamic Ports:0
TCP Port:<<<BLANK>>>
=====================================
IPALL
TCP Dynamic Ports:0
TCP Port:<<<BLANK>>>

Yes, if you don't need to use 127.0.0.1 I'd disable it.  You can use (local) instead.  I only used 127.0.0.1 for an example.  Cheers.

ted sorry m still having the problems
is it possible if the router is blocking it, is there anyway I check if its been blocked

any idea ted

Hi, sorry, I've been offline a few days.  If you want to test the route you can use 'tracert 203.1.1.145' on the command line.  Another thing you can try on the command line is 'telnet 203.1.1.145 1433'  That will tell you if the port is blocked.

when i do the connection using telnet 203.1..1 1433 it is saying couldnt connect
which means i have get teh it guys to open that port - right?

Yes, the port is blocked.  If it was successfully you would have seen a blinking cursor sitting there waiting for a request.

ok I have opened the port, but when I spoke to network team they are saying that port is open but there is nothing running on that port. how can I confirm if SQL server is running on port 1433

Hi ted I think the problem is my SQL server not the firewall
I tried the following commands and I got this answer

C:\>sqlcmd -s(local)\sqlexpress
HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re may be caused by the fact that under the default settings SQL Server does not
 allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

C:\>netstat -an

Active Connections

  Proto  Local Address          Foreign Address        State
  TCP    0.0.0.0:25             0.0.0.0:0              LISTENING
  TCP    0.0.0.0:135            0.0.0.0:0              LISTENING
  TCP    0.0.0.0:445            0.0.0.0:0              LISTENING
  TCP    0.0.0.0:1025           0.0.0.0:0              LISTENING
  TCP    0.0.0.0:1196           0.0.0.0:0              LISTENING
  TCP    0.0.0.0:3389           0.0.0.0:0              LISTENING
  TCP    127.0.0.1:1029         0.0.0.0:0              LISTENING
  TCP    203.xxx.xxx.xxx:139    0.0.0.0:0              LISTENING
  TCP    203.xxx.xxx.xxx:3389   192.168.147.2:4178     ESTABLISHED
  UDP    0.0.0.0:445            *:*
  UDP    0.0.0.0:500            *:*
  UDP    0.0.0.0:1033           *:*
  UDP    0.0.0.0:1078           *:*
  UDP    0.0.0.0:1434           *:*
  UDP    0.0.0.0:3456           *:*
  UDP    0.0.0.0:4500           *:*
  UDP    127.0.0.1:123          *:*
  UDP    127.0.0.1:1900         *:*
  UDP    203.xxx.xxx.xxx:123    *:*
  UDP    203.xxx.xxx.xxx:137    *:*
  UDP    203.xxx.xxx.xxx:138    *:*
  UDP    203.xxx.xxx.xxx:1900   *:*

C:\>

When you enabled remote connections what type did you enable?  You have to allow TCP/IP connections as well as named pipes.  I don't see that the server is listening on port 1433 so that means you haven't enabled remote connections for TCP/IP

ok I have tried every combination i can think off TCP/IP is enabled

I get this now when i do netstat -an
its saying that port 1433 is running on ip 0.0.0.0. what does that mean.

netstat -an

Active Connections

  Proto  Local Address          Foreign Address        State
  TCP    0.0.0.0:25             0.0.0.0:0              LISTENING
  TCP    0.0.0.0:80             0.0.0.0:0              LISTENING
  TCP    0.0.0.0:135            0.0.0.0:0              LISTENING
  TCP    0.0.0.0:443            0.0.0.0:0              LISTENING
  TCP    0.0.0.0:445            0.0.0.0:0              LISTENING
  TCP    0.0.0.0:1025           0.0.0.0:0              LISTENING
  TCP    0.0.0.0:1433           0.0.0.0:0              LISTENING
  TCP    0.0.0.0:3389           0.0.0.0:0              LISTENING
  TCP    127.0.0.1:1035         0.0.0.0:0              LISTENING
  TCP    127.0.0.1:1055         0.0.0.0:0              LISTENING
  TCP    127.0.0.1:1055         127.0.0.1:1058         ESTABLISHED
  TCP    127.0.0.1:1056         0.0.0.0:0              LISTENING
  TCP    127.0.0.1:1056         127.0.0.1:1057         ESTABLISHED
  TCP    127.0.0.1:1057         127.0.0.1:1056         ESTABLISHED
  TCP    127.0.0.1:1058         127.0.0.1:1055         ESTABLISHED
  TCP    192.168.33.14:139      0.0.0.0:0              LISTENING
  TCP    192.168.33.14:1943     203.***.***19:80       CLOSE_WAIT
  TCP    192.168.33.14:3389     203.***.**.***:1487   ESTABLISHED
  UDP    0.0.0.0:445            *:*
  UDP    0.0.0.0:500            *:*
  UDP    0.0.0.0:1029           *:*
  UDP    0.0.0.0:1064           *:*
  UDP    0.0.0.0:1434           *:*
  UDP    0.0.0.0:3456           *:*
  UDP    0.0.0.0:4500           *:*
  UDP    127.0.0.1:123          *:*
  UDP    127.0.0.1:1030         *:*
  UDP    127.0.0.1:1253         *:*
  UDP    127.0.0.1:1900         *:*
  UDP    192.168.33.14:123      *:*
  UDP    192.168.33.14:137      *:*
  UDP    192.168.33.14:138      *:*
  UDP    192.168.33.14:1900     *:*

anything I can do to give you more info ted

Hi, sorry I've been a little busy at work!  Hmm, this is really odd.  I compared your netstat results to my own installation and other than different subnet's they are virtually identical!

This one is really odd.  We must have missed something.  Does SQL express include the 'Surface Area Configuration Tool'?

yes it has the surface are config tool
I have pretty much enabled everything.
Is there anything I can do it there.

When you open the "Surface Area Configuration Tool", select "Surface Area Configuration for Services and Connections"

Under 'Database Engine', select 'Remote Connections';  The values should be 'Local and Remote Connections' and for now select 'Using TCP/IP Only'

hi ted tried this as well.
Is there anyway I can post (secure information) to you or email the document with screen captures so that you can have a look.

Sure, send it to tedbilly AT hotmail dot com

Also, ZIP it with a password and post the password here if you want to make sure the information is protected.

Hello Any EE who can help me with this I still cant connect to the database using System DSN

Can you add the text for the System DSN as an attachment?

Hello tedbilly I am creating the system DSN using the ODBC function in Control Panel>Adminsitrative Tool>ODBC

Please see attached pdf for the screen shots and error I am getting.
I think my problem is that I am not sure what is the correct instance of SQL server eg is it SQLEXPRESS or SQLserverExpress etc.

ODBC-Error.pdf

Yes you have the wrong syntax for the server.  It should be

The following is the WRONG syntax: \\202.164.17.27\SQLEXPRESS
The following is the WRONG syntax: //202.164.17.27/SQLEXPRESS

The correct syntax: 202.164.17.27\SQLEXPRESS

My first command mentioned that.

Sorry about that
I have changed it as suggested but still getting the error
please see attached
Let me know if you want the screen shots of my SQL Server config.
ODBC-Error-v1.pdf

Yes I'd like to see screen shots of the configuration.

see attached is there anyway I can remove attachments later
Microsoft-Word---sql-server-conf.pdf

Hmm, something very bizarre is happening. I have one last suggestion.

Do you know how to edit the hosts file on your client?  It's located in c:\Windows\System32\Drivers\etc

Add an entry like
202.164.17.27 MYSQLSERVER

Then use the following to connect
MYSQLSERVER\SQLEXPRESS

NOTE: Why did the IP change from your original question?

I did those changes and still get the error
The reason I changes the IP address coz I have to change the host/provider.

See attached pdf
Microsoft-Word---ODBC-error-v2.pdf

I am 100% convinced thet the port is being blocked on your side or the providers.  If you can't connect using telnet then that has to be the issue.

I did the netstat -an and got this

Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.

C:\Documents and Settings\Administrator>netstat -an

Active Connections

  Proto  Local Address          Foreign Address        State
  TCP    0.0.0.0:80             0.0.0.0:0              LISTENING
  TCP    0.0.0.0:135            0.0.0.0:0              LISTENING
  TCP    0.0.0.0:445            0.0.0.0:0              LISTENING
  TCP    0.0.0.0:1025           0.0.0.0:0              LISTENING
  TCP    0.0.0.0:1112           0.0.0.0:0              LISTENING
  TCP    0.0.0.0:3389           0.0.0.0:0              LISTENING
  TCP    0.0.0.0:8098           0.0.0.0:0              LISTENING
  TCP    0.0.0.0:8099           0.0.0.0:0              LISTENING
  TCP    127.0.0.1:1031         0.0.0.0:0              LISTENING
  TCP    202.164.17.27:139      0.0.0.0:0              LISTENING
  TCP    202.164.17.27:1112     202.164.17.27:4613     ESTABLISHED
  TCP    202.164.17.27:1112     202.164.17.27:4637     ESTABLISHED
  TCP    202.164.17.27:1112     202.164.17.27:4639     ESTABLISHED
  TCP    202.164.17.27:3389     124.178.39.95:2048     ESTABLISHED
  TCP    202.164.17.27:4613     202.164.17.27:1112     ESTABLISHED
  TCP    202.164.17.27:4637     202.164.17.27:1112     ESTABLISHED
  TCP    202.164.17.27:4639     202.164.17.27:1112     ESTABLISHED
  UDP    0.0.0.0:161            *:*
  UDP    0.0.0.0:445            *:*
  UDP    0.0.0.0:500            *:*
  UDP    0.0.0.0:1032           *:*
  UDP    0.0.0.0:1434           *:*
  UDP    0.0.0.0:4500           *:*
  UDP    127.0.0.1:123          *:*
  UDP    202.164.17.27:123      *:*
  UDP    202.164.17.27:137      *:*
  UDP    202.164.17.27:138      *:*
  UDP    202.164.17.28:123      *:*

Any idea?

That doesn't help.  Once again, if the port is blocked by telnet then a router or device is blocking traffic from the client to the SQL server.

By any chance did you enable 'Hide SQL Server' anywhere in the settings?  That will change the default listening port from 1433 to 2433.

Sorry tedbilly, I havent enabled/disable any settings. Is there any other test I can do to findout

One more thing does it make a difference if I am using SQL Server 2005 Express Edition instead of SQL server standard edition