DIAL-UP
HIGHSPEED
ISDN
HOSTING
UNIX
Windows
Lotus
Dedicated
Personal Web Space
WEB AUTHORING
SOFTWARE
SPAM
EXTERNAL LINKS
Magma.ca
Webmail
myaccount.magma.ca


Connecting to Microsoft SQL Server - Best Practices

This document is for Magma clients who need to connect to one of Magma's shared SQL database servers with Microsoft's "Enterprise Manager" client. The purpose of this document is to outline how best to configure the Enterprise Manager tool for remote connectivity to the Magma shared SQL environment. Please note that this document will outline the necessary steps for connectivity with both Microsoft Enterprise Manager v.7 and v.2000. Although the steps are very similar for the two products there may be some difference(s). Please confirm the version of the software you are running prior to following these guidelines.

Clients use Enterprise Manager to connect to, administer and manage their database hosted on Magma's SQL server. Enterprise Manager is an application provided by Microsoft and bundled with the SQL Server product. Magma does not provide clients with the Enterprise Manager software. However, Magma will support and assist customers with connectivity and management of their database using this product.

Magma's security infrastructure impacts the use of Enterprise Manager. The default settings in Enterprise Manager assume a LAN environment and will provide sub-optimal performance when connecting to Magma's firewalled SQL servers.

You should apply the configuration changes suggested in the following steps to ensure optimal performance when using Enterprise Manager to connect to Magma's SQL servers.






Step by Step - Microsoft SQL v.7

1. Magma highly recommends that clients install the latest applicable Microsoft SQL v.7 Service Pack as this addresses many issues discovered since the initial release of the product. These service packs are important installations not only for production servers, but also for systems running the client connectivity and management software. The most recent service pack for this product can be found at the following link to the Microsoft web site.

http://www.microsoft.com/sql/

2. The first step in configuring a connection to the Magma hosted SQL environment is to specify the client connectivity library and configure an alias. This is not done in the Enterprise Manager but rather through the client configuration tool.

2.1 From the Start menu, select Run and type "cliconfg". This will initiate a program window with three tabs. The first tab, which is displayed at startup, is the general tab showing the configured server aliases, their respective network libraries, and the connection parameters for each. The following image displays the window after it has been configured. Prior to following the steps in this document your display may be blank, or may display a previously configured entry.


Figure1: General tab of the client configuration utility

2.2 If you do not have a previously configured connection to the Magma SQL servers you will now need to click on the "Add" button. If you do have a previously configured connection you will need to select that connection and click on the "Edit" button. This will bring up the "Edit Network Library Configuration" window depicted in the following image:

Figure2: Edit Network Library Configuration window for entering SQL connectivity information

2.3 Please ensure the settings for your connection are configured with the following settings:

Selection Value
Server alias This value may be anything you wish as it is used only for identification. It is recommended to use an alias easily identified as the server you are connecting to.
Network libraries Only TCP/IP should be selected
Server name The IP address of the shared server you are connecting to.
SQL - 209.217.80.169
or
SQL2 - 209.217.80.189

2.4 The client configuration modifications are now complete. Please click the OK button to exit the "Edit Network Library Configuration" window. Confirm your entry looks like one of the ones listed in Figure1 shown previously in this document. Click the "OK" button to exit the Client Configuration Network Utility.

3. You may now open the Enterprise Manager application and begin the Register SQL Server Wizard to configure the management connection to your database server. The first window explains the steps you will be taking. After clicking "Next" you are presented with a list of configured Alias' on your server from which you must select the one you wish to register. Choose the alias name you have just created in the client configuration utility, click the Add button and click the Next button.

3.1 The next step of the wizard will present you with a choice for the authentication mode you wish to use. Magma makes use of SQL based authentication. As such you should now select the option as in the following image:

3.2 After clicking the "Next" button you will be prompted with the choice to enter your login information, or you can choose to enter the login information every time you connect. Most users find it more convenient to configure this information at this stage rather than being prompted at connection, however if you share your workstation with someone else and wish to keep the SQL connection private you may not wish to do so. To configure automatic authentication enter the userid and password supplied to you by Magma at the time your database was created.

3.3 The final step is to choose where in the enterprise manager application the new SQL registration entry should exist. This is up to the discretion of the user as it is strictly organizational on your part.

4. Congratulations! Now that you have configured your connection parameters you will be able to connect to and manage your database on the Magma shared environment. You may proceed by opening the Enterprise Manager, expanding the left pane to reveal the Group under which you configured your registration, and click on the server alias name representing the Magma server you are connecting to. The last thing is to expand the "Databases" folder. At this point you will notice you are able to see all of the databases being hosted on the server (this is normal). The security configuration on the server will only allow you to open and manage your own database. You may scroll through the list of available database names (alphabetically organized) and click on your database name to begin.



Step by Step - Microsoft SQL v.2000

1. Magma highly recommends that clients install the latest applicable Microsoft SQL 2000 Service Pack as this addresses many issues discovered since the initial release of the product. These service packs are important installations not only for production servers, but also for systems running the client connectivity and management software. The most recent service pack for this product can be found at the following link to the Microsoft web site.

http://www.microsoft.com/sql

2. The first step in configuring a connection to the Magma hosted SQL environment is to specify the client connectivity library and configure an alias. This is not done in the Enterprise Manager but rather through the client configuration tool.

2.1 From the Start menu, select Run and type "cliconfg". This will initiate a program window with four tabs. The first tab, which is displayed at startup, is the general tab specifying which protocols have been enabled/disabled. The only enabled protocol should be TCP/IP. If any other protocols are in the right hand window (Enabled) they can be selected by left clicking on the name of the protocol and selecting the disable button in the middle of the window. The end result should look like the following:

2.2 Next, click on the alias tab. If you do not have a previously configured connection to the Magma SQL servers you will now need to click on the "Add" button. If you do have a previously configured connection you will need to select that connection and click on the "Edit" button. This will bring up the "Edit Network Library Configuration" window depicted in the following image:

2.3 Please ensure the settings for your connection are configured with the following settings:

Selection Value
Server alias This value may be anything you wish as it is used only for identification. It is recommended to use an alias which easily identifies the server you are connecting to.
Network libraries Only TCP/IP should be selected
Server name The IP address of the shared server you are connecting to.
SQL - 209.217.80.169
or
SQL2 - 209.217.80.189
Dynamically determine port UNSELECTED

2.4 You will notice the default setting for "Dynamically determine port" is to be selected. This is the biggest concern with optimizing your connection as we have configured our secure environment to be very selective in the traffic allowed to our shared environment. After deselecting this option the text box will become available for additions. Please enter the TCP port used to connect to the server(s), which is port 1433. When you are complete, the Edit Network Library Configuration window will be displaying the following:

2.5 The client configuration modifications are now complete. Please click the Apply and OK buttons to exit the utility. The Alias tab of the Client Configuration Utility Window will display the following results, although you will probably only have one or the other configured alias:

3. You may now open the Enterprise Manager application and begin the Register SQL Server Wizard to configure the management connection to your database server. The first window explains the steps you will be taking. After clicking Next you are presented with a list of configured Alias' on your server from which you must select the one you wish to register. Choose the alias name you have just created in the client configuration utility, click the Add button and click the Next button.

3.1 The next step of the wizard will present you with a choice for the authentication mode you wish to use. Magma makes use of SQL based authentication. As such you should now select the option as in the following image:

3.2 After clicking the "Next" button you will be prompted with the choice to enter your login information, or you can choose to enter the login information every time you connect. Most users find it more convenient to configure this information at this stage rather than being prompted at connection, however if you share your workstation with someone else and wish to keep the SQL connection private you may not wish to do so. To configure automatic authentication enter the userid and password supplied to you by Magma at the time your database was created.

3.3 The window you are now presented with allows you to select where in the Microsoft Management Console you would like to place the newly registered server. This is left to the clients' discrimination as it is strictly organizational and only affects the display presented upon the initialization of the Enterprise Manager application.

3.4 After clicking the Next button you are presented with a synopsis of what was accomplished with the wizard including a display of the alias for which the registration has been done. By clicking on the Finish button you will initiate a "test" where the Enterprise Manager will attempt to connect to the database server using the information you have just entered to create the registration.

4. Congratulations! Now that you have configured your connection parameters you will be able to connect to and manage your database on the Magma shared environment. You may proceed by opening the Enterprise Manager, expanding the left pane to reveal the Group under which you configured your registration, and click on the server alias name representing the Magma server you are connecting to. The last thing is to expand the "Databases" folder. At this point you will notice you are able to see all of the databases being hosted on the server (this is normal). The security configuration on the server will only allow you to open and manage your own database. You may scroll through the list of available database names (alphabetically organized) and click on your database name to begin.