Connection to a Microsoft SQL Server Database System

After installing Microsoft SQL Server, to use it, you must first open it. Before performing any database operation, you must first connect to the database server. If you are planning to work on the server, on the taskbar, you can click Start -> (All) Programs, and position the mouse on Microsoft SQL Server. You can then click either Query Analyzer or Enterprise Manager:

If you had clicked Enterprise Manager, it would open the SQL Server Enterprise Manager:

You can also establish the connection through the SQL Query Analyzer. To do this, from the task bar, you can click Start -> (All) Programs -> Microsoft SQL Server -> Query Analyzer. This action would open the Connect to SQL Server dialog box:

If the Enterprise Manager was already opened but the server or none of its nodes is selected, on the toolbar of the MMC, you can click Tools -> SQL Query Manager. This also would display the Connect to SQL Server dialog box.

The Source of Data

To establish a connection, you must specify the computer you are connecting to, that has Microsoft SQL Server installed. If you are working from the SQL Server Enterprise Manager, first expand the Microsoft SQL Servers node, followed by the SQL Server Group. If you don't see any name of a server, you may not have registered it (this is the case with some installations, probably on Microsoft Windows XP Home Edition):

1. The following steps are used only if you need to register the new server.
   To proceed, you can right-click the SQL Server Group node and click New SQL Server Registration...

   

2. In the first page of the wizard, you can click Next:

   

3. In the Register SQL Server Wizard and in the Available Servers combo box, you can select the desired server (if would be selected already) or click (local), then click Add:

   

4. After selecting the server, you can click Next. In the third page of the wizard, you would be asked to specify how security for the connection would be handled. If you are planning to work in a non-production environment where you would not be concerned with security, the first radio button would be fine. In most other cases, you should select the second radio button as it allows you to eventually perform some security tests during your development. This second radio button is associated with an account created automatically during installation. This account is called sa

   

5. After making the selection, you can click Next. If you had clicked the second radio button in the third page, one option would ask you to provide the user name and the password for your account. You can then type either sa or Administrator (or the account you would be using) in the Login Name text box and the corresponding password. The second option would ask you to let the computer prompt you for a username and a password. For our exercise, you should accept the first radio button, then type a username and a password:

   

6. The next (before last) page would ask you to add the new server to the existing SQL Server Group. If you prefer to add the server to another group, you would click the second radio button, type the desired name in the Group Name text box, and click Next:

   

7. Once all the necessary information has been specified, you can click Finish:

   

8. When the registration of the server is over, if everything is fine, you would be presented with a dialog box accordingly:

   

9. You can then click Close

Therefore, to specify the computer you want connecting to, if you are working from the SQL Server Enterprise Manager, you can click either (local) or the name of the server you want to connect to. Here is an example:

If you are connecting to the server using the SQL Query Analyzer, we saw that you would be presented with the Connect to SQL Server dialog box. Normally, the name of the computer would selected already. If not, you can select either (local) or the name of the computer in the SQL Server combo box:

If the SQL Server Enterprise Manager is already opened and you want to open SQL Query Analyzer, in the left frame, you can click the server or any node under the server to select it. Then, on the toolbar of the MMC, click Tools -> SQL Query Analyzer. In this case, the Query Analyzer would open directly.

Security

An important aspect of establishing a connection to a computer is security. Even if you are developing an application that would be used on a standalone computer, you must take care of this issue. The security referred to in this attribute has to do with the connection, not how to protect your database.

If you are using SQL Server Enterprise Manager, you can simply connect to the computer using the steps we have reviewed so far.

If you are accessing SQL Query Analyzer from the taskbar where you had clicked Start -> (All) Programs -> Microsoft SQL Server -> Query Analyzer, after selecting the computer in the SQL Server combo box, you can specify the type of authentication you want. If security is not an issue in this instance, you can click the Windows Authentication radio button:

If you want security to apply and if you are connecting to SQL Query Analyzer using the Connect To SQL Server dialog box, you must click the SQL Server Authentication radio button:

If you are connecting to SQL Query Analyzer using the Connect To SQL Server dialog box and you want to apply authentication, after selecting the second radio button, this would prompt you for a username.

If you are "physically" connecting to the server through SQL Query Analyzer, besides the username, you can (must) also provide a password to complete the authentication:

After providing the necessary credentials and once you click OK, the SQL Query Analyzer would display:

Microsoft SQL Server (including MSDE) ships with various ready-made databases you can work with. In SQL Server Enterprise Manager, the available databases and those you will create are listed in a node called Databases. To display the list of databases, you can click the Databases node:

If you are not trying to connect to one particular database, you don't need to locate and click any. If you are attempting to connect to a specific database, in SQL Server Enterprise Manager, you can simply click the desired database. Here is an example:

If you are working in SQL Query Analyzer but you are not trying to connect to a specific database, you can accept the default master selected in the combo box of the toolbar. If you are trying to work on a specific database, to select it, on the toolbar, you can click the arrow of the combo box and select a database from the list:

Closing a Connection

After using a connection and getting the necessary information from it, you should terminate it. If you are working in SQL Server Enterprise Manager or the SQL Query Analyzer, to close the connection, you can simply close the window as an application.