MSDE on Windows 2003 Server

[da644]

Hi Everyone.

I have installed the latest version of MSDE on our new Windows 2003 Server (Standard Edition) and it all appears to have installed ok and the service is running ok, but I can't connect to it using Enterprise Manager or the Web Data Administrator tool. I set the SA password on via the command line at install:

setup SAPWD="password"

and I'm trying to connect using SA and the password but I just keep getting the following error message:

"Invalid username and/or password, or server does not exist.
Also, please ensure that SQL Server Authentication is enabled on the server."

Anyone have any idea what the problem is?

Thanks

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.