MSDE on Windows 2003 Server 1

[da644]

Hi Everyone.

I have installed MSDE 2000 SP3 on Windows 2003 Standard Edition and it all appears to be running ok (the MSSQL Server icon is in the system tray) but I can't connect.

I have checked the port it is running on in the registry and it is set to default (1433), however I can't connect to 1433 using telnet (telnet [ip] 1433) remotely or on the localhost (telnet localhost 1433), the connection is refused. I have read somewhere else it could be to do with Windows 2003 'Packet Filtering Policies' but I have checked what I think they are (admin tools > local security policy > ip security policies on local computer) but all the policies are set to policy assign = no, which I assume means they are not doing anything. The configuration of the machine is complete the same as when installed, with no firewall or any other configuration changes.

Anyone have any ideas why?

Best Regards

Andrew.

 

[da644]

Ok everyone, with a lot of digging around I have found the answer to this. It would appear the by default the latest version of MSDE (2000 SP3) by default disables TCP/IP connections and doesn't work in mixed mode. To over comes these problems you have to do two things. Firstly, when you install MSDE you must use a command line like:

setup SAPWD="password" SECURITYMODE=SQL

The SAPWD= bit sets the SA password and the SECURITYMODE=SQL means that it will use mixed mode security allowing you to log in using SQL username and password instead of Windows, this helps if it is a remote web server like our situation.

Once installed you need to enable TCP/IP to allow you to connect. To this you need to run a small utility that comes with MSDE but is not added to the programs menu or anywhere comes to that. To run the utility go to:

C:\Program Files\Microsoft SQL Server\80\Tools\Binn
here you will find a program called SVRNETCN.exe, run it.

In the dialog that opens select TCP/IP from the list of the left and click the 'Enable >>' button. Click ok and ok to the message about it not working until you restart the service. Now, either reboot the machine (recommended) or got the Services tool and start or restart the MSSQLSERVER service. You should now be able to connect remotely using the SA and password you set at installation.

Not the easiest thing in the world to do and not easy to find out how to it, but I got there in the end. Hope this helps other.

Best Regards

Andrew.