Introduction to SQL Server 2000 Security

1. Introduction to SQL Server 2000 Security Dave Watts CTO, Fig Leaf Software http://www.figleaf.com/

2. What’s 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 can’t 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, cont’d • 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, cont’d • SSL can be used between web server and database server to protect credentials from being sniffed.

16. CF and SQL Server authentication • CFMX doesn’t 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, cont’d • 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 Security:http://www.sqlsecurity.com/ • MS Technet Security:http://www.microsoft.com/technet/security • DataDirect Connect for JDBC:http://www.datadirect-technologies.com/

28. Conclusion • If you have any questions, contact me:dwatts@figleaf.com • Thank you!