Solved

MSDE cannot connect via telnet 1433 - query analyser works fine

Posted on 2004-09-09
9
1,307 Views
Last Modified: 2008-01-09
Hi

MSDE installation with Entprise Manager and Query analyser from Eval copy.

DBs can be seen and edited on localhost.   Default port 1433 being used.  Query analyser works fine.

However asp application on host needs DSN-less access to SQL server via port.

I cannot even telnet into 1433 and see a response.

Yet there is no firewall and I know service is running?

Do you need to enable it somehow?  Maybe something odd with MSDE.

Any ideas?

Bendecko
0
Comment
Question by:bendecko
  • 2
  • 2
  • 2
  • +2
9 Comments
 
LVL 34

Assisted Solution

by:arbert
arbert earned 31 total points
ID: 12022740
What do you get when you telnet--you don't normally get a response--you can type a few keystrokes and then you're taken back to a dos prompt....

Is the host on the same network--is it the host that you tried query analyzer from?  Is the asp application connecting using the same user/password you connected with from Query Analyzer?

0
 
LVL 1

Accepted Solution

by:
davee_jay earned 32 total points
ID: 12024487
I imagine that due to the Dynamic Port allocation (a new 'feature' in SQL 2K) MSDE is not bound to the 1433 port.

You can check the version of the client libraries on the webserver as follows (W2K Prof - Your O/S may differ slightly):

Start -> Settings -> Control Panel -> Administrative Tools -> Data Sources (ODBC) -> Add

- Select the SQL Server Driver (Can you post the complete version number for our reference as well pls.) - Click Next

- Enter name, desc and Server - Click Next

- Client Configuration -> TCP/IP
  - Server name should be the name you entered
  - There should be a checkbox - Dynamically determine port - this should be checked - Click OK

- Click Next and Next again Then Finish

- Test Datasource

N.B. If there is no checkbox as noted above then install the SQL 2K client connectivity libraries on the webserver (not EM or QA though!)

Let me know how you get on.

DJ
0
 
LVL 51

Assisted Solution

by:Julian Hansen
Julian Hansen earned 31 total points
ID: 12024921
Telnet won't return anything.

When you say Query Analyser works fine do you mean it works fine when you type in an IP address as opposed to the server name.

Does the script work when you use a server name instead of an IP?

When you use Query analyser - is it from the same machine as the asp script is running on?
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 31 total points
ID: 12025002
>>I cannot even telnet into 1433 and see a response.
if you run telnet IP_ADDRESS:1433 and do not get a response AND NO ERROR, this is fine, because this means that the port is open.
if you get an error, then there is a firewall or the line is not available.
to explain: MSDE SQL Service actually get's the telnet connection, but doesn't answer to it. Thus TELNET client get's no response, but no error neither, as it actually waits from the TELNET server (which MSDE is not) to return/request some data. That's the way async communication works...

As described above:
* Check if the MSDE indeed uses port 1433
* Check the client configuration of the ASP host is configured correctly
* check also that the ASP has the permissions on the SQL Server to connect!!!

CHeers
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Author Comment

by:bendecko
ID: 12026010
Thanks for all these great responses.

All the action is on one machine.  Website, MSDE, Query analyser all.....  No network or firewall or anything to worry about.

I know for a fact that if i telnet into my other machine (where all is working) I don't get refused entry on 1433 - this tells me that SQL is running fine.    I appreciate I get no response c.f. a mailserver response.

How can I determine which port SQL is running on if its dyanmic? Can I bind it manually.  What about a commandline command to see the binding services and ports?

I must have the connectivity libraries installed as EM and QA work - on the *same* machine.   No?

This is the ASP code that connects to the DB:

conn.Open "DRIVER=SQL Server;SERVER="&sysServer&";CATALOG="&sysCatalog&";UID="&sysUsername&";PWD="&sysPassword

No port is specficed in this string.  Does it need one?

Its worked hundreds of time on SQL servers running the full version of SQL Server 2000.  This is the first time I'm playing with MSDE and it doesn't appear to be playing the game the same way.

Thank you for your continued assistance.

Bendecko
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 12026130
Goto your registry

check the following key

HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\Client\ConnectTo

Under this Key either change the DSQUERY key to DBMSSOCN

Or create a server entry for your server

Create a new REG_SZ value with your server name as the value
Enter the following data for this value

DBMSSOCN,YOUR_SERVER_NAME

Try again

If this fails then try the following

Create a new ODBC connection to your database. On the second screen click the client confiruation button and change the connection type to TCP/IP. Proceed through the setup and at the end test the conection by pressing the test connection button - tell us what happens.
0
 
LVL 1

Expert Comment

by:davee_jay
ID: 12026479
Ah, okay the issue is clearer now.  You are right you must have the client libraries installed.

Please take a look at the SQL Server Network Utility and look at the enabled protocols.  I imagine TCP/IP is not in the list.  If this is the case, then it needs to be.  You can specify the default port here.

Let me know how you get on.

Also, can you post the driver version information (can be found as per my instructions above)

DJ
0
 
LVL 34

Expert Comment

by:arbert
ID: 12027773
Agree--since QA is on the same machine you can probably connect fine.  Like davee_jay said above, did you enable protocols when you installed MSDE?
0
 
LVL 1

Author Comment

by:bendecko
ID: 12028009
Yeah good work.

The protocol were enabled, yes.     Security all etc, all OK.

I read on another board to set the port to 0!  This turned out to be a disaster cos EM couldn't then see the DB at all.  This was supposed to let the server choose its own port.  Which, thinking about it, it did because...

I rebooted and it fixed itself and ent manager was able to connect.  I checked the port then in Client utility and it was 3144.   I changed this to 1433 and restarted the services and now we all cool.   Telnet in and ASP can access the DB.

I suspect that an additional reboot ealier would have actually had the same effect.

However EXCELLENT help and very quick responses.  Thank you.  I can now get back to serious business of coding the thing.

Thanks

Bendecko

0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Introduction: Often, when running a query with joins, the results show up "duplicates", and often, those duplicates can be "eliminated" in the results using DISTINCT, for example. Using DISTINCT is simple: just add it after the SELECT keyword, an…
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now