SQL Server Security Basics

SQL Server Security Basics – Part 1 http://www.LearnNowOnline.com

Objectives • Understand potential data threats and how SQL Server’s design protects against them • Learn about SQL Server and Windows integrated authentication • See how SQL Server provides an authorization system to control access to data and objects

Agenda • Security Overview • Authentication • Authorization

Security Overview • Relational data is a tempting target for attackers • SQL Server 2008 provides plenty of features to secure your data and server • Need to understand the threats • Match countermeasures to the threats

The Threats • Identifying threats is a critical first step • Type of data will probably influence security measures

• Sometimes the best way to protect data is to never put it in a database • Typical threats • Theft of data • Data vandalism • Protecting data integrity • Illegal storage

Security Design Philosophy • Trustworthy Computing memo, 2002 • Four pillars of security design • Secure by design • Secure by default • Secure in deployment • Secure through communications

• “It’s just secure” • Implications throughout the product • SQL Server is reasonably secure out of the box • Your job is to keep it secure Learn More @ http://www.learnnowonline.com Copyright © by Application Developers Training Company

The Two Stages of Security • Similar to Windows security • Authentication: who are you? • Authorization: now that we know who you are,

what can you do?

Key SQL Server Security Terms • • • • •

Authentication Authorization Group Impersonation Login

• • • • •

Permission Principal Privilege Role User

Agenda • Security Overview • Authentication • Authorization

Authentication • Process of verifying that a principal is who or what it claims to be • SQL Server has to uniquely identify principals in order to

authorize

• Two paths to authentication • Windows authentication • SQL Server authentication

Windows Integrated Authentication • SQL Server assumes a trust relationship with Windows Server • Windows does the heavy lifting for authentication • The SQL Server checks permissions on the principal

• Advantages • Single user login • Auditing features • Simplified login management • Password policies

Configuring SQL Server Security Settings • Select either when install or later • Settings apply to all databases and server objects in an instance of SQL Server • Changing modes after installation may or may not cause problems • Windows to Mixed • Mixed to Windows

SQL Server Authentication • Client applications must provide login credentials as part of connection string • Logins stored in SQL Server • Windows authentication stronger • But must use SQL Server authentication with old

versions of Windows, non-Windows systems

Windows and SQL Server Logins • SQL Server logins are not stored in Windows • Disabled if you select Windows authentication

• Mixed mode is much more flexible • But less secure

Beware of the sa Login • • • • • • •

System administrator login Mapped to sysadmin fixed server role Conveys full system administrator privileges Cannot modify or delete Must use a strong password! Use only as access of last resort NEVER use sa for database access through client applications Learn More @ http://www.learnnowonline.com Copyright © by Application Developers Training Company

End of Part 1 http://www.LearnNowOnline.com

Learn More @ http://www.learnnowonline.com Copyright ÂŠ by Application Developers Training Company

Turn static files into dynamic content formats.

Create a flipbook