Managing Remote Servers - 02 Feb 2000

Several people have asked me how to use tools such as Visual InterDev, Access 2000, or Enterprise Manager to access SQL Server across the Internet. Let's look at these tools.

To use Visual InterDev or Access 2000, you start a new project. At the prompt for the SQL Server system name in the New Project dialog box, enter the name or address of the Web server. For example, when you create a new data connection in Visual InterDev, you can specify the name of the database server or the Data Source Name (DSN). I usually use a system DSN for this purpose.

To create a system DSN for a remote SQL Server, open the Control Panel, then the ODBC manager. Click the System tab and use the SQL Server DSN wizard to start creating a new DSN for SQL Server. At the Microsoft SQL Server Configuration page, click the Client Configuration button. On the Edit Network Library Configuration dialog box, click TCP/IP. Enter the name of the remote server you want to use in the Server Alias dialog box. Then enter the name for the server in the Computer Name dialog box. Set the Server Alias and Computer Name to the same name unless you have a compelling reason not to. The only time I don't use the same name for the project is when I'm creating a new copy of an existing site or database and I want an identifiable name. Click OK and finish the steps of the wizard. You can use this DSN with Access 2000 or Visual InterDev to connect to the remote database.

To use Enterprise Manager to connect to a remote database, you need a slightly different approach. In Enterprise Manager, open the Client Network Utility from the Program Files in the Microsoft SQL Server 7.0 folder. On the General tab, click the Add button. Click the TCP/IP radio button, enter the alias and computer name as you did for the DSN, and click OK. You'll see the new server in the list of Server Alias configurations. Click OK to close the SQL Server Client Network Utility. Now when you register the server with Enterprise Manager, the network library name will match the server name you enter and Enterprise Manager will find the remote server. You can use Enterprise Manager as usual, except you're now managing a remote server.

Also, if you have access to PC Anywhere, you can use it to run management tools remotely if you have the proper Windows NT and SQL Server security credentials.