It's a similar theme, off by default if it makes sense, minimize surface area.
Recommendations: for new installs, leave stuff off unless you need it.
Minimize connectivity. for upgrades, turn things off if you do not
truly use them.
SQLSAC - Surface Area tool, launch from command line or setup. There's
a SAC command line utility to script out the configuration that you can
apply to other servers.
BOL says don't use Network Service? Why not? Has more rights than
generally needed. Also a shared account. If penetrated somehow from
another place, control of SQL Server is gained.
Local or domain account is recommended. You can change the password without a shutdown.
Least desirable: Local system
OK: Network Service or Local Service.
Changing the account: Previous advice was go to SQL Service Manager or
Enterprise Manager to change the account. Reason is because large
number of files and folders are ACL'd. Same advice for SQL Server 2005.
Use the tools, not Services applet.
Windows is recommended and is the default.
Exceptions are because an application requires its own login. Also DBAs
do not want to have Windows administrators having control over who can
access SQL Server. This is why there are additional features in SQL
Server 2005 for SQL logins. One thing is that the channel from client
to server needs to be encrypted to cover passwords. SQL Server 2005
uses a self-signed certificate to encrypt the password packet to keep
It's recommended to enable the fewest protocols you need, probably only TCP/IP.
Also default ports should be blocked and changed (1433, 1434)
Endpoints are new entries into SQL Server, but you can secure these to
limited users/roles. You can limit the types of access as well (no ad
DO NOT EXPOSE YOUR SERVER to the Internet. This should be common
knowledge for anyone and it's something you want to do if at all
REVOKE execute from permission from XPs and system stored procedures.
Even though public had rights to run a system proc, the procedures had
a security check. In 2005 all were re-examined to be sure rights were
needed, for example sp_help had a security check added. Many XPs
(XP_dirtree, xp_regread, etc) have security checks added.
They are more secure, but removing XPs results in an unsupported configuration.
In previous versions, no validation of SQL auth passwords.
New features added based on OS capabilities. Recommend that
CHECK_POLICY is left on, CHECK_EXPIRATION is left on and MUST_CHANGE
for new logins. Note, policy is only checked when passwords are
changed, not during an upgrade.
If you set MUST_CHANGE, this only works for 2005 clients (.NET 2.0,
Native Client). The reason is they can send a two password packet that
old clients cannot when a change password is required.
Use when needed. Don't use SA because it's convenient. Also minimize
the number of administrators and provision them explicitly. Don't use
the Windows Administrators group.
Also be sure each administrator has their own account to be sure you can audit and determine who change what.
User Account Control issues.
Protected Admin - Even if you are an admin, your token does not have
the admin token. A check is made to be sure that you have rights and
want to perform the action.
DB Ownership and Trust
SQL2K SP3 turned off database cross chaining. This ensures that a cross
database query has a security check. If you don't need it, turn it off.
If you do need this, you can trust the dbo of the other database or
sign a stored procedure with a certificate and have the dbo trust the
Recommendations are to have distinct owners for each database, not "sa", and only enable trust as needed.
It's a namespeace in the container hierarchy
server -> database -> schema -> object
Can be owned by anyone and can grant permissions at this level. This
was done to isolate applications or groups of objects, separate
administrative grouping from application usage. You can easily change
the user as well.
OK, I'm done. It's too basic a session. I guess if you haven't really
worked with SQL Server, this is a good list of stuff, but I think too
much of this is common knowledge for DBAs. Giving some of the reasons
for changes is good, but for a many of these explanations it sounds
like an excuse or a "ok, we give in". Having a somewhat boring speaker
Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest