Printed 2014/04/23 02:33AM

SQL Server 2005 Security

By Steve Jones, 2007/06/06

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.

Service Accounts
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.

Authentication Mode
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 safe.

Network Connectivity
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 hoc, etc.).
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 possible.

System Procedures:
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.

Password Policy
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.

Admin privileges
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 certificate.
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 doesn't help.

Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.