SQL Server 2000 Security Features And Deployment Considerations - PowerPoint PPT Presentation

Loading...

PPT – SQL Server 2000 Security Features And Deployment Considerations PowerPoint presentation | free to download - id: 175e74-ZDc1Z



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

SQL Server 2000 Security Features And Deployment Considerations

Description:

... the certificate using Microsoft Internet Explorer or the MMC Certificate Snap-in ... C2-Style Auditing. Must be on an NTFS partition. All events audited ... – PowerPoint PPT presentation

Number of Views:168
Avg rating:3.0/5.0
Slides: 43
Provided by: shrabon
Learn more at: http://www.greycelltechnologies.com
Category:

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: SQL Server 2000 Security Features And Deployment Considerations


1
SQL Server 2000 Security Features and
Deployment Considerations Microsoft
Corporation
2
Agenda
  • Basic overview
  • Logins/users
  • Roles
  • Permissions
  • Securing SQL Server
  • Setup
  • Post-setup configuration
  • Encryption
  • Delegation
  • Security auditing
  • Troubleshooting
  • Security Updates

3
Agenda
  • Basic overview
  • Logins/users
  • Roles
  • Permissions
  • Securing SQL Server
  • Setup
  • Post-setup configuration
  • Encryption
  • Delegation
  • Security auditing
  • Troubleshooting

4
SQL Server Security Modes
  • Windows Authentication security mode
  • Only accept logins using Windows NT/ Windows
    2000 credentials
  • Implements network-wide single sign-on
  • Mixed security mode
  • Allows Windows logins
  • Allows SQL Server-based security

5
(No Transcript)
6
(No Transcript)
7
Logins And Users
  • A Login gives you connection rights
  • Is contained in the master database
  • Applies to the server
  • Has no permissions directly per server
  • Exception Server Role membership
  • A database user is permissions container
  • Also the schema owner
  • Permissions are granted to database users, not
    logins
  • Specific to a single database

8
SQL Server Roles
  • Fixed server roles
  • Server Wide - Flexible server administration
  • Fixed database roles
  • Database Wide - Flexible database administration
  • User Defined roles
  • Custom security combinations
  • Application roles (sp_setapprole)
  • Assign rights to applications instead of users

9
Permissions
  • SQL Server three permission verbs
  • Grant gives a right
  • Deny explicitly denies a right
  • Revoke takes away an existing grant or deny

Grant
-
Revoke
Revoke deny

DENY
Grant
Deny
10
Agenda
  • Basic overview
  • Logins/users
  • Roles
  • Permissions
  • Securing SQL Server
  • Setup
  • Post-setup configuration
  • Encryption
  • Delegation
  • Security auditing
  • Troubleshooting

11
Setup Is Secure
  • If you install into NTFS file system, we secure
    the directories and files
  • Service accounts and the local administrators
    group get full control, no other permissions set
  • We secure the SQL Server registry keys
  • Same permissions as the NTFS files
  • We default to integrated security on NT
  • MSDE install on Win98 and WinMe defaults to Mixed

12
But, Things You May Want To Change
  • Remove Everyone Group from Registry, Grant Full
    control to Admin group, local system account and
    Service account.
  • Built-in\Administrators are sysadmins by default
  • Replace with service accounts, NT
    Authority\System (for mssearch), cluster service
    account on a cluster
  • Never select a blank sa password
  • Set a password for Probe account if used
  • Turn on Failed Login Auditing/additional Auditing
  • Disable the guest account

13
Encryption
  • Network Communications Encryption
  • Multi-protocol
  • SSL
  • Metadata Encryption - Windows Crypto API
  • File Encryption for database files
  • Data Encryption inside the server

14
Multi-Protocol Encryption
  • Still there ?
  • Use for backwards compatibility only
  • Doesnt work with named instances
  • Only targets the machine name, not
    machine\instance
  • Uses Windows RPC
  • So same security strength as the OS
  • Requires a valid windows account

15
SSL Encryption Over The Network
  • You must have a server certificate to negotiate
    SSL encryption
  • Set up the certificate using Microsoft Internet
    Explorer or the MMC Certificate Snap-in
  • Make sure to request the server certificate in
    the fully-qualified DNS name of your server
    SQLServer.Sydney.corp.Microsoft.com

16
Setting Up For SSL Encryption Over the Network
  • We will always encrypt your standard security
    login attempt
  • Not necessary for Integrated login attempts
  • You can optionally request encryption of all
    communications from a single client
  • Just use the checkbox in the client network
    utility
  • But, if the certificate is not trusted by the
    client, your connection attempt will fail

17
Secure Server Option
  • Use to force encryption of all communications
    with this SQL server
  • Turn on encryption via the server network utility
  • Any connection attempt which cant negotiate an
    SSL session will be rejected
  • You MUST have a certificate on the server or no
    communications of any kind is possible (including
    local connections)

18
Meta Data Encryption
  • Just as in previous releases, we can encrypt
  • Stored procedures
  • Triggers
  • Views
  • DTS packages are now encrypted
  • Strictly for SQL Server
  • No, theres no way to hack it (yet)
  • All encryption will be broken eventually

19
File Encryption
  • You can secure the database files
  • Use the Windows Encrypted File System (EFS)
  • Windows 2000 or later
  • Slows down the server, but lt 5 typically
  • Fully Supported
  • Prevents illicit copying of db files
  • Also products from third parties, such as
    http//www.netlib.com

20
Data Encryption
  • Microsoft doesnt provide in the box
  • We could, but key recovery/escrow is really,
    really hard
  • Third party solutions available, including
    http//www.protegrity.com
  • Or, you can write your own
  • Hard to do right and still be secure
  • Some third party solutions out there

21
Kerberos and Delegation
  • Kerberos is the preferred security protocol for
    Windows 2000
  • Much more secure than NTLM
  • Provides for delegation, which is
  • The ability to bridge credentials across more
    than one server

22
Enabling Delegation
  • Must be a Windows 2000 domain, using the Active
    Directory, and client and server using Kerberos
  • This means all computers here are Windows 2000
  • Set the following in the Active Directory
  • The Account is sensitive and cannot be delegated
    option must not be set for the user requesting
    delegation
  • The Account is trusted for delegation option
    must be set for the service account of SQL Server
  • The Server running SQL Server must be allowed to
    delegate credentials (the computer is trusted for
    delegation option)

23
Enabling Delegation (User)
  • SQL Server must have a Service Principal Name
    (SPN) assigned by the Windows 2000 account
    domain administrator assigned to the service
    account of the SQL Server service on that
    particular machine
  • Must enable via the setspn utility in the
    Windows 2000 Resource Kit
  • No dynamic TCP port
  • All accounts must be part of the same domain
  • Setspn A MSSQLSvc/Hostport serviceaccount
  • Example setspn A MSSQLSvc/SQL2.redmond.corp.m
    icrosoft.com1433 SQLaccount
  • Dont specify redmond\SQLaccount doesnt work

24
Enabling Delegation (Machine)
  • Or, you can run under the local system account
    and we will self-register at service startup
  • SQL Server automatically registers the SPNs
    itself no user action required one change to
    Setspn if you wish to use it
  • Setspn A MSSQLSvc/Hostport machine
  • Example setspn A MSSQLSvc/sql21433 sqlaccount
  • LocalSystem is much easier but you lose other
    functionality

25
More Delegation Stuff
  • All accounts must be in the same domain or within
    the same trust tree
  • Dont use dynamic TCP ports for named instances
  • Part of the SPN is the port number
  • You better get along with your domain
    administrators if you want this to work ?

26
Agenda
  • Basic overview
  • Logins/users
  • Roles
  • Permissions
  • Securing SQL Server
  • Setup
  • Post-setup configuration
  • Encryption
  • Delegation
  • Security auditing
  • Troubleshooting

27
SQL Server 2000 Auditing
  • SQL Trace the server side of profiling
  • SQL Profiler the UI components
  • Auditing is performed by SQL Trace internal to
    SQLServr.exe
  • Very robust and secure
  • Collect the minimum necessary data to keep
    overhead reasonable
  • DBA Events, logins, password changes, etc.

28
SQL Trace Features
  • File rollover
  • So you can back up the old trace files while a
    new one is being populated
  • Specify a max file size limit or an end time for
    a trace
  • All audit types and data columns are selectable
  • Get as much or as little auditing as you want/need

29
SQL Server 2000 Audit Events
We audit 19 different kinds of events
  • Login/logout
  • GRD - statement perms
  • GRD object perms
  • Add/drop SQL login
  • GRD NT login rights
  • Modify login property
  • Password change event
  • Add/remove from fixed server role
  • Add/remove database user
  • Derived Permissions
  • Add/remove database role member
  • Add/drop a database role
  • Change Approle password
  • Statement permission used
  • Object permission used
  • Backup/restore event
  • DBCC command issued
  • Audit modification event
  • Server shutdown/pause/start

30
SQL Server 2000 Auditing
  • For each event, many subtypes
  • Example - GRD object permission
  • Grant
  • Revoke
  • Deny
  • Each event includes (at a minimum)
  • Success or failure
  • server name
  • Date/time of event
  • Application name
  • NT username
  • Spid
  • Host name
  • Statement text

31
How To Turn On An Audit
  • An audit (except for C2 audit) is just a profiler
    trace
  • So, turn on a profiler trace with the new
    profiler procedures, adding auditing events
  • Set the trace to start with the server if you
    want a comprehensive audit
  • Wrap the trace setup into a stored procedure
  • Enable that stored procedure for autostart

32
Enabling An Audit (Code Example)
  • Create proc sp_audittrace with encryption as
  • / Complex code here wont fit on slide /
  • Exec sp_trace_create _at_traceid output, 2,
    nd\program files\microsoft sql
    server\mssql\audit\myaudit, 500
  • Return
  • Go
  • Exec sp_procoption p_audittrace, startup, on

33
C2-Style Auditing
  • Must be on an NTFS partition
  • All events audited
  • We will shut down the server if we cant write to
    the audit file
  • The file rollover size is fixed at 200MB
  • The file goes into your mssql\data directory and
    is named audit_YYYYMMDDHHMMSS_1
  • To enable
  • Exec sp_configure C2 audit mode, 1
  • Restart service
  • Dont run C2 mode unless you really need it

34
Recommendations
  • Dont run C2 mode unless you really need it
  • Collect the minimum necessary data to keep
    overhead reasonable
  • DBA Events, logins, password changes, etc.

35
Agenda
  • Basic overview
  • Logins/users
  • Roles
  • Permissions
  • Securing SQL Server
  • Setup
  • Post-setup configuration
  • Encryption
  • Delegation
  • Security auditing
  • Troubleshooting

36
Most Common Security Complaints/Issues
  • Ownership chains
  • Dynamic SQL inside stored procs
  • Changing Object Owner
  • Restore permissions for database owners
  • Should everything be owned by dbo?
  • Xp_cmdshell

37
Ownership Chains
  • Create table user1.t1 (c1 int not null)
  • Create proc user2.proc1 as select from user1.t1
    return
  • If user3 has execute permissions on proc1, still
    need select permissions on user1.t1

38
Dynamic SQL
  • Dynamic SQL inside stored procs runs in the
    security context of the executor of the proc, not
    in the owner of the stored proc
  • This is a security feature to protect you
  • Example
  • Create proc myproc _at_p1 nvarchar(500) as
  • Exec (_at_p1) return 0
  • Exec myproc sp_addlogin richard exec
    sp_addsrvrolemember richard,sysadmin
  • And youre sysadmin ?

39
Changing Object Owner
  • If you drop a user you must first drop/reassign
    all their objects
  • Use sp_changeobjectowner dont directly update
    the system tables
  • Not supported
  • Were working on a better long-term fix in the
    next release

40
Restore Permissions For Database Owners
  • If you restore database with the REPLACE option,
    its really a create database
  • Hence, you need create database permissions
  • Database ownership is not good enough

41
Backup and Restore
  • Security of Backup Files and Media
  • Backup to disk then to tape
  • password protected.
  • If physically available then not secure
  • Restoring to another server
  • Mixed mode database security breaks
  • Windows Authentication (Same Domain)
  • Windows Authentication (Different Domains)
  • Users from a Trusted Domain

42
DBO Own Everything?
  • No, the issue here is to avoid ownership chains
  • No need at all to have everything owned by dbo
  • But it does simplify name resolution
  • db_owner role needs to add/remove login then
    needs to be added to the fixed server role
    SecurityAdmin.

43
XP_CMDSHELL
  • Is dangerous
  • Runs as the service account or as the proxy
    account
  • Dont grant unnecessarily
  • Dont run the service as an administrator
  • Never run on a domain controller and grant
    permissions to xp_cmdshell
  • Restrict to sysadmin
  • xp_regread, xp_regwrite, xp_regdeletekey,xp_regdel
    etevalue, xp_regaddmultistring,xp_regremovemultist
    ring,xp_regenumvalues,xp_regenumkeys
  • Elimination of the SQLAgentCmdExec Proxy Account
    in SQL2K non-system admin access

44
General Permissions
  • Registry permissions
  • HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\MSSQLSERVER
  • Remove Everyone Group
  • NT Admin account restriction
  • SA Account
  • Local system, Local User and Domain User accounts
    Restriction
  • File System

45
Encryption Error
  • SQL2K cluster will fail to come online if Server
    side encryption is used with invalid or no
    certificate-
  • Encrytion requested but no valid certificate
    was found.
  • 17826 Could not set up Net-Library 'SSNETLIB'
  • SQL Server could not spawn FRunCM thread
  • Need to install Certificate Server on the same
    domain as the SQL Server cluster and request a
    valid certificate on each node of Cluster

46
Known Issues With SSL
  • We pick the first certificate we find by default
  • Even if its not valid for SSL
  • Fixed in SP1
  • Certificate Services on the same machine breaks
    us
  • Same issue as above, fixed in SP1
  • If you turn on client side encryption, cant
    communicate to 6.5 or 7.0 servers
  • Because they dont support SSL

47
Agenda
  • Basic overview
  • Logins/users
  • Roles
  • Permissions
  • Securing SQL Server
  • Setup
  • Post-setup configuration
  • Encryption
  • Delegation
  • Security auditing
  • Troubleshooting
  • Security Updates

48
Security Updates
  • Microsoft Security Bulletin MS02-006 (7.0 sp3)
    and MS02-007 (2K sp2) Print - SQL Server Contain
    Unchecked Buffers
  • Microsoft Security Bulletin MS01-032 (7.0 sp3)
    Print - SQL Query Method Enables Cached
    Administrator Connection to be Reused. Only
    effected if using Mixed mode.

49
Summary
  • Use a STRONG SA password
  • Windows vs Mixed authentication
  • Use SQL roles
  • Encryption is available
  • Auditing is available
  • When moving databases there are security
    considerations
  • Do not grant unnecessary access to xp_cmdshell
    and similar sp
  • Stay secure by keeping up to date with security
    patches

50
More info
  • http//www.microsoft.com/technet/treeview/default.
    asp?url/TechNet/security/prodtech/sqlsec.asp
  • http//www.microsoft.com/security/
  • http//www.microsoft.com/sql
About PowerShow.com