Introduction to SQL Server 2000 Security

1
Introduction to SQL Server 2000 Security

• Dave Watts
• CTO, Fig Leaf Software
• http//www.figleaf.com/

2
Whats this presentation about?

• What kinds of security problems may occur with
  SQL Server?
• How can you configure your SQL Server to be
  secure?
• What do you have to do within your applications
  to keep SQL Server secure?

3
About SQL Server

• Lots of functionality
• Easy to use and manage, compared to other
  products
• Originally popular as a workgroup product, but
  aimed at enterprise use
• Not secure by default
• Not just used on database servers, but often
  bundled with other products

4
Security problems

• Buffer overflows typically do not require
  authorization for success.
• SQL injection attacker can run arbitrary SQL
  commands through client application with rights
  of that application.

5
Security

• Installation and initial configuration
• Network connectivity
• Trusted and untrusted connections
• Database logins, roles, and rights
• Application security
• Data validation

6
Installation and initial configuration

• Service user accounts
• Filesystem ACLs
• Default roles and permissions within SQL Server
• Control access to system and extended stored
  procedures
• Drop sample databases

7
User accounts

• SQL Server and SQL Server Agent may run as
  SYSTEM, or as specific users.
• SQL Server should run as a low-privilege local
  user account.
• SQL Server Agent may need to be a domain account,
  if replication or other network functionality is
  being used.

8
User account configuration

• During install, specific user accounts can be
  chosen.
• The installer will grant those accounts the
  necessary rights to run SQL Server and related
  processes.
• It will also grant filesystem and registry ACLs
  needed to run SQL Server.
• You will need to create the accounts before
  installation.

9
SQL Server 2000 on Windows Server 2003

• Requires SQL Server 2000 SP2 or higher.
• During installation, you cant choose a
  lower-privilege user account!
• You will need to manually set ACLs and account
  rights yourself!
• Documentation available on MS site, SQL Security
  site.

10
Network topology

• SQL Server should not be exposed on the public
  Internet.
• If possible, it should only be available to the
  web server(s) using it and to internal
  administrative workstations.
• If it needs to be exposed, exposure should be
  limited to specific IP addresses or through VPN.

11
User authentication

• SQL Server supports two types of connections
• Windows Authentication (trusted)
• SQL Server logins (untrusted)

12
Trusted connections

• Generally recommended best practice.
• Windows Authentication uses existing Windows
  accounts.
• Takes advantage of built-in Windows security
  functionality
• Account management
• Password management
• Auditing

13
Trusted connections, contd

• Windows Authentication uses the security context
  of the client process.
• With CF, this means the CF service account would
  be used for authentication.
• The Windows password is not transferred between
  the client and server.

14
Untrusted connections

• Native SQL Server logins do not rely on Windows
  security.
• Most CF applications use native SQL Server
  logins.
• Usernames and passwords are passed as slightly
  obfuscated text.

15
Untrusted connections, contd

• SSL can be used between web server and database
  server to protect credentials from being sniffed.

16
CF and SQL Server authentication

• CFMX doesnt support trusted connections with the
  included JDBC driver.
• The latest version of DataDirect Connect for JDBC
  does support trusted connections.
• Using trusted connections would require that the
  CF Server account have rights to all databases
  used by a web server.
• Impractical unless hosting a single application,
  or using multiple instances.

17
Network connectivity

• Supported protocols
• TCP/IP
• IPX/SPX
• Named Pipes
• TCP/IP is MS recommended choice.
• By default, connections between clients and
  servers use plaintext!

18
Demonstration

• Viewing database connection information for
  untrusted connections

19
Encryption options for database connections

• By default, connections between clients and
  servers use plaintext.
• TCP/IP and SSL
• Multiprotocol
• CF 5 vs CFMX
• CF 5 uses ODBC functionality
• CFMX uses DataDirect JDBC drivers

20
TCP/IP default listening ports

• TCP/1433 client connections
• UDP/1434 discovery
• TCP/2433 client connections if hide server
  option enabled.
• If named instances of SQL Server are installed,
  each will listen on a different, user-defined
  port instead of TCP/1433.

21
Ports, contd

• UDP/1434 can and should be blocked for production
  servers.
• The server can be manually configured to listen
  on a port other than TCP/1433.
• This will limit the effectiveness of worms
  attacking exposed servers.

22
Users and roles within SQL Server

• PUBLIC should be denied access to database
  objects.
• Create new logins for your applications, and
  grant them rights to specific tables and other
  database objects.

23
Roles

• Server roles
• sysadmin
• backup
• security admin
• Database roles
• db owner
• db_datareader
• db_datawriter

24
SQL injection

• Attacker sends arbitrary SQL commands through
  your application.
• Attacker uses error messages (or simply times
  results) to determine success.

25
Demonstration

• SQL injection attack

26
Input filtering

• CFQUERYPARAM
• Stored procedures

27
Resources

• SQL Securityhttp//www.sqlsecurity.com/
• MS Technet Securityhttp//www.microsoft.com/tech
  net/security
• DataDirect Connect for JDBChttp//www.datadirect
  -technologies.com/

28
Conclusion

• If you have any questions, contact
  medwatts_at_figleaf.com
• Thank you!