A1257278286ksOAR

1
(No Transcript)
2
??????????? SQL BI ????????????? ????? Lets
SQL Together! ??? ?? ??? ?? ??????? ????????,
???????????????, ?????????? ????????? ?
????????????, ????? ???? ??????? ??? ??????? ?
???????? ?? SQL Server BI.
3
????? ?? ???????

• ?? ????????? ????
• ?? ??????????? ???????
• ?? ?????? ????????
• ?? ????? ????????? ?? ?????? ? ????? ????? ? ???
  ?????????????, ???????????? ??????????

4
????

• SQL 2005 Performance monitoring and optimization
• SQL 2005 Upgrade paths and details
• BI Solutions (with MOSS 2007, Pro Clarity,
  PerformancePoint Server)
• SQL 2005 HA
• ? ?????? ????? ? ???????? ? ?????????

5
SQL Server 2005 Security
6
(No Transcript)
7
Microsoft SQL Server Runs the Security Table

• The rate of security vulnerabilities documented
  in the National Vulnerability Database for the
  major database vendors is noteworthy for the
  stark contrast between Microsoft, MySQL and
  Oracle.

http//www.databasesecurity.com/dbsec/comparison.p
df
8
Microsoft SQL Server Runs the Security Table

• ESG believes that Microsofts investments in
  secure development processes are responsible for
  the impressive results in SQL Server quality.

9
Microsoft SQL Server Runs the Security Table

• ESG considers Microsoft to be years ahead of
  Oracle and MySQL in producing secure and reliable
  database products.

10
The comparison

• The Microsoft Security Bulletins Web page
• The Oracle Security Alters web page
• The CVE website
• The SecurityFocus.com website

11
Last 2 years to see better
12
Brief history

• In last three years

• MS SQL Server
• 3 worms
• 15 security bulletins
• 2 remotely exploitable vulns
• 3 months security push

• Oracle Database Server
• 0 worms
• 24 security alerts
• 34 remotely exploitable vulns
• Security push?

• Today

• MS SQL Server
• 0 unpatched vulnerabilities

• Oracle Database Server
• gt100 unpatched vulnerabilities

http//www.argeniss.com/research/SQL-Oracle.zip
13
The facts

• MS SQL Server
• Vulnerabilty rate reduced over years
• Huge security efforts
• Improved security response center
• Improved relation with security researchers

• Oracle Database Server
• Vulnerability rate not reduced over years
• Security efforts?
• Bad security response center
• Bad relation with security researchers

14
Oracle needs time

• Oracles results over the past two years show
  that much work has to be done to bring the
  vulnerabilities into
• line with competing database products from IBM,
  Microsoft, MySQL and Sybase.
• it will take a significant period of time for
  Oracle engineering to get to the root causes of
  the high rate of vulnerabilities and to implement
  corrective development procedures.
• ESG believes that there are no shortcuts in this
  process.

15
What has Microsoft done that the industry can
learn from?

• Bake security into the core
• Reduce attack surfaces
• Break the implementation before customers do

SQL Server 2005 implements Security Development
Lifecycle (SDL)
16
SDL Implementation in Dev Lifecycle
17
Security Development Lifecycle

• The SDL is mandatory for any software that is
• Expected to be used to process personal or
  sensitive information
• Expected to be used in an enterprise or other
  organization (including academia, government, or
  non-profits)
• Expected to be connected to the Internet or
  otherwise used in a networked environment

Progress on SDL Comparative Database
Vulnerabilities 12 Month Study SQL Server
2000 Windows Server 2003 27 High 36
Other MySQL Red Hat Enterprise 3 41 High 75
Other Oracle 10g Red Hat Enterprise 3 73
High 134 Other
18
Regulatory requirements

• Database authentication, including password
  policies and authentication protocols
• Database authorization and access controls
• Data protection for sensitive data that is stored
  in a database
• Data protection for sensitive data that is
  transferred to a database or from a database
• Audits of database transactions to help guarantee
  confidentiality and data integrity

19
SQL Server 2005 Secure By Default

• Efforts made in reducing surface area include
• Some features are off by default (except when you
  perform an upgrade)
• Granular permissions on SQL engine and SQL Agent
• Users need VIEW DEFINITION permissions to see
  metadata that they do not own
• Surface Area Configuration tool helps manage
  off-by-default features

20
Endpoint Based Authentication

• Endpoint
• Point of entry into an instance
• Binds transport protocol to payload

Transport Protocol Named pipes Shared
Memory TCP HTTP VIA
Payloads TDS SOAP SSB Database
Mirroring

• HTTP, Service Broker, Database Mirroring
• Endpoints need to be explicitly created
• No permissions on endpoint by default
• TCP, Named Pipes, Shared Memory
• Default endpoint created at start up
• CONNECT permissions granted to authenticated
  logins
• Permissions can be denied on a per endpoint basis

21
SQL Server Security ModelUsing Endpoints
Network connection request/pre-login handshake
Login authentication request to SQL Server
Switch to a database and authorize access
Attempt to perform some action
22
Password Policy Enforcement

• Enforcement of the following on logins
• Password strength
• Password expiration
• Account lockouts
• Follows local windows password policy
• Supports consistent enterprise wide policy
• Approach
• New password policy check API
• Only available on Windows Server 2003 and above
• On previous versions, rudimentary complexity check

23
What are Principals?
entities that can request SQL Server resources
Securables
Permissions
Principals
24
What are Securables?
resources to which the authorization system
regulates access
Securables
Permissions
Principals
Files
Registry Keys
Server Instance
Database
Schema
25
What are Permissions?
Every securable has associated permissions that
can be granted to a principal.
Securables
Permissions
Principals
Files
ACL
Registry Keys
GRANT/DENY/REVOKE
CREATE ALTER DROP CONTROL CONNECT SELECT EXECUTE U
PDATE DELETE INSERT TAKE OWNERSHIP VIEW
DEFINITION BACKUP
Server Instance
Database
Schema
26
SQL Server 2005 Permissions

• Examples include
• CONTROL Confers ownership-like capabilities on
  the grantee. The grantee effectively has all
  defined permissions on the securable.
• ALTERConfers the ability to change the
  properties, except ownership, of a particular
  securable.
• TAKE OWNERSHIPEnables the grantee to take
  ownership of the securable on which it is
  granted.
• IMPERSONATE ltUsergtEnables the grantee to
  impersonate the user.
• VIEW DEFINITIONEnables the grantee to access
  metadata.
• When considering all the combinations of
  permissions on securables the list reaches almost
  200

27
Data Encryption

• Why consider encryption?
• Additional layer of security
• Required by some regulatory compliance laws
• In SQL Server 2000, vendor support required
• In SQL Server 2005
• Built-in support for data encryption
• Support for key management

28
Cryptography 101 - Encryption
Encryption
Symmetric Keys
0x0088840517080E4FA2
1234-5678-1234-5678
Decryption
29
Cryptography 101 - Encryption
Encryption
Symmetric Keys
0x0088840517080E4FA2
1234-5678-1234-5678
Decryption
Encryption with Public Key
Asymmetric Keys
0x0088840517080E4FA2
1234-5678-1234-5678
Decryption with Private Key
30
SQL Server 2005 Support

• Encryption and Decryption built-ins
• DDL for creation of
• Symmetric Keys
• Asymmetric Keys and Certificates
• Symmetric Keys and Private Keys are always stored
  encrypted
• Securing the Keys themselves
• Based on user passwords
• Automatic, using SQL Server key management

31
Encryption and Decryption Built-ins
EncryptByKey () EncryptByAsymKey () EncryptByCert
() EncryptByPassPhrase () Each with corresponding
DecryptByXxx ( ) ALTER TABLE HumanResources.Emplo
yee ADD EncryptedNatIDNumber varbinary(128)
-- Open the symmetric key to encrypt dataOPEN
SYMMETRIC KEY SSN_Key_01 DECRYPTION BY
CERTIFICATE HR037 -- Encrypt the value in
column NationalIDNumber -- with symmetric key
SSN_Key_01, saving the -- result in column
EncryptedNatIDNumber UPDATE HumanResources.Employ
ee SET EncryptedNatIDNumber
EncryptByKey( Key_GUID('SSN_Key_01'),
NationalIDNumber )
32
Its All About the Keys

• Encryption keys need to be protected
• Some keys need to be backed up
• If the keys are lost, data may be lost
• Therefore, ability to restore keys must exist
• Keys may need to be regenerated
• In case of compromise of the keys
• Keys may need to be shared across systems
• In the case, where encrypted data is being shared

33
(No Transcript)
34
Service Master Key

• Used to secure system data
• Linked server passwords
• Database Master keys
• Proxy credentials
• Created upon first use
• Support for regeneration and recovery
• ALTER SERVICE MASTER KEY DDL
• BACKUP / RESTORE SERVICE MASTER KEY DDL
• Secured using DPAPI
• The service accounts credentials
• Always take a backup!

35
Database Master Key

• Secures private keys inside the database
• Explicitly created by owner of the database
• Support for regeneration and recovery
• ALTER MASTER KEY DDL
• BACKUP / RESTORE MASTER KEY DDL
• Always secured using user password
• Stored in the database
• Can also be secured using Service Master Key
• Always take a backup!

36
Certificates vs. Asymmetric Keys

• Not much difference, they wrap key pair
• Its about
• Packaging
• Flexibility of formats
• Asymmetric key encryption by
• A user key derived from user-supplied password
• The database master key
• Symmetric Key encryption by
• Public key of a certificate
• A user-supplied password
• Another Symmetric Key
• An asymmetric key

37
Encryption Over the wire

• Login Credentials Encryption
• Uses SSL certificate from certificate store (if
  available)
• Can be explicitly chosen
• Otherwise, will use SQL generated Certificate
• Data packets can be encrypted
• Server Side Option Force Protocol Encryption
• Client Side Encryption with or without
  certificate validation

38
Using Certificates to Sign Modules

• Scenario Want to grant access to a resource,
  but only when going through a particular module
• Execute AS support can solve this, but
• Changes execution context
• Another option Signed modules
• Sign the module using a certificate
• Grant the relevant permissions to the certificate
• At execution time
• Certificate temporarily added to token for
  duration of module
• Context of execution remains unchanged
• Access is granted by virtue of presence of
  certificate in token

39
How do Signatures Work?
Grant SELECT to CertUser
Grant EXECUTE to EndUser
SP1
Table 1
Token after SP1
Token before SP1
40
Code signing demo
41
Auditing features

• Auditing on/off
• Traces
• DDL Triggers 
• Using Event Notifications

42
What Are Event Notifications?

• Messages containing event data
• DDL events
• DML events
• Trace events
• Sent to an event processing service by
  usingService Broker
• A message type and contract are predefined
• You must create a queue, a service, and a route

43
How to Create Event Notifications
Define the event notification
Specify the scope
Specify the event
Specify the service
CREATE EVENT NOTIFICATION UpdateStats
CREATE EVENT NOTIFICATION UpdateStats ON SERVER
CREATE EVENT NOTIFICATION UpdateStats ON
SERVER FOR UPDATE_STATISTICS
CREATE EVENT NOTIFICATION UpdateStats ON
SERVER FOR UPDATE_STATISTICS TO SERVICE
'NotifyService', 'current database'
44
How to Process Event Notifications
Receive the message
Extract event data using query()
DECLARE _at_messageTypeName NVARCHAR(256), _at_messageB
ody XML RECEIVE TOP(1) _at_messageTypeName
message_type_name, _at_messageBody
message_body FROM dbo.NotifyQueue IF
_at__at_ROWCOUNT 0 RETURN
DECLARE _at_cmd NVARCHAR(1000) DECLARE _at_posttime
NVARCHAR(24) DECLARE _at_spid NVARCHAR(6) SET _at_cmd
CONVERT(NVARCHAR(100), _at_messagebody.query ('da
ta(//TSQLCommand//CommandText)')) SET _at_posttime
CONVERT(NVARCHAR(24), _at_messagebody.query('data(//
PostTime)')) SET _at_spid CONVERT(NVARCHAR(6), _at_me
ssagebody.query('data(//SPID)'))
45
How to Manage Event Notifications

• Viewing event notifications and queues

SELECT name FROM sys.event_notifications SELECT
definition FROM sys.service_queues

• Deleting event notifications

DROP EVENT NOTIFICATION UpdateStats ON SERVER
46
DEMO Event Notifications
47
(No Transcript)