Articles & Documents

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:


    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... 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. The New Alias dialog 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.

Helaas...deze pagina en de artikelen hiernaast zijn niet beschikbaar in het Nederlands. Klik hier om naar de Nederlandstalig home pagina te gaan of lees verder in het Engels.