18Apr “Access Denied” when connecting to MSDE remotely after installation
I had to install a product today that requires some sort of SQL Server installation. The server it’s on didn’t have the full SQL Server product installed (it’d be overkill) so MSDE 2000 was installed. First up, you need to set a strong password for ’sa’ account when installing MSDE. You do this by running the following :
where
Click here for more information on the parameters you can use when running MSDE setup.
So anyway, once you’ve done that you might find you can’t connect to your nice new shiny MSDE server over the network due to an error that looks like the following :
This is because by default there are no remote protocols enabled, i.e. you can’t connect remotely. To fix this you can either hack up the registry but we’re not going to do that here. Instead we’re going to use the SVRNETCN.EXE utility that comes with SQL Server. Unless you’ve installed MSDE as a named instance this executable is available in the following path :
Run this and you’ll get something that looks like the following.
For typical networks/installations using TCP/IP will be sufficient. If this is ok for you select TCP/IP from the list of ‘Disabled Protocols’ on the left side of the screen and click the ‘Enable >>’ button to enable the protocol. Click OK and accept the message that comes up saying you need to restart SQL Server before the change will take effect.
From Control Panel | Administrative Tools | Services you’ll then need to find the service for the instance of SQL that you’re working with (by default this is called “MSSQLSERVER”) and restart it … right-click the service name and select “Restart”. If SQLSERVERAGENT is running you’ll be asked if you want to restart this too - you’ll need to say Yes to continue.
After this you should be able to use Query Analyser or Enterprise Manager to administer your MSDE installation using the ’sa’ account (note that MSDE doesn’t come with these tools as standard though).
If you want to login to your MSDE server using Windows Authentication you have to enable that too … I’ll post about that shortly (there are registry changes involved if you don’t run setup with the appropriate switches at install time).