Connecting to the Database Engine
SQL Server 2008 R2
Other Versions
When you install the SQL Server Database Engine, the tools
that are installed depend upon the edition and your setup choices. This lesson
reviews the principal tools, and shows you how to connect and perform a basic
function (authorizing more users).
This lesson contains the following tasks:
Tools For Getting Started
Connecting with Management Studio
Authorizing Additional Connections
Tools For Getting Started
The SQL Server Database Engine ships with a variety of
tools. This topic describes the first tools you will need, and helps you select
the right tool for the job. All tools can be accessed from the Start menu. Some
tools, such as SQL Server Management Studio, are not installed by default. You
must select the tools as part of the client components during setup. For a
complete description of the tools described below, search for them in SQL
Server Books Online. SQL Server Express contains only a subset of the tools.
Basic Tools
SQL Server Management Studio is the principal tool for
administering the Database Engine and writing Transact-SQL code. It is hosted
in the Visual Studio shell. It is not included in SQL Server Express.
SQL Server Management Studio Express is a free, limited
version of SQL Server Management Studio, intended for use with SQL Server
Express and for users who want the basic features of Management Studio in a
lightweight tool. Some downloads of SQL Server Express include Management
Studio Express and some do not. Management Studio Express can be installed from
Microsoft Download Center.
tool configures the more advanced connectivity elements but
does not enable features.
Sample Database
The sample databases and samples are not included with SQL
Server. Most of the examples that are described in SQL Server Books Online use
the AdventureWorks2008R2 sample database. For more information, see
Considerations for Installing SQL Server Samples and Sample Databases.
To start SQL Server Management Studio
On the Start menu, point to All Programs, point to Microsoft
SQL Server 2008 R2, and then click SQL Server Management Studio.
To start SQL Server Configuration Manager
On the Start menu, point to All Programs, point to Microsoft
SQL Server 2008 R2, point to Configuration Tools, and then click SQL Server
Configuration Manager.
Connecting with Management Studio
It is easy to connect to the Database Engine from tools that
are running on the same computer if you know the name of the instance, and if
you are connecting as a member of the Administrators group on the computer. The
following procedures must be performed on the same computer that hosts SQL
Server.
To determine the name of the instance of the Database Engine
Log into Windows as a member of the Administrators group,
and open either Management Studio or Management Studio Express.Security Note
If you are connecting to SQL Server 2000 or SQL Server 2005
on Windows Vista or Windows Server 2008 (or more recent), you may need to
right-click Management Studio and then click Run as Administrator in order to
connect using your Administrator credentials. Starting in SQL Server 2008,
setup adds selected logins to SQL Server, so your Administrator credentials are
not necessary. For more information, see How to: Connect to SQL Server from
Windows Vista.
In the Connect to Server dialog box, click Cancel.
If Registered Servers is not displayed, on the View menu,
click Registered Servers.
With Database Engine selected on the Registered Servers
toolbar, expand Database Engine, right-click Local Server Groups, point to
Tasks, and then click Register Local Servers. All instances of the Database
Engine installed on the computer are displayed. This includes instances of SQL
Server 2000 and SQL Server 2005. The default instance is unnamed and is shown
as the computer name. A named instance displays as the computer name followed
by a backward slash (\) and then the name of the instance. For SQL Server
Express, the instance is named <computer_name>\sqlexpress unless the name
was changed during setup.
To verify that the Database Engine is running
In Registered Servers, if the name of your instance of SQL
Server has a green dot with a white arrow next to the name, the Database Engine
is running and no further action is necessary.
If the name of your instance of SQL Server has a red dot
with a white square next to the name, the Database Engine is stopped.
Right-click the name of the Database Engine, and then click Start. After a
confirmation dialog box, the Database Engine should start and the circle should
turn green.
To connect to the Database Engine
In Management Studio, on the File menu, click Connect Object
Explorer.
The Connect to Server dialog box opens. The Server type box
displays the type of component that was last used.
Select Database Engine.
In the Server name box, type the name of the instance of the
Database Engine. For the default instance of SQL Server, the server name is the
computer name. For a named instance of SQL Server, the server name is the
<computer_name>\<instance_name>, such as ACCTG_SRVR\SQLEXPRESS.
Click Connect.
Authorizing Additional Connections
Now that you have connected to SQL Server as an
administrator, one of your first tasks is to authorize other users to connect.
You do this by creating a login and authorizing that login to access a database
as a user. Logins can be either Windows Authentication logins, which use
credentials from Windows, or SQL Server Authentication logins, which store the
authentication information in SQL Server and are independent of your Windows
credentials. Use Windows Authentication whenever possible.
Create a Windows Authentication login
In the previous task, you connected to the Database Engine
using Management Studio. In Object Explorer, expand your server instance,
expand Security, right-click Logins, and then click New Login.
The Login - New dialog box appears.
On the General page, in the Login name box, type a Windows
login in the format <domain>\<login>.
In the Default database box, select AdventureWorks2008R2 if
available. Otherwise select master.
On the Server Roles page, if the new login is to be an
administrator, click sysadmin, otherwise leave this blank.
On the User Mapping page, select Map for the
AdventureWorks2008R2 database if it is available. Otherwise select master. Note
that the User box is populated with the login. When closed, the dialog box will
create this user in the database.
In the Default Schema box, type dbo to map the login to the
database owner schema.
Accept the default settings for the Securables and Status
boxes and click OK to create the login.Security Note
This is basic information to get you started. SQL Server
provides a rich security environment, and security is obviously an important
aspect of database operations.
No comments