SQL Server security: Authentication

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.

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 ...
  
  To continue reading for free, register below or login

  Requires Membership to View

  To gain access to this and all member only content, please provide the following information:

  Email Address:
  
  
  

  By joining SearchSystemsChannel.com you agree to receive email updates from the TechTarget network of sites, including updates on new content, magazine or event notifications, new site launches and market research surveys. Please verify all information and selections above. You may unsubscribe at any time from one or more of the services you have selected by editing your profile or unsubscribing via email.
  
  TechTarget cares about your privacy. Read our Privacy Policy
  To read more you must become a member of SearchSystemsChannel.com

  As an existing member of the TechTarget network please activate your SearchSystemsChannel.com account 

  By joining SearchSystemsChannel.com you agree to receive email updates from the TechTarget network of sites, including updates on new content, magazine or event notifications, new site launches and market research surveys. Please verify all information and selections above. You may unsubscribe at any time from one or more of the services you have selected by editing your profile or unsubscribing via email.
  
  TechTarget cares about your privacy. Read our Privacy Policy
  
  
  

  Digg This!     StumbleUpon     Del.icio.us

  
  
  
  

  RELATED CONTENT Relational Database Management Systems (RDBMSes) SQL Server 2008 Reporting Services for high-availability deployment SQL Server 2008 Reporting Services for Internet deployment SQL Server 2008 hardware and software requirements Key features in SQL Server 2008 Reporting Services editions Optimizing SQL Server 2008 performance SQL Server 2008 features study guide Troubleshooting a failed SQL Server 2008 installation Microsoft SQL Server 2008 guide Oracle Database 11g study guide Federated databases Database Management Services SQL Server 2008 Reporting Services for high-availability deployment SQL Server 2008 Reporting Services for Internet deployment Key features in SQL Server 2008 Reporting Services editions SQL Server 2008 hardware and software requirements Optimizing SQL Server 2008 performance SQL Server 2008 features study guide How to automate database integration Using continuous database integration and a database 'sandbox' Troubleshooting a failed SQL Server 2008 installation Microsoft SQL Server 2008 guide

  RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

  
  
  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