Security Administration Guide

Home

Chapter 2: Overall Process of Security Administration

Chapter 4: Managing SQL Server Logins and Database Users

Security Administration Guide

Chapter 3: Getting Started After Installation

Chapter 3

Getting Started After Installation

Status of SQL Server After Installation

This chapter assumes that you have already installed SQL Server. It provides guidelines for what to do following installation.

When SQL Server is installed, a single login called "sa", with a NULL password, is automatically configured with the System Administrator and System Security Officer roles.

Note: The SQL Server installation and configuration guide recommends that you change the password of the "sa" login, so the password might no longer be NULL. If the password is still NULL, use sp_password to change it as soon as possible after installation. For example, to change the password to "16tons", execute:
sp_password null, "16tons"

Getting Started Administering SQL Server

The "sa" account is configured with the System Administrator and System Security Officer roles, so the account, essentially, has unlimited power. To limit the power of any one login account and to establish individual accountability, use the "sa" account initially to assign the System Security Officer and System Administrator roles to individual users. Then lock the "sa" account so that no user has unlimited access. Consider following the general guidelines listed in the next section to help ensure that your operating environment is secure. Specific steps you can take and an overall example follow the list of guidelines.

Guidelines

Follow these guidelines when you start administering SQL Server:

Use the "sa" login only during initial setup. Instead of allowing several users to use the "sa" account, establish individual accountability by assigning specific roles to individual administrators.
Enable auditing early in the administration process so that you have a record of privileged commands that are executed by System Security Officers and System Administrators. You might also want to audit commands that are executed by those with other special roles, such as Operators when they dump and load databases.
Assign SQL Server login names that are the same as their respective operating system login names. This makes login to SQL Server easier, simplifies management of server and operating system login accounts, and makes it easier to correlate the audit data generated by SQL Server with that of the operating system.

Steps to Take

To follow these guidelines, take the steps listed in Table 3-1 after installation. Be sure to read the sections of this manual that give detailed instructions for each step. For example, before setting up auditing, read Chapter 8, "Auditing," and before assigning roles, read Chapter 5, "Roles in SQL Server." For an example of how to use the "sa" account to set up a secure operating environment, see "An Example of Getting Started".

Note: If you have decided not to install auditing, skip the steps that pertain to auditing.

Table 3-1: Steps to get started
Step
Commands or Utilities to Use
Refer To
Log into your operating system.
This is specific to your operating system.
Your operating system documentation.
Log into SQL Server as "sa". In SQL Server, both the sa_role and sso_role are active when you log in as "sa".
isql utility
Example:
isql -Usa
SQL Server utility programs manual.
Enable auditing for privileged commands.
Before you can do this step, auditing must be installed. See the SQL Server installation and configuration guide for information about how to install auditing.
sp_auditoption
Example:
sp_auditoption "enable auditing",
"on"
sp_auditoption "sa commands",
"both"
Required: To execute sp_auditoption, you must have the sso_role active.
Chapter 8, "Auditing."
Add logins and passwords for System Security Officers, System Administrators, and Operators. Make every SQL Server user's login name the same as the user's operating system login name.
sp_addlogin
Example:
sp_addlogin ralph, orientab,
@fullname ="Ralph Smith"
Required: To execute sp_addlogin, you must have the sso_role active.
Chapter 4, "Managing SQL Server Logins and Database Users."
Grant the sa_role, sso_role, and oper_role to System Administrators, System Security Officers, and Operators, respectively.
sp_role
Example:
sp_role "grant","sso_role", ralph
Required:
To grant the sso_role or oper_role, you must have the sso_role active.
To grant the sa_role, you must have the sa_role active.
Chapter 5, "Roles in SQL Server."
Give access to the sybsecurity database to the System Security Officer or Officers who are responsible for administering auditing.
If you have a single auditing administrator, use sp_changedbowner
Example:
use sybsecurity
sp_changedbowner ralph
If you have more than one auditing administrator, use sp_adduser and grant to give permissions for sybsecurity to the other auditing administrators.
For changing database ownership, see sp_changedbowner in the SQL Server Reference Manual.
To add users to databases, see Chapter 4, "Managing SQL Server Logins and Database Users."
To grant permissions, see Chapter 6, "Managing User Permissions."
Verify that you can log into SQL Server as the users you have configured with the sa_role and sso_role.
Then, lock the "sa" account.
sp_locklogin
Example:
sp_locklogin sa,"lock"
Required: You must have either the sa_role or the sso_role active.
Chapter 4, "Managing SQL Server Logins and Database Users."
Be sure to check all scripts that may contain the "sa" login name and password. These can include scripts that perform backups, run bcp, or perform dbcc checking. The scripts cannot run if they are meant to run as "sa" and that account is locked. Change the logins in those scripts to the name of a user with the correct role.
Use your favorite text editor for this step.

**Table 3-1: Steps to get started**
Step	Commands or Utilities to Use	Refer To
Log into your operating system.	This is specific to your operating system.	Your operating system documentation.
Log into SQL Server as "sa". In SQL Server, both the sa_role and sso_role are active when you log in as "sa".	isql utility Example: isql -Usa	SQL Server utility programs manual.
Enable auditing for privileged commands. Before you can do this step, auditing must be installed. See the SQL Server installation and configuration guide for information about how to install auditing.	sp_auditoption Example: sp_auditoption "enable auditing", "on" sp_auditoption "sa commands", "both" Required: To execute sp_auditoption, you must have the sso_role active.	Chapter 8, "Auditing."
Add logins and passwords for System Security Officers, System Administrators, and Operators. Make every SQL Server user's login name the same as the user's operating system login name.	sp_addlogin Example: sp_addlogin ralph, orientab, @fullname ="Ralph Smith" Required: To execute sp_addlogin, you must have the sso_role active.	Chapter 4, "Managing SQL Server Logins and Database Users."
Grant the sa_role, sso_role, and oper_role to System Administrators, System Security Officers, and Operators, respectively.	sp_role Example: sp_role "grant","sso_role", ralph Required: To grant the sso_role or oper_role, you must have the sso_role active. To grant the sa_role, you must have the sa_role active.	Chapter 5, "Roles in SQL Server."
Give access to the sybsecurity database to the System Security Officer or Officers who are responsible for administering auditing.	If you have a single auditing administrator, use sp_changedbowner Example: use sybsecurity sp_changedbowner ralph If you have more than one auditing administrator, use sp_adduser and grant to give permissions for sybsecurity to the other auditing administrators.	For changing database ownership, see sp_changedbowner in the SQL Server Reference Manual. To add users to databases, see Chapter 4, "Managing SQL Server Logins and Database Users." To grant permissions, see Chapter 6, "Managing User Permissions."
Verify that you can log into SQL Server as the users you have configured with the sa_role and sso_role. Then, lock the "sa" account.	sp_locklogin Example: sp_locklogin sa,"lock" Required: You must have either the sa_role or the sso_role active.	Chapter 4, "Managing SQL Server Logins and Database Users."
Be sure to check all scripts that may contain the "sa" login name and password. These can include scripts that perform backups, run bcp, or perform dbcc checking. The scripts cannot run if they are meant to run as "sa" and that account is locked. Change the logins in those scripts to the name of a user with the correct role.	Use your favorite text editor for this step.

Note: To use the "sa" account later for a SQL Server upgrade, you must unlock the account. After completing the upgrades, you can again lock the account. For information about how to lock and unlock accounts, see "Locking SQL Server Logins: sp_locklogin". See the SQL Server installation and configuration guide for information about upgrading SQL Server.

An Example of Getting Started

Suppose you have decided to assign special roles to these users:

Table 3-2: Example of getting started
Name
Role
Operating System
Login Name
Ralph Smith
sso_role
ralph
Kathy Masters
sa_role
kathy
Mary Randolph
sa_role
mary
Gene Wishing
oper_role
gene

**Table 3-2: Example of getting started**
Name	Role	Operating System Login Name
Ralph Smith	sso_role	ralph
Kathy Masters	sa_role	kathy
Mary Randolph	sa_role	mary
Gene Wishing	oper_role	gene

The following sequence of commands provides an example of using the initial "sa" account to set up a secure operating environment for SQL Server, based upon the role assignments shown in Table 3-2

Table 3-3: Commands to set up a secure operating environment
isql Utility and SQL Commands
What the Commands Are Doing
isql -Usa
Logs into SQL Server as "sa". Both sa_role and sso_role are active.
sp_auditoption "enable auditing","on"
sp_auditoption "sa commands","both"
sp_auditoption "sso commands","both"
sp_auditoption "oper commands","both"
Enables auditing for privileged commands. All commands that require sa_role, sso_role, or oper_role are audited.
If the sso_role is active, you can use the select command to query the sysaudits table, which contains the audit log. To access sysaudits, you must be in the sybsecurity database.
sp_addlogin ralph, orientab, @fullname ="Ralph Smith"
sp_addlogin kathy, grace33, @fullname ="Kathy Masters"
sp_addlogin mary, guten33, @fullname ="Mary Randolph"
sp_addlogin gene, sleeping, @fullname ="Gene Wishing"
Adds logins and passwords for Ralph, Kathy, Mary, and Gene.
A default database is not specified for any of these users, so their default database is master.
sp_role "grant","sso_role", ralph
sp_role "grant","sa_role", mary
sp_role "grant","sa_role", kathy
sp_role "grant","oper_role", gene
Grants the sso_role to Ralph, the sa_role to Mary and Kathy, and the oper_role to Gene.
use sybsecurity
sp_changedbowner ralph
Grants access to the auditing database, sybsecurity, by making Ralph, who is the System Security Officer, the database owner.
sp_locklogin sa,"lock"
Locks the "sa" login. Now, no one can login as "sa". Individuals can assume only the roles that are configured for them.

**Table 3-3: Commands to set up a secure operating environment**
isql Utility and SQL Commands	What the Commands Are Doing
isql -Usa	Logs into SQL Server as "sa". Both sa_role and sso_role are active.
sp_auditoption "enable auditing","on" sp_auditoption "sa commands","both" sp_auditoption "sso commands","both" sp_auditoption "oper commands","both"	Enables auditing for privileged commands. All commands that require sa_role, sso_role, or oper_role are audited. If the sso_role is active, you can use the select command to query the sysaudits table, which contains the audit log. To access sysaudits, you must be in the sybsecurity database.
sp_addlogin ralph, orientab, @fullname ="Ralph Smith" sp_addlogin kathy, grace33, @fullname ="Kathy Masters" sp_addlogin mary, guten33, @fullname ="Mary Randolph" sp_addlogin gene, sleeping, @fullname ="Gene Wishing"	Adds logins and passwords for Ralph, Kathy, Mary, and Gene. A default database is not specified for any of these users, so their default database is master.
sp_role "grant","sso_role", ralph sp_role "grant","sa_role", mary sp_role "grant","sa_role", kathy sp_role "grant","oper_role", gene	Grants the sso_role to Ralph, the sa_role to Mary and Kathy, and the oper_role to Gene.
use sybsecurity sp_changedbowner ralph	Grants access to the auditing database, sybsecurity, by making Ralph, who is the System Security Officer, the database owner.
sp_locklogin sa,"lock"	Locks the "sa" login. Now, no one can login as "sa". Individuals can assume only the roles that are configured for them.

Note: Do not lock the "sa" account until you have configured individual users with the sa_role and sso_role and have verified that the roles operate successfully.


	Expand Search