1 / 32

Securing SQL Server 2005

Securing SQL Server 2005. Anil Desai. Speaker Information. Anil Desai Independent consultant (Austin, TX) Author of several SQL Server books Instructor, “Implementing and Managing SQL Server 2005” (Keystone Learning) Info: http://AnilDesai.net or Anil@AnilDesai.net. Agenda and Outline.

quemby-warner
Download Presentation

Securing SQL Server 2005

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Securing SQL Server 2005 Anil Desai

  2. Speaker Information • Anil Desai • Independent consultant (Austin, TX) • Author of several SQL Server books • Instructor, “Implementing and Managing SQL Server 2005” (Keystone Learning) • Info: http://AnilDesai.net or Anil@AnilDesai.net

  3. Agenda and Outline • SQL Server Security Architecture • Configuring Service Accounts • Managing Logins and Server Permissions • Database Users and Roles • Managing Permissions • Other Security Features • Encryption • DDL Triggers (Auditing) • Monitoring Security with SQL Profiler

  4. SQL Server Security Overview • Layered Security Model: • Windows Level • SQL Server Level • Database • Schemas (for database objects) • Terminology: • Principals • Securables • Permissions • Scopes and Inheritance

  5. Security Overview • (from Microsoft SQL Server 2005 Books Online)

  6. Security Best Practices • Make security a part of your standard process • Use the principle of least privilege • Implement defense-in-depth (layered security) • Enable only required services and features • Regularly review security settings • Educate users about the importance of security • Define security roles based on business rules

  7. SQL Server Service Accounts • Local Service Account • Permissions of “Users” group (limited) • No network authentication • Network Service Account • Permissions of Users group • Network authentication with Computer account • Domain User Accounts • Adds network access for cross-server functionality

  8. Instance-Specific(one service per instance): SQL Server SQL Server Agent Analysis Services Reporting Services Full-Text Search Instance-unaware Notification Services Integration Services SQL Server Browser SQL Server Active Directory Helper SQL Writer SQL Server Services

  9. SQL Server Surface Area Configuration • Default installation: Minimal services • SAC for Services and Connections • Allow Remote Connections • Access to Reporting Services, SSIS, etc. • SAC for Features • Remote queries • .NET CLR Integration • Database Mail • xp_cmdshell

  10. Managing Logins • Windows Logins • Authentication/Policy managed by Windows • SQL Server Logins • Managed by SQL Server • Based on Windows policies • Password Policy Options: • HASHED (pw is already hashed) • MUST_CHANGE • CHECK_EXPIRATION • CHECK_POLICY

  11. Creating Logins • Transact-SQL • CREATE LOGIN statement • Replaces sp_AddLogin and sp_GrantLogin • SQL Server Logins • Windows Logins • SQL Server Management Studio • Setting server authentication options • Login Auditing • Managing Logins

  12. Managing Server Roles • Built-In Server-Level Roles: • SysAdmin • ServerAdmin • SetupAdmin • SecurityAdmin • ProcessAdmin • DiskAdmin • DBCreator • BulkAdmin

  13. Database Users and Roles • Database Users • Logins map to database users • Database Roles • Users can belong to multiple roles • Guest (does not require a user account) • dbo (Server sysadmin users) • Application Roles • Used to support application code

  14. Database Roles • Built-in Database Roles: • db_accessadmin • db_BackupOperation • db_DataReader • db_DataWriter • db_DDLAdmin • db_DenyDataReader • db_DenyDataWriter • db_Owner • db_SecurityAdmin • public

  15. Creating Database Users and Roles • CREATE USER • Replaces sp_AddUser and sp_GrantDBAccess • Can specify a default schema • Managed with ALTER USER and DROP USER • CREATE ROLE • Default owner is creator of the role • SQL Server Management Studio • Working with Users and Roles

  16. Understanding Database Schemas • Schemas • Logical collection of related database objects • Part of full object name: • Server.Database.Schema.Object • Default schema is “dbo” • Managing Schemas • CREATE, ALTER, DROP SCHEMA • SQL Server Management Studio • Can assign default schemes to database users: • WITH DEFAULT_SCHEMA ‘SchemaName’

  17. Configuring Permissions • Scopes of Securables • Server • Database • Schema • Objects • Permission Settings: • GRANT • REVOKE • DENY • Options • WITH GRANT OPTION • AS (Sets permissions using another user or role)

  18. Managing Execution Permissions • Transact-SQL Code can run under a specific execution context • By default, will execute as the caller • EXECUTE AS clause: • Defined when creating an object or procedure • Options: • CALLER (Default) • SELF: Object creator • Specified database username

  19. Getting Security Information • Procedures and Functions • sys.fn_builtin_permissions • Has_Perms_By_Name • CURRENT_USER • SETUSER • IS_MEMBER • SUSER_NAME / SUSER_ID • SCHEMA_NAME

  20. Security Catalog Views • Sys.Server_Permissions • Sys.Server_Principals • Sys.Database_Permissions • Sys.Database_Principals • Sys.Database_Role_Members

  21. Other Security Options • Encrypting Object Definitions • Use the WITH ENCRYPTION Clause • Stores the object definition in an encrypted format • SQL Server Agent • Proxies based on subsystems allow lock-down by job step types • Preventing SQL Injection attacks

  22. Understanding Encryption • Goals: • Authentication • Data Encryption • Symmetric Encryption • Uses a single key • Asymmetric Encryption • Uses a “key-pair” • Public key: Can be distributed • Private key: Stored securely • Certificates protect the public key

  23. Understanding SQL Server Certificates • Uses of Certificates • Data encryption • Service Broker endpoints • Digital signatures for objects • Web / HTTP connections • SQL Server Certificates • Stored within user databases • Notes: • Encryption overhead can be significant • Keys must be protected

  24. SQL Server Encryption • Encryption Hierarchy • Windows Level • Stores Service Master Key • SQL Server Level • Service Master Key • Database Master Key • Certificates • Asymmetric Keys • Encrypted Objects / Data (varbinary)

  25. Encryption Hierarchy • (from Microsoft SQL Server 2005 Books Online)

  26. Managing Certificates • Transact-SQL commands: • CREATE CERTIFICATE • Stored within the user database • Can use a password or a file • BACKUP CERTIFICATE • Exports to a file • DROP CERTIFICATE • Encrypting Data: • EncryptBy_____ (Cert, SymmetricKey, Passphrase, etc.) • DecryptBy_____()

  27. Getting Certificate Information • Information • Sys.Certificates • Sys.Asymmetric_Keys • Sys.Symmetric_Keys • Cert_ID returns the certificate identifier • CertProperty(Cert_ID, ‘PropertyName’) • Start_Date, Expiry_Date, etc.

  28. DDL Triggers • Respond to Data Definition Language (DDL) commands • Examples: • DROP_TABLE • ALTER_TABLE • CREATE_LOGIN • Purpose: • Preventing certain changes • Logging / sending notifications of schema changes • Can rollback changes

  29. DDL Triggers • DDL triggers do not generate inserted/deleted tables • Getting Details • EVENTDATA function returns details of the changes in XML format • Can query with XQUERY expressions • DDL Trigger Scope: • Database-Level (stored within the same database) • Server-Level (stored in the master database)

  30. Monitoring Security with SQL Profiler • Options: • Log to a trace file or to a table • Run programmatically using SQL Trace SP’s • “Security Auditing” Event Class • Audit Login / Audit Logoff • Audit Add DB User Event • Audit Addlogin Event

  31. SQL Profiler: Security Auditing

  32. For More Information • Resources from Anil Desai • Web Site (http://AnilDesai.net) • E-Mail: Anil@AnilDesai.net • Keystone Learning Course: “Microsoft SQL Server 2005: Implementation and Maintenance (Exam 70-431)” • The Rational Guide to Managing Microsoft Virtual Server 2005 • The Rational Guide to Scripting Microsoft Virtual Server 2005

More Related