• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1619
  • Last Modified:

System DSN using IP address

Hello EE
I am having trouble creating System DSN for my server which has a static IP address (eg. 203.11.2.1). I am runnign SQL Server 2005.
I am creating native Client
Name:mydb
Desc: My Database
Server:\\203.11.2.1\SQLEXPRESS (I think thats where the problem is)
When I do test the DB it gives me the follwoing error
==========================================================
Microsoft SQL Native Client Version 09.00.3042

Running connectivity tests...

Attempting connection
[Microsoft][SQL Native Client]SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
[Microsoft][SQL Native Client]Login timeout expired
[Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

TESTS FAILED!


Any idea whats causing this. Note my SQL Server and my web app and web services are running fine.
0
saleemz
Asked:
saleemz
  • 24
  • 20
  • 2
  • +1
1 Solution
 
Tommie Nathaniel Carter, Jr., MBACommented:
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
0
 
Ted BouskillSenior Software DeveloperCommented:
Also you do not need the leading backslashes.  203.11.2.1\SQLEXPRESS is a valid SQL name just like MYSERVER\SQLEXPRESS

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

0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
saleemzAuthor Commented:
is there any way I can post my screen captures here
0
 
Tommie Nathaniel Carter, Jr., MBACommented:
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.
0
 
saleemzAuthor Commented:
guys I have tried pretty much everything  but no luck
0
 
Ted BouskillSenior Software DeveloperCommented:
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.
0
 
saleemzAuthor Commented:
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"
0
 
Ted BouskillSenior Software DeveloperCommented:
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.

0
 
saleemzAuthor Commented:
Hi Tedbilly i have tried that didnt work
0
 
Ted BouskillSenior Software DeveloperCommented:
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>'
0
 
saleemzAuthor Commented:
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
0
 
saleemzAuthor Commented:
any ideas any one
0
 
Ted BouskillSenior Software DeveloperCommented:
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
0
 
saleemzAuthor Commented:
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>>>
0
 
Ted BouskillSenior Software DeveloperCommented:
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.
0
 
saleemzAuthor Commented:
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
0
 
saleemzAuthor Commented:
any idea ted
0
 
Ted BouskillSenior Software DeveloperCommented:
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.

0
 
saleemzAuthor Commented:
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?
0
 
Ted BouskillSenior Software DeveloperCommented:
Yes, the port is blocked.  If it was successfully you would have seen a blinking cursor sitting there waiting for a request.
0
 
saleemzAuthor Commented:
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
0
 
saleemzAuthor Commented:
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:\>
0
 
Ted BouskillSenior Software DeveloperCommented:
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
0
 
saleemzAuthor Commented:
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     *:*

0
 
saleemzAuthor Commented:
anything I can do to give you more info ted
0
 
Ted BouskillSenior Software DeveloperCommented:
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'?
0
 
saleemzAuthor Commented:
yes it has the surface are config tool
I have pretty much enabled everything.
Is there anything I can do it there.
0
 
Ted BouskillSenior Software DeveloperCommented:
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'
0
 
saleemzAuthor Commented:
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.
0
 
Ted BouskillSenior Software DeveloperCommented:
Sure, send it to tedbilly AT hotmail dot com
0
 
Ted BouskillSenior Software DeveloperCommented:
Also, ZIP it with a password and post the password here if you want to make sure the information is protected.
0
 
saleemzAuthor Commented:
Hello Any EE who can help me with this I still cant connect to the database using System DSN
0
 
Ted BouskillSenior Software DeveloperCommented:
Can you add the text for the System DSN as an attachment?
0
 
saleemzAuthor Commented:
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
0
 
Ted BouskillSenior Software DeveloperCommented:
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.
0
 
saleemzAuthor Commented:
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
0
 
Ted BouskillSenior Software DeveloperCommented:
Yes I'd like to see screen shots of the configuration.
0
 
saleemzAuthor Commented:
see attached is there anyway I can remove attachments later
Microsoft-Word---sql-server-conf.pdf
0
 
Ted BouskillSenior Software DeveloperCommented:
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?
0
 
saleemzAuthor Commented:
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
0
 
Ted BouskillSenior Software DeveloperCommented:
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.
0
 
saleemzAuthor Commented:
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?
0
 
Ted BouskillSenior Software DeveloperCommented:
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.
0
 
saleemzAuthor Commented:
Sorry tedbilly, I havent enabled/disable any settings. Is there any other test I can do to findout
0
 
saleemzAuthor Commented:
One more thing does it make a difference if I am using SQL Server 2005 Express Edition instead of SQL server standard edition
0
 
Ted BouskillSenior Software DeveloperCommented:
No it won't matter.  Please stop using the 'SQL Native Client' and use the 'SQL Server' instead.  I have a feeling that will work.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 24
  • 20
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now