Problem solve Get help with specific problems with your technologies, process and projects.

SQL Server security: Authentication

SQL Server's security capabilities have advanced quite a bit since SQL Server 2000. Find out how the changes to authentication can benefit your customers.

SQL Server security has been bolstered by the application's handling of authentication and permissions. In recent years, SQL Server customers have demanded stronger authentication and more granular permissions. In SQL Server 2000 and below, it was frequently difficult to configure SQL Server to run under a service account with low privileges. For example, both SQL Server Full-Text Search and replication required (by default) administrator access to the machine.

More on SQL Server
The changing database consolidation landscape

SQL Server database security

If a machine running under elevated security privileges was exploited by a buffer overflow attack (such as slammer), the hacker would have complete rights over the Windows machine running the compromised SQL Server. If this SQL Server account was running under a domain account, the hacker would have network privileges and could compromise other machines on the network that also ran under this account or machines that had granted rights to the compromised SQL Server account. If the compromised SQL Server ran under a domain account, the hacker would have administrator privileges to the entire domain.

To prevent this from happening, SQL Server 2005 was locked by the use of five features:

  • Surface Area Configuration (SAC) Manager: This tool allows DBAs and developers to manage the features of SQL Server 2005 they want to run on their installation. SQL Server 2005's default installation is a minimum one to reduce its exposure to threats. Configuration tools such as SAC disable all but the essential features, presenting fewer areas for the hackers to attack. SAC allows one-click enablement of these features, instead of requiring users to configure numerous switches, set parameters and start services.
  • More granular permissions: SQL Server 2005 introduced highly granular permissions, which allow nonadministrators to carry out functions that were previously administrator-only functions. In SQL Server 2000, to carry out many administrative functions a user has to have the sysadmin role, which essentially allows an administrator to carry out any operation on SQL Server. For example, on a human resources application, anyone in the sysadmin role can view or even manipulate payroll data.
  • Ability to execute stored procedures under a different security context than the "calling" security context: Frequently, it is necessary to execute a stored procedure that will in turn execute other stored procedures. In some cases, the requirement is that the calling stored procedure has fewer rights than one of the stored procedures being called. In SQL Server 2000, this requires a rewrite and/or redesign of the application. In SQL Server 2005, it is possible to change the execution context of a called stored procedure to minimize the rights that the calling stored procedure would have.
  • Separation of schema from objects: SQL Server 2005 changed a schema to be a container object. In that version of SQL Server, the schema can contain or own objects such as tables, functions or stored procedures. The sample database that ships with SQL Server 2005, AdventureWorks, best illustrates schema-owned objects. If you examine this database, you will see tables and stored procedures owned by the Person, HumanResources, Production, Purchasing and Sales schemas. This separation of objects from their schemas can be used to insulate users of one schema from seeing objects in another schema; using the AdventureWorks database, users associated with the Person schema can by default access all objects in the Person schema but not objects in the HumanResources schema, unless the DBA has granted them permissions to access objects belonging to the HumanResources schema as well.
  • New authentication algorithms: SQL Server 2005 also introduced stronger authentication protocols. For example, Windows authentication can be replaced by Kerberos, and HTTP endpoints can use SSL certificates to encrypt the communication going back and forth. The SQL Authentication feature was redesigned to support password policies and expiration of passwords after a predefined interval. Prior to SQL Server 2000, when you used SQL Authentication, the password traveled the ether as plain text and a hacker using a sniffer could read the password. In SQL Server 2000, DBAs requiring high security would have to use force protocol encryption via SSL; find more information about SSL encryption in SQL Server on Microsoft's Help and Support site. Starting in SQL Server 2005, the SQL Authentication traffic is encrypted end to end; find more information about using encryption in SQL Server on this MSDN Library page.

In SQL Server 2008, SAC's functionality was been absorbed into the SQL Server Configuration Manager. The other security features discussed above remain in SQL Server 2008. You should examine your clients' SQL Server installations to ensure that they are following security best practices and educate them on these authentication and permissions features.

Go to page: 1 - 2 - 3

Dig Deeper on Database software management

Join the conversation

1 comment

Send me notifications when other members comment.

Please create a username to comment.

Good article.