Troubleshooting connecting to a remote server in SQL Server 2005
Area: SQL Server 2005
Connecting within your own domain to a SQL server machine in the SQL Server Management
Studio couldn’t be simpler; File / New / Database Engine Query, enter the
name of the SQL Server and your authentication information and you’re connected.
Registering this same server for regular use is also easy: View / Registered Servers
and select the Database Engine type. Right-click in the Registered Servers pane,
then choose New / Server Registration… and fill in the same details.
When a SQL Server is outside your domain though, matters can sometimes become more
complicated. Follow these steps if you’re having trouble connecting to or registering
a remote server using the SQL Server Management Studio:
- Is the Windows server visible? Try pinging it to make sure. Bring up a command prompt
and enter:
ping WINDOWSSERVERNAME
If you don’t get a response here, there’s no point in continuing within SQL. The
server is unreachable, so contact the relevant Windows admin. If you do get
a reply, make note of the IP address shown.
- Try entering the IP address instead of the server name in the Server Registration
dialog.
- If the remote server is a SQL Server 2005 server, make sure (if you can) that the
server is set up to accept remote connections. This feature is turned off by default
in SQL Server 2005 and can be re-enabled by a SQL admin using the
Surface Area Configuration utility.
Ensure also that it’s configured for the same network protocol you’re using.
- If you have access to the remote Windows server on which SQL is running, this is
also a good moment to verify that (a) the SQL service is running and (b) that you’re
trying to access the correct server name.
For a default SQL install the SQL instance has the same name as the Windows server,
but if
SQL instances
are in use then the SQL Server name that should be registered takes the form WindowsMachineName\SQLServerInstanceName.
A default SQL Server Express install will usually run as WindowsMachineName\SQLEXPRESS,
for instance (no pun intended).
- Still not co-operating? Start the SQL Server Configuration Manager...
...from the SQL Server 2005 programs group in your Start menu. Go to SQL Native Client
Configuration / Aliases and choose New Alias… from the Action
menu.
NOTE:: If you’re running The SQL Management Studio on Windows Vista with
the recommended settings (non-administrator account, UAC switched on) then you’ll
get a “WMI provider error: Access denied” once you try to save your new alias. The
way around this is to launch the SQL Server Configuration Manager as an administrator;
right-click on the icon and select “Run as administrator”, then you’ll be able to
create an alias without problems.
- Try registering or connecting to the server in the Management Studio using the Alias
name you’ve just created. This should now connect successfully.