| Author |
Topic |
|
benricho
Yak Posting Veteran
84 Posts |
 Posted - 2003-11-28 : 00:13:14
|
I have installed MSDE on a server and am trying to connect to it remotely but haven't been able. After looking through newsgroups etc I found to check the error.log file to see what port the database is listening on, but it looks like it isn't at all:2003-11-17 18:12:44.57 server Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Desktop Engine on Windows NT 5.2 (Build 3790: )2003-11-17 18:12:44.57 server Copyright (C) 1988-2002 Microsoft Corporation.2003-11-17 18:12:44.57 server All rights reserved.2003-11-17 18:12:44.57 server Server Process ID is 1412.2003-11-17 18:12:44.57 server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL$MSDE2K\LOG\ERRORLOG'.2003-11-17 18:12:44.64 server SQL Server is starting at priority class 'normal'(2 CPUs detected).2003-11-17 18:12:44.90 server SQL Server configured for thread mode processing.2003-11-17 18:12:44.90 server Using dynamic lock allocation. [500] Lock Blocks, [1000] Lock Owner Blocks.2003-11-17 18:12:45.00 spid2 Starting up database 'master'.2003-11-17 18:12:45.43 server Using 'SSNETLIB.DLL' version '8.0.766'.2003-11-17 18:12:45.43 spid5 Starting up database 'model'.2003-11-17 18:12:45.46 spid2 Server name is 'MMSIT01\MSDE2K'.2003-11-17 18:12:45.46 spid2 Skipping startup of clean database id 42003-11-17 18:12:45.59 spid5 Clearing tempdb database.2003-11-17 18:12:45.95 spid5 Starting up database 'tempdb'.2003-11-17 18:12:46.04 spid2 Recovery complete.2003-11-17 18:12:46.04 spid2 SQL global counter collection task is created.2003-11-17 18:12:48.42 server SQL server listening on Shared Memory.2003-11-17 18:12:48.42 server SQL Server is ready for client connections2003-11-18 17:01:05.87 spid51 Starting up database 'msdb'.2003-11-18 17:01:07.25 spid51 Using 'xpsqlbot.dll' version '2000.80.760' to execute extended stored procedure 'xp_qv'. Now I think I would be able to fix this Enterprise Mananger by going to the properties of the database --> General tab --> Network Properties and then add TCP/IP to the enabled box. But the problem is that I can't get Enterprise Manager on the server, I don't have physical access to the box.Can this be done through another method apart from loading Enterprise Manager on the machine? Or could there be another reason why SQL Server isn't listening? |
|
|
saglamtimur
Yak Posting Veteran
91 Posts |
Posted - 2003-11-28 : 04:44:46
|
| You can configure MSDE to listen TCP port 1433 and/or namedpipes through svrnetcn.exe which is under x:\Program Files\Microsoft SQL Server\80\Tools\Binn folder where MSDE installed. You will see TCP/IP and namedpipes in Disabled Protocols. You should first enable them and then configure by clicking properties button. |
 |
|
|
benricho
Yak Posting Veteran
84 Posts |
Posted - 2003-11-30 : 19:38:24
|
| Thanks, I used that tool and added TCP/IP and named pipes. I also checked that the TCP/IP port was 1433.But I still can't connect remotely, I get an "SQL Server does not exist or access denied" error message. I have the right access details because I can login locally through osql using them.Any other suggestions on how I could get this working? |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-11-30 : 19:46:39
|
| OK. Don't hate me for this question.... but you would be amazed how many times people come back and say "ooops, you are right".Are you sure there is no firewalls in between you and the box that may be blocking you ? After the slammer worm (etc) network admins got a LOT more vigilant in closing off ports like 1433.Damian |
|
|
|
benricho
Yak Posting Veteran
84 Posts |
Posted - 2003-11-30 : 19:54:23
|
| Thats a good question but yes, I contacted the host and made sure the firewall was opened on port 1433 (it was blocked by default). They have told me that they openned the port but maybe it's worth checking again? Is there an easy way I can check this? |
|
|
|
benricho
Yak Posting Veteran
84 Posts |
Posted - 2003-11-30 : 20:28:33
|
| I tried a few other things, I run netstat -a from the command line to see the open ports and I don't get port 1433, but I do get ms-sql-s as a port, could this be it? I also tried using telnet, haven't used it before and I wasn't quite sure if it was working but I could get a connection on port 80 but not 1433.By the way when i look at the error.log file it says it listening on port 1433. |
|
|
|
saglamtimur
Yak Posting Veteran
91 Posts |
Posted - 2003-11-30 : 21:42:46
|
| At this point it looks firewall issue as Merkin said.As I know SQL server listens UDP 1434, open UDP port 1434 on firewall.Instead of netstat -a, use netstat -an to see the port numbers.For example I first run netstat -a and saw two "ms-sql-s". One is listening TCP and other one is listening UDP. Then I run netstat -an and check line numbers by netstat -a results to find the ports numbers. TCP servername:ms-sql-s = 1433, and UDP servername:ms-sql-s = 1434Hope this helps. If not, another thing to do is disabling -if possible- firewall to see if this is really a firewall issue. |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-11-30 : 21:57:02
|
| Telnet was the next thing I would have suggested. It does look like your host hasn't configured the firewall properly.Also, I *think* (and I may be wrong here so anyone feel free to correct me), SQL Server uses TCP for actual communication and only UDP for discovery on a network.Perhaps UDP got unblocked and not TCP ?Damian |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2003-12-01 : 00:53:49
|
| OK, MSDE installed from SP3 is different in many ways. The two that are biting you are the following:1. By default all protocols are disabled (as you found out).2. SQL server authentication is disabled (no sa login from remote systems)You fixed problem number one, now you need to enable mixed mode authentication. Or you can just use windows authentication for your application (microsoft strongly recommends this btw). If you have a firewall between you and your database the windows authentication will be more trouble than it is worth.Anyway, please read the install readme that is included in SP3a for MSDE, and do some technet searches for mixed mode authentication and MSDE to see how to re-enable. It is only a small change to a registry entry, but I cannot remember it off the top of my head. btw, I believe the pertinent section is 3.5.7 in the readme document, but that is from memory only. Also, these defaults can be altered at install time with a couple of setup arguments.-ec |
|
|
|
benricho
Yak Posting Veteran
84 Posts |
Posted - 2003-12-01 : 01:10:39
|
| Thanks eyechart, but when I installed MSDE I added the command option SECURITYMODE=sql and specified a sa password when installing so that I would have SQL server authentication. I think it looks like it might be a firewall issue. I can telnet to the server locally, the support staff tell me they can telnet to the server from their connection but I can't from here. The host suggested there might be a firewall at this end blocking me, but our network administrator assures me this isn't the case.I am going to try telneting from home and see how I go. Hopefully I will be able to resolve this tomorrow, thanks for your help so far everyone. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2003-12-01 : 01:50:57
|
| ok, you may want to reference note 319930 to validate that mixed mode is active though.It is odd that your named instance is running on port 1433. are there any other instances running on that server? A default instance maybe? You may want to change the port you are using, but make sure that you are using MDAC 2.6 or newer on the clients that try and connect to it.-ec |
|
|
|
benricho
Yak Posting Veteran
84 Posts |
Posted - 2003-12-01 : 02:16:07
|
| I had a look at the registry to see what mode my MSDE was running in, and after looking at Knowledge base article 322336 (http://support.microsoft.com/?kbid=322336) I found that the LoginMode should be 1 (for Windows Authentication) or 2 (for mixed mode). When I checked mine, the value was 0?? I stopped MSDE and changed it to 2.Still can't connect remotely, but sounds like a firewall issue at this stage, still can't even telnet to it. By the way eyechart, I thought 1433 was the default port for SQL Server? |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2003-12-01 : 02:32:08
|
quote:
Originally posted by benricho
I had a look at the registry to see what mode my MSDE was running in, and after looking at Knowledge base article 322336 (http://support.microsoft.com/?kbid=322336) I found that the LoginMode should be 1 (for Windows Authentication) or 2 (for mixed mode). When I checked mine, the value was 0?? I stopped MSDE and changed it to 2.Still can't connect remotely, but sounds like a firewall issue at this stage, still can't even telnet to it. By the way eyechart, I thought 1433 was the default port for SQL Server?
Well, typically the default instance listens on port 1433. A named instance can listen on 1433, but usually doesn't so as not to step on the default instance.If this system doesn't have another instance of SQL Server running, then the MSDE instance you installed is perfectly fine listening on 1433. However, if there is another separate instance on this machine listening on the same port, then you will experience a problem similar to what you have described. This is a reach though. Have you validated the version of MDAC you are using to connect to the database? This needs to be 2.6 or greater to connect to a named instance. 2.5 and older can actually be used, but you need to create an alias in the cliconfg utility for it to work. If all that doesn't work, I guess we are back to looking at the firewall..-ec |
|
|
|
saglamtimur
Yak Posting Veteran
91 Posts |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2003-12-03 : 01:04:22
|
| benricho,Any update on this? |
|
|
|
benricho
Yak Posting Veteran
84 Posts |
Posted - 2003-12-03 : 01:13:22
|
| Yeah, just got it working now!! It was the host, they didn't have the firewall setup correctly! But I did learn a few things from this experience, like how to add TCP/IP and names pipes if they aren't added during the setup, and what flags to add if you want them to be installed by default when you run setup. The little trick to check the error.log file to see if the server is listening and if so on what port was also very helpful.Thank you very much for your help everyone. |
|
|
|
|
SQL server not listening on TCP/IP