Security In Sql Server 2008

SQL Server 2008 Security

Presented by Scott Whigham

• Database principals • Schemas • Roles • Assigning Database permissions

More….

• Chapter Overview • Reducing the attack surface area • An Overview of SQL Server Security Model • Logins and Permissions

Database-level Security

Server-level Security

What We’re Going to Cover

• Encryption overview • Linked servers • SIDs and User accounts

Chapter Overview

• This chapter focuses on – How to create and manage logins – How to design and manage database security – Understanding Transparent Data Encryption – Creating and managing Linked Servers

Chapter Overview

• SQL Server Security is not that difficult – Understanding the main security model is easy – Remembering what each term does is easy – Understanding what to do when is easy once explained

• Database principals • Schemas • Roles • Assigning Database permissions

More….

• Chapter Overview • Reducing the attack surface area • An Overview of SQL Server Security Model • Logins and Permissions

Database-level Security

Server-level Security

What We’re Going to Cover

• Encryption overview • Linked servers • SIDs and User accounts

SQL Server Security

• A primary principle of security is to “reduce the attack surface area” • Another main focus is to only allow the least amount of privileges needed to do a task • This chapter teaches how to do both

SQL Server Security

• There are many levels of security in SQL Server – Physical machine – Operating System – Network – Instance – Database

SQL Server Security

• Physical Machine – Who has access to the actual machine? • Hard drives in many servers can easily be removed

– Recommendations to reduce attack surface area: • Server in a locked room with access limited to systems admins only

SQL Server Security

• Operating System – Should you run other software on this machine? • Internet Information Server (IIS), Exchange, SharePoint

– Which users have logins to the server? • If they can login to the server, can they make a file copy of SQL Server databases?

– Recommendations to reduce attack surface area: • The more critical/sensitive/large the database(s), the more likely you need a dedicated machine • Only allow system administrators and DBAs to login to the operating system

SQL Server Security

• Network – By changing default network settings you can reduce the surface area • Changing default instance TCP port from 1433 to another value

– Recommendations to reduce attack surface area: • Place your web SQL Server behind a firewall to prevent remote access (if possible) • Consider allowing local-only connections for applications installed on the server

SQL Server Security

• Instance-level – Defines who can login to the SQL Server instance and what permissions they have instance-wide – sa has instance-wide administrator privileges – Anyone who can login to the SQL Server is called a “login”

SQL Server Security

• Database-level – Defines which logins are able to use this database and what permissions each has in that database – By default, database permissions are granular • Permissions in “AdventureWorks” do not mean a user has those same permissions in another database

SQL Server Security

• Let’s look in-depth at instance-level and database-level security next!

• Database principals • Schemas • Roles • Assigning Database permissions

More….

• Chapter Overview • Reducing the attack surface area • An Overview of SQL Server Security Model • Logins and Permissions

Database-level Security

Server-level Security

In the next video…

• Encryption overview • Linked servers • SIDs and User accounts

SQL Server 2008 Security

Presented by Scott Whigham

• Database principals • Schemas • Roles • Assigning Database permissions

More….

• Chapter Overview • Reducing the attack surface area • An Overview of SQL Server Security Model • Logins and Permissions

Database-level Security

Server-level Security

In this video

• Encryption overview • Linked servers • SIDs and User accounts

SQL Server Security

• Something you need to know before we begin: – Permissions are GRANT, DENY and REVOKE • REVOKE is like an eraser • DENY cannot be overturned • One GRANT is all it takes

SQL Server Security

• Other basic terms to know are: – Logins – Users – Roles – Principals

• Let’s do a quick definition of each!

SQL Server Security

• New term: “Login” – To use a SQL Server, you must have a login – The “sa” login has full rights to the instance – Having a login does not necessarily give you access to a database

SQL Server Security

• New term: “User” – To use a database, you must have a user account • For brevity, just called a “user”

– Just having a user account does not mean you can access a particular table

SQL Server Security

• New term: “Role” – Logins or users can be grouped into a “Role” and then permissions granted to the role • Called “Groups” in Windows and other systems

– There are server roles (instance-level) and database roles – SQL Server combines login/user/role level permissions when evaluating requests

SQL Server Security

• New term: “Principal” – “Principals are entities that can request SQL Server resources”1 • A resource is anything from logging in to manipulating data

– A principal is an abstraction layer that encompasses users, roles, and logins • There are server principals (logins, server roles) and database principals (users, database roles) 1 – Source: SQL Server Books Online

SQL Server Security

• Why the abstraction layer? – Instance-wide permissions can be granted: • To an individual login • To a role that the login belongs to

– Remember that SQL Server combines the login, user and role permissions to determine whether a user is GRANT or DENY • A single DENY prevents access

SQL Server Security

• Why the abstraction layer?

(cont.)

– A “Login” can be created from: • Windows Group – All members of the group are granted/denied access

• Windows User account – A single Windows User account is granted/denied access

• SQL Server Authentication login – The DBA creates a login/password and grants/denies access

SQL Server Security

• Why the abstraction layer?

(cont.)

– By using the generic term “principal” when talking about permissions, it encompasses roles as well as logins of all types – Easier to say “Make sure to DENY all principals” than to say “Make sure you DENY John, Mark, Jane, the CustomerSupport role, and the public role”

• Database principals • Schemas • Roles • Assigning Database permissions

More….

• Chapter Overview • Reducing the attack surface area • An Overview of SQL Server Security Model • Logins and Permissions

Database-level Security

Server-level Security

In the next video…

• Encryption overview • Linked servers • SIDs and User accounts

SQL Server 2008 Security

Presented by Scott Whigham

• Database principals • Schemas • Roles • Assigning Database permissions

More….

• Chapter Overview • Reducing the attack surface area • An Overview of SQL Server Security Model • Logins and Permissions

Database-level Security

Server-level Security

In this video

• Encryption overview • Linked servers • SIDs and User accounts

SQL Server Logins

• The SQL Server DBA is like the door person at an exclusive, invitation-only party – To enter requires your name being on “the list” – If your name isn’t on the list, you can’t get in!

• “The List”, in SQL Server terms, is the list of logins

SQL Server Logins

• A login can be created from: – Windows User account – Windows Group – SQL Server login

• Logins can be granted or denied access

SQL Server Logins

• Example scenario: – The Windows Group “SQLDevs” is granted access – Chad is a member of SQLDevs – Chad’s latest code check-in caused major data problems – What are our options to prevent Chad from logging in?

SQL Server Logins

• Solution #1: Remove Chad’s Windows User account from the Windows group – PROs: • Problem solved quickly and easily

– CONs: • The Windows Group may have other necessary permissions outside of SQL Server • SQL Server DBA may not have rights to Active Directory

SQL Server Logins

• Solution #2: 1. Add Chad’s Windows User account as a login 2. Deny Chad’s Windows User login access 3. Now all other SQLDevs except Chad can login

• PROs – Does not affect Chad’s other Windows permissions outside of SQL Server – SQL Server DBA can implement regardless of Active Directory access

• CONs – Requires an extra step

SQL Server Logins

• Let’s demo!

SQL Server 2008 Security

Presented by Scott Whigham

• Database principals • Schemas • Roles • Assigning Database permissions

More….

• Chapter Overview • Reducing the attack surface area • An Overview of SQL Server Security Model • Logins and Permissions

Database-level Security

Server-level Security

In this video

• Encryption overview • Linked servers • SIDs and User accounts

SQL Server Logins

• SQL Server authentication logins are used when a user/application does not have a Windows account – A 3rd party application uses a custom login

• The “sa” account is a SQL Server login

SQL Server Logins

• To use SQL Server logins requires Mixed Mode – Changing authentication mode requires restarting SQL Server

SQL Server Logins

• SQL Server logins can use password policies of the computer/domain

SQL Server Logins

• Administrative Tools -> Local Security Policy

SQL Server Logins

• There are other logins as well for specific situations – Certificate-based logins and logins mapped to an asymmetric key • Used to sign routines for extra/elevated security

– Logins mapped to a credential • Credentials are simply a fancy database name for a Windows user/password

SQL Server Logins

• All logins have – A default database – Default language for error messages – Server role membership(s) – User mapping – Securables – Status

• Let’s look at each in the next video!

SQL Server Logins

• But first – demo!

SQL Server 2008 Security

Presented by Scott Whigham

• Database principals • Schemas • Roles • Assigning Database permissions

More….

• Chapter Overview • Reducing the attack surface area • An Overview of SQL Server Security Model • Logins and Permissions

Database-level Security

Server-level Security

In this video

• Encryption overview • Linked servers • SIDs and User accounts

SQL Server Logins

• All logins have – A default database – Default language – Server role membership(s) – User mapping – Securables – Status

SQL Server Security

• The default database is the database the user is placed in upon login unless the user requests a different database • The default language specifies the language of error messages and functions

SQL Server Security

• Server roles allow for instance-wide grouping of permissions – Inflexible • Permissions cannot be changed • Cannot create your own roles

– To use, put logins in and take them out

SQL Server Security

• sysadmin role has all permissions – “sa” login is a member of sysadmin

• dbcreator sounds good but…. – Members can DROP any database on the server – (Documentation does not list that little goodie…)

SQL Server Security

• Be careful - each server role has the ability to add other logins to it! – If I add Chad to the “dbcreator” role he can add others to it

• Suggestion: Grant permissions in the Securables tab (upcoming)

SQL Server Logins

• All logins have – A default database – Default language – Server role membership(s) – User mapping – Securables – Status

SQL Server Security

• A login must be made a user of a database upon creation – Unless it is a member of sysadmin server role

• User accounts coming up in a future video…

In the next video…

• All logins have – A default database – Default language – Server role membership(s) – User mapping – Securables – Status

SQL Server 2008 Security

Presented by Scott Whigham

In this video…

• All logins have – A default database – Default language – Server role membership(s) – User mapping – Securables – Status

Securables

• First thing: SQL Server uses the GDR for permissions – GRANT – DENY – REVOKE (i.e. The Eraser)

Securables

• Securables are another abstraction layer – Securables are simply things that you define permissions to • Tables, views, etc

• You define permissions on securables to principals

Securables

• Securables have “scope” – Server • Database, Login

– Database • User, role, schema

– Schema • Table, view, procedure, function

Securables

Securables

• For each of these, there are three basic permissions – CONTROL – ALTER – VIEW DEFINITION • Each level can have individual permissions as well – Logins can have IMPERSONATE permission – TABLES have SELECT permission

Securables

• CONTROL permission effectively gives ownership rights – Includes the ability to give others rights as well -- Give the login Chad sysadmin-level rights:

GRANT CONTROL SERVER to Chad

Securables

• ALTER permission gives ability to modify – ALTER ANY <entity> • Permission applied for all entities of that entity type

– ALTER < entity> • Permission applied for only the given <securable>

Securables -- Give the login Terry the ability to change -Chrissy’s password

GRANT ALTER ON LOGIN::Chrissy TO Terry -- Give Chrissy the option to change -- anyone’s password GRANT ALTER ANY LOGIN TO Chrissy -- Yikes!!!!!

Securables

• VIEW DEFINITION permission allows for viewing: – Source code if given to stored procedures, functions, triggers, views – Login names/info if given at Login level – Table metadata if given at table level

Securables -- Give the login Terry the ability to view -information about Chrissy’s login GRANT VIEW DEFINITION ON LOGIN::Chrissy TO TERRY

-- Give Chrissy the option to view the source -code for a view vw_Customer GRANT VIEW DEFINITION ON vw_Customer TO Chrissy

Securables

• Each permission can also be granted using the WITH GRANT option – If I give Chad the VIEW DEFINITION permission on a stored procedure, he can then give that to Chrissy

Alternative to db_creator role

• Remember our problem with db_creator? – Any member can now ALTER or DROP any user database

• It is recommended that you do not use db_creator

Alternative to db_creator role

• To give logins the ability to create a database without giving them “extra” permissions, grant them the server permission “Create any database”

Alternative to db_creator role

Alternative to db_creator role

Alternative to db_creator role

Securables

• Let’s demo the server-scoped securables

SQL Server 2008 Security

Presented by Scott Whigham

In this video…

• All logins have – A default database – Default language – Server role membership(s) – User mapping – Securables – Status

Status

• Logins can be: – Disabled or Enabled – Grant or Deny – Locked out!

A most confusing screenshot…

Account Status

• Logins that are “Disabled” or have “Deny” cannot login – No functional difference

• If you “Deny”: DENY CONNECT SQL to [login]

• If you “Disable”: ALTER LOGIN [login] DISABLE

Status

• Disabling or denying logins: – Allows you to deny access to logins that you may later need to restore access • Consultants, staff on vacation or out of the office

– Allows you to deny access to creators of objects (databases, tables, etc) yet still let others use those objects • Alternative would be to transfer object ownership to other login and then drop the login

Locked out!

• A SQL Server authentication login can be locked out due to policies

Locked out!

• There are two ways to reset a locked-out account: – By resetting the password – By resetting the lockout time

• It is recommended that you reset lockedout accounts by resetting the password – Resetting the lockout time will clear any password history associated with the account!

Locked out!

• To reset a locked-out account by resetting the password, use ALTER LOGIN ALTER LOGIN [Scott] WITH PASSWORD=‘e%3I' UNLOCK

Locked out!

• To reset a locked-out account without resetting the password, use ALTER LOGIN … WITH CHECK_POLICY ALTER LOGIN [Scott] WITH CHECK_POLICY = OFF GO ALTER LOGIN [Scott] WITH CHECK_POLICY = ON GO -- Account may now login using existing password

SQL Server 2008 Security

Presented by Scott Whigham

• Database principals • Schemas • Roles • Assigning Database permissions

More….

• Chapter Overview • Reducing the attack surface area • An Overview of SQL Server Security Model • Logins and Permissions

Database-level Security

Server-level Security

In this video

• Encryption overview • Linked servers • SIDs and User accounts

Accounts Added by SQL Server

• When you install SQL Server, there are several accounts added by default – SQL Server authentication accounts – Windows authentication accounts – Certificate-based logins

SQL Server accounts added

• sa – Full permissions – Can be disabled – Only available in Mixed mode – Recommendation: DON’T USE IT • Create Windows authentication logins for your sysadmins instead

Certificate-based accounts added

• Logins that are surrounded by the double hash signs are Certificate-based SQL Server logins – – – –

##MS_SQLReplicationSigningCertificate## ##MS_PolicyEventProcessingLogin## ##MS_PolicySigningCertificate## ##MS_PolicyTsqlExecutionLogin## (and several more)

• These get installed depending on which features you have installed

Windows-based accounts added

• SQL Server 2008 also adds several Windows-based logins – NT AUTHORITY\SYSTEM – an internal, default Windows group that is granted sysadmin privilege • Do not modify this account’s privileges • Used by Microsoft Update, service packs, hotfixes, and the SQL Writer Service (and more) • For more information, view KB article 932881

Windows-based accounts added

• SQL Server 2008 creates a local Windows group for each service that you install • These groups are subsequently granted SQL Server logins and given sysadmin privilege

Windows-based accounts added

• For a default install of SQL Server, there are two critical Windows Groups installed: – SQLServerMSSQLUser$<machine_name>$ – SQLServerSQLAgentUser$<machine_name>$

• Remember: Instance IDs for a default instance are MSSQLSERVER

Windows-based accounts added

• For the server named LEARNITFIRST with a default instance of SQL 2008: – SQLServerMSSQLUser$LEARNITFIRST$MSSQLSERVER – SQLServerSQLAgentUser$LEARNITFIRST$MSSQLSERVER

• For a server named COLTRANE with a named instance of SQL Server with an instance ID of “Development”: – SQLServerMSSQLUser$COLTRANE$DEVELOPMENT – SQLServerSQLAgentUser$COLTRANE$DEVELOPMENT

Windows-based accounts added

• SQLServerMSSQLUser group: – Any user in this group can login to SQL Server as a sysadmin – Also has the full privileges of the SQL Server service account – No need to directly edit this group; SQL Server will do the right thing when you change service accounts through the SQL Server Configuration Manager

Windows-based accounts added

• In SQL Server, you will see these new accounts listed as: – NT SERVICE\ – NT SERVICE\SQLAgent

Account Provisioning

• Any accounts that you provision to be system administrators during installation will become sysadmins – Make sure to provision your Windows account during installation!

Account Provisioning

Changes in security

• Unlike previous editions, operating system and domain administrators are not sysadmins in SQL Server by default – BUILTIN\Administrators is no longer added by default – Can be added manually if necessary

• Database principals • Schemas • Roles • Assigning Database permissions

More….

• Chapter Overview • Reducing the attack surface area • An Overview of SQL Server Security Model • Logins and Permissions

Database-level Security

Server-level Security

In the next video

• Encryption overview • Linked servers • SIDs and User accounts

SQL Server 2008 Security: Users

Presented by Scott Whigham

• Database principals • Schemas • Roles • Assigning Database permissions

More….

• Chapter Overview • Reducing the attack surface area • An Overview of SQL Server Security Model • Logins and Permissions

Database-level Security

Server-level Security

In this video

• Encryption overview • Linked servers • SIDs and User accounts

Database-Level Security

• A login allows access to the SQL Server • A user account is required to access a database • Remember: logins and users are actually “principals”

Database-Level Security

• You, the DBA, can grant user accounts: – When creating the login – After the login has been created – Using SSMS’s GUI tools – Using T-SQL’s CREATE USER command

Database-Level Security

• There are four built-in user accounts – dbo • Full privileges; the person who creates the database is the dbo by default unless changed • Sysadmins are dbo of every database

– Guest • Disabled by default

– INFORMATION_SCHEMA • Internal

– Sys • Internal

Database-Level Security

• A login has no user accounts created for it by default – If you enable Guest in a database, this means that any login can use that database even without a user account – Guest is disabled by default; It is not a standard practice to enable Guest

Database-Level Security

• A login may have zero or one user account per database – A login without a user account in a database cannot access that database unless the Guest account is enabled

Database-Level Security

• User names do not have to match login names – Let’s look at the login ScottWhigham: Database

User mapping

master

guest

model

guest

msdb

ScottWhigham

tempdb

guest

AdventureWorks

<none>

ScottsDatabase

dbo

WebsiteMarketing Scott

Database-Level Security

• A login may have zero or one user account per database – A login without a user account in a database cannot access that database

Database-Level Security

• Imagine your SQL Server as a 100-floor skyscraper – Huge foyer complete with menacing looking security guards – Unlike most skyscrapers, you must show your key card (i.e. your credentials) to enter the foyer Image by wallyg at flikr.com

Database-Level Security

• Think of the “secure” foyer as the master database – Anyone who has access to any floor of the skyscraper is allowed in through the turnstiles and into the foyer – Certain staff are only allowed into the foyer

• In SQL Server, this is called a login

Database-Level Security

• The elevators of our skyscraper require a key card – This key card determines which floor(s) you have access to – You may only go to floors that your key card specifically allows Image by SYangPhoto at flikr.com

Database-Level Security

• In SQL Server parlance, the “floor” is really a database – Logins may not use a database unless they have a user account – A user account in one database has no effect on whether a login may access another database • Example: your key card lets you go to the 32nd floor. Would you also expect that key card to allow you permission to the 23rd floor? No!

Database-Level Security

• Let’s demo!

SQL Server 2008 Security: Schemas

Presented by Scott Whigham

• Database principals • Schemas • Roles • Assigning Database permissions

More….

• Chapter Overview • Reducing the attack surface area • An Overview of SQL Server Security Model • Logins and Permissions

Database-level Security

Server-level Security

In this video

• Encryption overview • Linked servers • SIDs and User accounts

Roles and Schemas

• The word schema has multiple definitions in SQL Server: – The structure of a database including tables, columns, and relationships – An XML schema collection – A container

Roles and Schemas

• The word schema has multiple definitions in SQL Server: – The structure of a database including tables, columns, and relationships – An XML schema collection – A container

Roles and Schemas

• DBAs and Developers place objects in schemas for – Organization of objects – Security of objects

HumanResources schema

• Organization: • HumanResources schema in AdventureWorks contains all objects containing information about the Human Resources department

Schemas and name resolution

• When you request an object, SQL Server allows up to four “levels”: SELECT * FROM Server.Database.Schema.Object SELECT * FROM Jupiter.LearnItFirst.dbo.Customer

Schemas and name resolution

• If you are on the same server, you can skip the server name -- Logged on to Jupiter server: SELECT * FROM LearnItFirst.dbo.Customer

Schemas and name resolution

• If you are already in the correct database, you can skip the database -- Logged on to Jupiter server and using LearnItFirst:

SELECT * FROM dbo.Customer

Schemas and name resolution

• How would you query a table in the WebMarketing database while you were in the LearnItFirst database? _________________________________________

Schemas and name resolution

• How would you query a table in the WebMarketing database while you were in the LearnItFirst database? SELECT * FROM WebMarketing.dbo.Log

Schemas and name resolution

• If the default schema for your user account is the same as your desired object’s schema, you can omit the schema: -- Logged on to Jupiter server, in LearnItFirst db, and default schema is “dbo”: SELECT * FROM Customer -- Warning: not best practice

Schemas and name resolution

• We’ll cover more on schemas and name resolution later in the chapter

Schemas

• Let’s demo!

SQL Server 2008 Security: Roles

Presented by Scott Whigham

• Database principals • Schemas • Roles • Assigning Database permissions

More….

• Chapter Overview • Reducing the attack surface area • An Overview of SQL Server Security Model • Logins and Permissions

Database-level Security

Server-level Security

In this video

• Encryption overview • Linked servers • SIDs and User accounts

Roles

• Roles are easy: same thing as a group! • There are three types of roles: – Fixed Server Roles (covered already) – Database-level roles – Application Roles

Roles

• Roles are easy: same thing as a group! • There are three types of roles: – Fixed Server Roles (covered already) – Database-level roles – Application Roles

Database-level Roles

• Database-level roles allow DBAs to group users together and assign permissions to the role – Far superior to doing user-level permissions – Fixed database roles = the built-in roles – Flexible database roles = the ones you create

Database-level Roles

• You can use the built-in database roles as well as creating your own • Some commonly used built-in roles are: Role

Description

db_owner

Full rights to database

public

Any user is a member of public; sets base permissions for all connections

db_ddladmin

Create objects of any type; can alter or drop objects they created

db_datawriter

Can modify the data any table

db_datareader

Can read the data from any table

db_securityadmin

Manage role memberships and permissions

Database-level Roles

• Example of using roles: – Your database has 10 users – Your security assessment shows that there are really three types of permissions needed: • Full control (Micah and Thomas) • Development work (Sam and Nica) • Read-only access to all tables but modify to dbo.Customer table (all others)

Database-level Roles User

Permission

Micah, Thomas

Full control

Sam, Nica

Developers

All others

Read all tables; modify dbo.Customer

• Option 1: Assign each permission to each individual user – Pros: easy to understand when creating – Cons: repetitive, difficult to maintain, silly

Database-level Roles User

Permission

Micah, Thomas

Full control

Sam, Nica

Developers

All others

Read all tables; modify dbo.Customer

• Option 2: Create roles for each group – Pros: easy to understand when creating, easy to assign permissions, easy to add/remove – Cons: ?

Database-level Roles

Login

User

Role

Micah

Micah

public, db_owner

Thomas

Thomas

public, db_owner

Sam

Sam

public, db_ddladmin, Developers

Nica

Nica

public, db_ddladmin, Developers

Five other logins

Five users, one for each login

public, db_datareader, CustomerManagers

Roles and Schemas

• Notice that roles can be given the same permissions that users can

• Database principals • Schemas • Roles • Assigning Database permissions

More….

• Chapter Overview • Reducing the attack surface area • An Overview of SQL Server Security Model • Logins and Permissions

Database-level Security

Server-level Security

In the next video

• Encryption overview • Linked servers • SIDs and User accounts

SQL Server 2008 Security: Roles

Presented by Scott Whigham

• Database principals • Schemas • Roles • Assigning Database permissions

More….

• Chapter Overview • Reducing the attack surface area • An Overview of SQL Server Security Model • Logins and Permissions

Database-level Security

Server-level Security

In this video

• Encryption overview • Linked servers • SIDs and User accounts

Roles

• Roles are easy: same thing as a group! • There are three types of roles: – Fixed Server Roles – Database-level roles – Application Roles

Roles

• Application roles allow you to tie a specific application to certain permissions in the database – Step 1: User launches application – Step 2: Application logs into SQL Server – Step 3: Application uses a database – Step 4: App sets or invokes the application role • The application has now lost the user perms and now has the app role perms

Roles

• To use app roles, you still must have: – Login – User

• You can create app roles in SSMS or TSQL

Roles

Roles

• For an application to use the app role, it must know the password • The stored procedure sp_setapprole is how you invoke or set the app role • sp_unsetapprole can be used to restore the original security contexts

Roles

Roles

Roles

• Application roles are for specific situations – For stateful apps, not stateless apps like websites – Useful for kiosk-style apps in which users will not have their own logins – Useful for apps that bypass SQL Server users and create their own table-based security system – Most people will not ever encounter app roles

• Database principals • Schemas • Roles • Assigning Database permissions

More….

• Chapter Overview • Reducing the attack surface area • An Overview of SQL Server Security Model • Logins and Permissions

Database-level Security

Server-level Security

In the next video

• Encryption overview • Linked servers • SIDs and User accounts

SQL Server 2008 Security: Permissions Presented by Scott Whigham

• Database principals • Schemas • Roles • Assigning Database permissions

More….

• Chapter Overview • Reducing the attack surface area • An Overview of SQL Server Security Model • Logins and Permissions

Database-level Security

Server-level Security

In this video

• Encryption overview • Linked servers • SIDs and User accounts

The GDR

• As we saw earlier, permissions are given using the GDR – GRANT – DENY – REVOKE

The SQL Server Security Model

• Deny takes precedence • User cannot perform an action unless they have been granted that permission • Users inherit permissions from roles • It only takes one DENY to prevent!

The SQL Server Security Model

• Micah is a user of the WebsiteMarketing db – public has DENY SELECT on dbo.Log – Micah belongs to db_datareader – Micah’s user account: • GRANT SELECT on dbo.Log • GRANT UPDATE on dbo.Customer

• What are Micah’s effective permissions?

The SQL Server Security Model

• Micah’s effective permissions are: – Read all tables except dbo.Log – Update dbo.Customer

• Micah is a user of the WebsiteMarketing db – public has DENY SELECT on dbo.Log – Micah belongs to db_datareader – Micah’s user account: • GRANT SELECT on dbo.Log • GRANT UPDATE on dbo.Customer

Securables and Principals

• You will use the GDR to manage permissions at every level • Permissions have scope – Server – Database – Schema

Server scoped permissions

• Server scoped permissions – Apply to the connection and are always “on” -- Give the login Chad sysadmin-level rights:

GRANT CONTROL SERVER to Chad -- Give Chrissy the option to change -- anyone’s password GRANT ALTER ANY LOGIN TO Chrissy

Server scoped permissions

Permission

Description

Administer bulk operations

Required to perform bulk operations

Alter any login

Change anyone’s password, et al

Control server

Sysadmin privileges

Create any database

Allows non-sysadmins to create databases

Shutdown

Uhhhhhhhhh

View any database

Can see metadata about every database; PUBLIC has this permission by default

View any definition

Discussed on next slide

View server state

Discussed on next slide

• Server roles often include these •

Adding a login to sysadmin grants “Control server”

Server scoped permissions

• In SQL Server 7.0 and SQL Server 2000, users were allowed to view the metadata about every object in the database -- Return metadata about every object in SQL 2000:

SELECT * FROM sysobjects

Server scoped permissions

• Starting with SQL Server 2005, you must enable permissions: -- Returns only objects that user has perms defined for:

SELECT * FROM sys.objects

Server scoped permissions

• If you need backward compatibility, then you need to GRANT server scoped permissions -- So that any user can view objects in db:

GRANT VIEW ANY DEFINITION to public -- Give users permissions to see health of server

GRANT VIEW SERVER STATE to public

Server scoped permissions

• Please! Do not issue these statements on a production server unless you know that need them! – These changes in security reflect changes to the SQL Server security model that were put in place for a reason

Roles

• When you use SSMS’ GUI to manage permissions, behind the scenes it will execute GDR statements to perform the requested action

Roles

• The easiest way to learn the Transact-SQL statements that DBAs use is to use the Script button to view the statements that SSMS will execute

Roles

• (We’ll do a full Transact SQL review later)

• Database principals • Schemas • Roles • Assigning Database permissions

More….

• Chapter Overview • Reducing the attack surface area • An Overview of SQL Server Security Model • Logins and Permissions

Database-level Security

Server-level Security

In the next video

• Encryption overview • Linked servers • SIDs and User accounts

SQL Server 2008 Security: Database Permissions Presented by Scott Whigham

• Database principals • Schemas • Roles • Assigning Database permissions

More….

• Chapter Overview • Reducing the attack surface area • An Overview of SQL Server Security Model • Logins and Permissions

Database-level Security

Server-level Security

In this video

• Encryption overview • Linked servers • SIDs and User accounts

Securables and Principals

• You will use the GDR to manage permissions at every level • Permissions have scope – Server – Database – Schema

Database scoped permissions

• Database scoped permissions – Apply to objects in the database only – Can assign permissions to: • Users • Roles • Schemas

Admin-related permissions

Permission

Description

Alter any application role

Change passwords

Alter any role

Create/alter/drop app roles, modify privileges

Alter any schema

Create/alter/drop schemas, modify perms, change object ownership

Alter any user

Create/alter/drop users, rename user; change default schema

Backup database

Can perform FULL and DIFFERENTIAL backups

Backup log

Can perform LOG backups

Control

Same permission as dbo

View database state

Required to view dynamic management views (DMVs) related to database state

Developer-related permissions

Permission

Description

Create assembly

Load new assemblies into database

Create function

Can create functions in any schema unless specifically denied

Create procedure

Can create stored procedures in any schema unless specifically denied

Create table

Can create tables in any schema unless specifically denied

Create view

Can create views in any schema unless specifically denied

Select

Same as placing in db_datareader

Insert, Update, Delete

Same as db_datawriter

Database scoped permissions

• Many user-level and flexible role permissions can be handled with the fixed database roles – “DENY Insert, Update, Delete…” is equivalent to adding user/role to db_denydatawriter role – Giving a user create function, view, proc, and table is the same as db_ddladmin

Database scoped permissions

• Roles can be nested – Step 1: Create DbAdmins flexible database role – Step 2: Make DbAdmins members of db_ddladmin

• The DbAdmins now have the same permissions as db_ddladmin – This may not make much sense yet…

Transact SQL

• Give the user Chrissy the ability to create functions -- Always make sure you are in the right db!

USE[LearnItFirst.com] GO GRANT CREATE FUNCTION TO [Chrissy]

Transact SQL

• Remove Chrissy’s ability to create functions REVOKE CREATE FUNCTION FROM [Chrissy] -- Chrissy now has no permissions

Transact SQL

• Prevent Chrissy from ever being able to create functions DENY CREATE FUNCTION TO [Chrissy]

Exercise

• What is the best way to give a user the ability to read all tables in the WebsiteMarketing db? _________________________________________ • Can you think of at least two other alternatives? _________________________________________

Exercise

• What is the best way to give all users the ability to read all tables in the WebsiteMarketing db? – Add user to db_datareader

• Can you think of at least two other alternatives? – Grant SELECT TABLE as database permission – Manually GRANT SELECT on each table in database – Grant SELECT on each table public role

Up next…

• Enough lecture – let’s demo!

SQL Server 2008 Security: Database Permissions Presented by Scott Whigham

• Database principals • Schemas • Roles • Assigning Database permissions

More….

• Chapter Overview • Reducing the attack surface area • An Overview of SQL Server Security Model • Logins and Permissions

Database-level Security

Server-level Security

In this video

• Encryption overview • Linked servers • SIDs and User accounts

Securables and Principals

• You will use the GDR to manage permissions at every level • Permissions have scope – Server – Database – Schema

What is a schema again?

• Schemas are containers – For convenience – Addictive

• Schemas are used in name resolution SELECT * FROM Server.Database.Schema.Object

Schema scoped permissions

• Schema-scoped permissions – Apply to objects in the schema only – Can assign permissions to: • Tables, views, procedures, functions and much more

• A schema is a database-level securable – Permissions on a schema in one database have no effect on a user’s permissions in another database

Schema level permissions

• If you have a schema-level permission, you have that permission for all objects contained within that schema Permission

Meaning

CONTROL

User has owner-like privileges on all objects in schema

ALTER

User may alter all objects in schema

INSERT, UPDATE, DELETE, SELECT

User may perform these operations (specified individually) on all objects in schema

VIEW DEFINITION

User may view definition (i.e. metadata) on all objects in schema

Roles and Schemas

• DBAs can secure objects at the schema level • DBAs can GRANT/DENY permissions on all objects in schema to users

Up next…

• Impersonation and EXECUTE AS • Demos

SQL Server 2008 Security: Impersonation and EXECUTE AS Presented by Scott Whigham

• Database principals • Schemas • Roles • Assigning Database permissions

More….

• Chapter Overview • Reducing the attack surface area • An Overview of SQL Server Security Model • Logins and Permissions

Database-level Security

Server-level Security

In this video

• Encryption overview • Linked servers • SIDs and User accounts

Impersonation and EXECUTE AS

• Before we get into deep demo mode, you need to understand impersonation at a high level – Impersonation allows you to change the execution context – This means you can execute statements as another login or another user without physically logging in as that principal

Impersonation and EXECUTE AS

• We use the EXECUTE AS command to perform impersonation – You can impersonate a login or a user – Requires the IMPERSONATE permission

• We use REVERT to switch back to our login

Impersonation and EXECUTE AS

• Sysadmins have impersonate permission for all logins and users – This means that the ‘sa’ can execute statements as any login or user

• Normal users cannot perform impersonation by default – You must grant them IMPERSONATE permission on a specific login or user

Granting login impersonation

-- We are logged in as „sa‟ GRANT IMPERSONATE ON Login::[LearnItFirst] TO [Chrissy] -- Chrissy is a login GO

Granting user impersonation

-- We are logged in as „sa‟ USE [LearnItFirst.com] GO GRANT IMPERSONATE ON User::[LIF] TO [Chrissy] -- Chrissy is a user GO

EXECUTE AS syntax { EXEC | EXECUTE ] AS [;] ::= { LOGIN | USER } = 'name' [ WITH { NO REVERT | COOKIE INTO @varbinary_variable } ] | CALLER

– NO REVERT – once activated, cannot be undone using REVERT

Impersonation and EXECUTE AS

• There are two types of context switching – Server-level (impersonating a login) • EXECUTE AS LOGIN = ‘NewLogin’

– Database-level (impersonating a user) • EXECUTE AS USER = ‘NewUser’

Impersonation and EXECUTE AS

• Server-level impersonation: – Once you switch, you are no longer you; you are the login you are now impersonating – Use REVERT to return to your original context – Can impersonate while impersonating! -- Useful T-SQL functions for server-level impersonation: SELECT SUSER_SNAME() AS CurrentLogin, ORIGINAL_LOGIN() AS OriginalLogin

Impersonation and EXECUTE AS

• Database-level impersonation: – Once you switch, you are no longer you; you are the user you are now impersonating – Use REVERT to return to your original context – Can also impersonate while impersonating

• One “gotcha”: once triggered, you cannot access objects from databases that do not have a guest account

Impersonation and EXECUTE AS -- Useful T-SQL functions for database-level impersonation: SELECT SUSER_SNAME() AS CurrentLogin, USER_NAME() AS CurrentUser

Impersonation and EXECUTE AS

• After we discuss object ownership, we will see there is another level of impersonation

Up next…

• Let’s do a few demos showing permissions, roles, users, and context switching!

SQL Server 2008 Security: Ownership Presented by Scott Whigham

• Database principals • Schemas • Roles • Assigning Database permissions

More….

• Chapter Overview • Reducing the attack surface area • An Overview of SQL Server Security Model • Logins and Permissions

Database-level Security

Server-level Security

In this video

• Encryption overview • Linked servers • SIDs and User accounts

Ownership

• In SQL Server, we talk about database ownership, object ownership, and schema ownership – The owner has full rights

Database Ownership

• Database ownership: – The database owner is a login – The dbo user is the database owner – By placing users in the db_owner role, they have similar permissions • However, the dbo can DENY privileges to the members of the db_owner role

Cross database ownership chaining

• “Cross database ownership chaining” (‘CDOC’) allows database owners of one database to act as owners of other databases without having specific user access

Cross database ownership chaining

• “Cross database ownership chaining” is both a server setting and a database setting – Set it at the server-level to enable for entire instance – Set it at the database-level to allow specific databases to participate

Cross database ownership chaining

• “Cross database ownership chaining” is not recommended – Disabled by default – “… should not be enabled unless it is specifically required.” – Source: Books Online

Schema Ownership

• Schema ownership is extremely important – Remember that all objects in a database are contained within a schema – The schema owner has full rights on all objects in that schema

Object Ownership

• Object ownership: – You can grant CONTROL on certain objects within a schema instead of granting permission on the entire schema

Object Ownership

• Transferring ownership is easy – Can transfer schema ownership – Can transfer object ownership

• Can use Transact-SQL (recommended) or the SSMS GUI screens (lots of clicks!)

Object Ownership

• Example #1 – Terry owns the “Sales” schema – Terry leaves the company and we hire Chad to replace him – We transfer ownership of the “Sales” schema to Chad

Object Ownership

• Example #2 – Chrissy owns a table in the “Sales” schema named “Prospects” – Chad owns the schema – We hire Candy to replace Chrissy – We grant CONTROL on “Sales.Prospects” to Candy – We remove Chrissy!

In the next videos…

• Examples and scenarios • Ownership chains • How impersonation can affect permissions • How to transfer ownership of databases and schemas

SQL Server 2008 Security: Ownership Presented by Scott Whigham

• Database principals • Schemas • Roles • Assigning Database permissions

More….

• Chapter Overview • Reducing the attack surface area • An Overview of SQL Server Security Model • Logins and Permissions

Database-level Security

Server-level Security

In this video

• Encryption overview • Linked servers • SIDs and User accounts

Object Ownership

• Let’s walk through a few examples on object ownership first – Remember that the owner has full rights

Example #1

dbo schema (owned by dbo, the user): • Merchant (table) • AddMerchant (proc) • ViewMerchants (view) WebsiteMarketing dbo: Chad

Permissions: • public: <none>

• Can Chad SELECT from dbo.Merchant? – ___________________

• Can Chad execute dbo.AddMerchant? – ___________________

• Why is this? – ___________________

Example #1

dbo schema (owned by dbo, the user): • Merchant (table) • AddMerchant (proc) • ViewMerchants (view) WebsiteMarketing dbo: Chad

Permissions: • public: <none>

• Can Chad SELECT from dbo.Merchant? – Yes!

• Can Chad execute dbo.AddMerchant? – Yes!

• Why is this? – He is the dbo and has full rights to the database

Example #2

dbo schema (owned by dbo, the user): • Merchant (table) • AddMerchant (proc) • ViewMerchants (view) WebsiteMarketing dbo: Chad

Permissions: • public: <none>

• Chrissy is a user with no permissions • Can Chrissy SELECT dbo.Merchant? – ___________________

• Why or why not? – ___________________

Example #2

dbo schema (owned by dbo, the user): • Merchant (table) • AddMerchant (proc) • ViewMerchants (view) WebsiteMarketing dbo: Chad

Permissions: • public: <none>

• Chrissy is a user with no permissions • Can Chrissy SELECT dbo.Merchant? – No

• Why or why not? – Her effective permission does not contain a GRANT

Example #3

dbo schema (owned by dbo, the user): • Merchant (table) • AddMerchant (proc) • ViewMerchants (view) WebsiteMarketing dbo: Chad

Permissions: • public: SELECT on dbo.Merchant

• Chrissy is a user with no permissions • Can Chrissy SELECT dbo.Merchant? – ___________________

• Why or why not? – ___________________

Example #3

dbo schema (owned by dbo, the user): • Merchant (table) • AddMerchant (proc) • ViewMerchants (view) WebsiteMarketing dbo: Chad

Permissions: • public: SELECT on dbo.Merchant

• Chrissy is a user with no permissions • Can Chrissy SELECT dbo.Merchant? – Yes

• Why or why not? – Her effective permission contains a GRANT with no DENY

Example #4

dbo schema (owned by dbo, the user): • Merchant (table) • AddMerchant (proc) • ViewMerchants (view) WebsiteMarketing dbo: Chad

Permissions: • public: SELECT on dbo.Merchant

• Chrissy: member of db_datareader • Chrissy: DENY SELECT ON SCHEMA::[dbo] • Can Chrissy SELECT dbo.Merchant? – ___________________

• Why or why not? – ___________________

Example #4

dbo schema (owned by dbo, the user): • Merchant (table) • AddMerchant (proc) • ViewMerchants (view) WebsiteMarketing dbo: Chad

Permissions: • public: SELECT on dbo.Merchant

• Chrissy: member of db_datareader • Chrissy: DENY SELECT ON SCHEMA::[dbo] • Can Chrissy SELECT dbo.Merchant? – No

• Why or why not? – Her effective permission contains a DENY

Object Ownership

• Clear cut examples like the previous ones make things seem easy • What happens when there are multiple objects and multiple permissions?

Example #5

dbo schema (owned by dbo, the user): • Merchant (table) • AddMerchant (proc) • ViewMerchants (view) WebsiteMarketing dbo: Chad

Permissions: • public: SELECT on dbo.Merchant

• Chrissy: DENY SELECT ON dbo.Merchant • Chrissy: GRANT SELECT ON dbo.ViewMerchants • Can Chrissy SELECT dbo.ViewMerchants? – ___________________

Example #5

dbo schema (owned by dbo, the user): • Merchant (table) • AddMerchant (proc) • ViewMerchants (view) WebsiteMarketing dbo: Chad

Permissions: • public: SELECT on dbo.Merchant

• Chrissy: DENY SELECT ON dbo.Merchant • Chrissy: GRANT SELECT ON dbo.ViewMerchants • Can Chrissy SELECT dbo.ViewMerchants? – Yes, and she can see all of dbo.Merchant!

Object Ownership

• When multiple objects access each other sequentially, this is called a chain

Object Ownership

• When the owner of all objects in the ownership chain is the same, permission is only checked at the entry point of the chain – Example: if the dbo owns all objects in the chain

Ownership Chain Demo

Object Ownership

• This makes life easy for the DBA: you only need to concern yourself with whether your user/role has permissions at the entry point – Most real-world database work is done on objects all owned by the same user

In the next videos…

• Ownership chains • How impersonation can affect permissions • How to transfer ownership of databases and schemas

SQL Server 2008 Security: Ownership Presented by Scott Whigham

• Database principals • Schemas • Roles • Assigning Database permissions

More….

• Chapter Overview • Reducing the attack surface area • An Overview of SQL Server Security Model • Logins and Permissions

Database-level Security

Server-level Security

In this video

• Encryption overview • Linked servers • SIDs and User accounts

Object Ownership

• When there are different owners in the chain, SQL Server checks the permission at each change in ownership – This is called ownership chaining – It’s a hard life dealing with databases with multiple schema owners that also contain objects controlled by multiple users

Ownership Chain Demo

Example #1

• The dbo creates a view, Admin.GetMerchants, that references dbo.Merchant

• The dbo grants Chrissy SELECT on the view • When Chrissy selects from the view, is ownership chaining in effect? – No, the same owner owns all objects!

Object Ownership

• It is not the schema name that causes ownership chaining – It is the fact that the schemas are owned by different users

Object Ownership

• This is hard stuff… Follow me here: – Chrissy: • Owns Admin schema • Has CREATE VIEW privilege • Creates a view, Admin.GetMerchants, based on dbo.Merchant

– Can Chrissy create this view despite not having the SELECT permission?

Object Ownership

• This is hard stuff… Follow me here: – Chrissy: • Owns Admin schema • Has CREATE VIEW privilege • Creates a view, Admin.GetMerchants, based on dbo.Merchant

– Since Chrissy cannot select but can create the view, can she select from her own view and get the data in that way?

Object Ownership

• This is hard stuff… Follow me here: – Chrissy: • Owns Admin schema • Has CREATE VIEW privilege • Creates a view, Admin.GetMerchants, based on dbo.Merchant

– Since Chrissy cannot select but can create the view, can she select from her own view and get the data in that way? • No! She must be given SELECT privilege on dbo.Merchant

Object Ownership

• This is hard stuff… Follow me here: – Chrissy: • Owns Admin schema • Has CREATE VIEW privilege • Creates a view, Admin.GetMerchants, based on dbo.Merchant

– Chrissy GRANTs Terry SELECT permission on her view. Terry has no permissions on dbo.Merchant. Can he SELECT from the view?

Object Ownership

• This is hard stuff… Follow me here: – Chrissy: • Owns Admin schema • Has CREATE VIEW privilege • Creates a view, Admin.GetMerchants, based on dbo.Merchant

– Chrissy GRANTs Terry SELECT permission on her view. Terry has no permissions on dbo.Merchant. Can he SELECT from the view? • No! But for a different reason…

Object Ownership

• Remember: SQL Server checks permissions at each change in ownership • Terry must have: – SELECT permission on Admin.GetMerchants – SELECT permission on dbo.Merchant

• But there is another way……………………….

In the next videos…

• How impersonation can affect permissions • How to transfer ownership of databases and schemas

SQL Server 2008 Security: Ownership Presented by Scott Whigham

• Database principals • Schemas • Roles • Assigning Database permissions

More….

• Chapter Overview • Reducing the attack surface area • An Overview of SQL Server Security Model • Logins and Permissions

Database-level Security

Server-level Security

In this video

• Encryption overview • Linked servers • SIDs and User accounts

Object Ownership

• This is hard stuff… Follow me here: – Chrissy: • Owns Admin schema • Has CREATE VIEW privilege • Creates a view, Admin.GetMerchants, based on dbo.Merchant

– Chrissy GRANTs Terry SELECT permission on her view. Terry has no permissions on dbo.Merchant. Can he SELECT from the view?

Object Ownership

• Remember: SQL Server checks permissions at each change in ownership • Terry must have: – SELECT permission on Admin.GetMerchants – SELECT permission on dbo.Merchant

• But there is another way……………………….

Impersonation in Modules

• The creator of the module (i.e. procedure, view, function, trigger) can specify the execution context – Just because you can doesn’t mean you should…

Example #1: • Chrissy: – Owns Admin schema – Has CREATE VIEW privilege – Creates a view, Admin.GetMerchants, based on dbo.Merchant

CREATE VIEW Admin.GetMerchants AS SELECT * FROM dbo.Merchant GO

Impersonation in Modules

• This example actually runs under the execution context EXECUTE AS CALLER: – EXECUTE AS CALLER says that SQL Server should check permissions at each change in ownership (the default)

Impersonation in Modules

• There are actually four options for switching execution context in modules: – EXECUTE AS CALLER (the default) – EXECUTE AS ‘user_name’ – EXECUTE AS SELF – EXECUTE AS OWNER

Impersonation in Modules

• EXECUTE AS ‘user_name’ – All access to other tables and modules in this module is done via the security context of ‘user_name’ – Creator of module must have IMPERSONATE ‘user_name’

Impersonation in Modules

• EXECUTE AS SELF – All access to other tables and modules in this module is done via the security context of the creator of the module

Impersonation in Modules

• EXECUTE AS OWNER – All access to other tables and modules in this module is done via the security context of the current owner of the module

Example #1: •

Chrissy: – – – –

Owns Admin schema Has CREATE VIEW privilege Creates a view, Admin.GetMerchants, based on dbo.Merchant Has IMPERSONATE „Terry‟

CREATE VIEW Admin.GetMerchants EXECUTE AS ‘Terry’ AS SELECT * FROM dbo.Merchant GO -- Terry must have SELECT on -- dbo.Merchant for any user -- (except dbo) to get data

How to transfer objects…

• The final piece here is knowing how to transfer objects • You can transfer: – Databases to new logins – Schemas and modules to users or roles

To Transfer a Database

• DBAs often create databases and then transfer them to another login – We are stingy about giving out GRANT CREATE DATABASE permissions…

To Transfer a Database

• To transfer a database, use the sp_changedbowner stored procedure – The login must not be an existing user in the database prior to transfer - why? -- Make sure you are in the right DB! USE [LearnItFirst.com] GO EXEC sp_changedbowner „Chrissy‟ -- The login „Chrissy‟ is now the dbo!

To Transfer an Object

• Transferring object ownership is really transferring the object to a new schema or changing the schema’s owner – Generally you want to change the schema’s owner in the real world

To Transfer an Object

• To transfer ownership of a schema: – Generally you want to change the schema’s owner in the real world

To Transfer an Object

• To transfer ownership of a schema in Transact-SQL: ALTER AUTHORIZATION ON SCHEMA::[Customer] TO [Terry]

To Transfer an Object

• If you absolutely, positively have to change the schema of an object (table, view, etc), it is easiest to do so in T-SQL – You can do it in SSMS’ GUI but sheesh! ALTER SCHEMA [NewSchema] TRANSFER „OldSchema.MyTable‟

SQL Server 2008 Security: Encryption Overview Presented by Scott Whigham

• Database principals • Schemas • Roles • Assigning Database permissions

More….

• Chapter Overview • Reducing the attack surface area • An Overview of SQL Server Security Model • Logins and Permissions

Database-level Security

Server-level Security

In this video

• Encryption overview • Linked servers • SIDs and User accounts

Encryption Overview

• SQL Server 2008 has native encryption built in – Connections can be encrypted – Source code can be encrypted – Data can be encrypted

Encryption Overview

• DBAs need to know about: – How to encrypt connections – How to enable encryption – How to backup and restore the encryption mechanisms (i.e. the encryption ‘keys’)

• Let’s talk about each!

Encryption Overview

• DBAs need to know about: – How to encrypt connections – How to enable encryption – How to backup and restore the encryption mechanisms (i.e. the encryption ‘keys’)

Encrypting Connections to SQL Server

• SQL Server can use 40 or 128-bit Secure Sockets Layer (“SSL”) to encrypt network packets – Slower performance but higher security – Requires a certificate to be installed on the machine that hosts the SQL Server – Best practice is to use a trusted certificate • Self-signed certificates can be thwarted

Encrypting Connections to SQL Server

– Step 1: Install certificate – Step 2: In the SQL Server Configuration Manager, right click on Protocols for

Encrypting Connections to SQL Server

– Step 3: Choose your certificate in the Certificates tab

Encrypting Connections to SQL Server

– Step 4: Choose whether or not encryption is required • The Force Encryption option: – If “No”, encryption is optional – If “Yes”, clients who are not encrypted will not be allowed to connect

Encryption Overview

• DBAs need to know about: – How to encrypt connections – How to enable encryption – How to backup and restore the encryption mechanisms (i.e. the encryption ‘keys’)

Encrypting Source Code

• Source code can also be encrypted – However, whether it can be decrypted or not is another matter – Always have a separate copy of your encrypted source code

CREATE PROC MyProc WITH ENCRYPTION AS PRINT „You cannot see the source code now!‟ GO

Encrypting Data

• When we talk about encryption in SQL Server, we are generally discussing the encryption of actual data • There are many ways to encrypt data inside your SQL Server – Pass in data that is already encrypted – Have SQL Server handle the encryption for you

Encrypting Data

• In the next video, we’ll cover the types and techniques of native SQL Server encryption

SQL Server 2008 Security: Encryption Overview Presented by Scott Whigham

• Database principals • Schemas • Roles • Assigning Database permissions

More….

• Chapter Overview • Reducing the attack surface area • An Overview of SQL Server Security Model • Logins and Permissions

Database-level Security

Server-level Security

In this video

• Encryption overview • Linked servers • SIDs and User accounts

Encryption Overview

• DBAs need to know about: – How to encrypt connections – How to enable encryption – How to backup and restore the encryption mechanisms (i.e. the encryption ‘keys’)

Encryption

• Encryption can be done at the databaselevel or at the column-level (or cell-level) • Be aware that encryption will result in poorer performing queries

Encryption

• If you don’t have Enterprise Edition… – You must perform column-level encryption manually using functions like: • • • •

EncryptByPassphrase() EncryptByCert() EncryptByAsymKey() EncryptByKey()

– Column-level encryption is covered in detail in out SQL Server 2008 Transact-SQL programming class • Course 161

Encryption

• If you do have Enterprise Edition, then you can use Transparent Data Encryption (“TDE”) – Entire database is encrypted – Requires no coding changes by developers – Encryption/decryption is done automatically and cannot be bypassed – Performance hit of “3-5%” – Source: Microsoft article, “Database Encryption in SQL Server 2008” via http://learnitfirst.com/go.aspx?id=J4EK

Encryption

• To enable TDE: – Step 1: Create a master key in the master database – Step 2: Create a certificate based on the master key – Step 3: Create a database encryption key – Step 4: Turn it on!

• Let’s look at each step

Encryption

• To enable TDE: – Step 1: Create a master key in the master database – Step 2: Create a certificate based on the master key – Step 3: Create a database encryption key – Step 4: Turn it on!

Encryption

• Step 1: Create a master key in the master database – A master key is a symmetric key used to protect the private keys of certificates and asymmetric keys – There is no “default” master key so you must either create it or use an existing key if already present

Encryption

• Step 1: Create a master key in the master database (cont.) USE master GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password„ -- Encrypted using Triple DES

Encryption

• To enable TDE: – Step 1: Create a master key in the master database – Step 2: Create a certificate based on the master key – Step 3: Create a database encryption key – Step 4: Turn it on!

• Let’s look at each step

Encryption

• Step 2: Create a certificate based on the master key – Required to create Step 3’s database encryption key

USE master GO CREATE CERTIFICATE LearnItFirstCert WITH SUBJECT = „Transparent Data Encryption Cert‟

Encryption

• To enable TDE: – Step 1: Create a master key in the master database – Step 2: Create a certificate based on the master key – Step 3: Create a database encryption key – Step 4: Turn it on!

Encryption

• Step 3: Create a database encryption key – Based on the certificate from Step 2 – Can use AES, DES, Triple DES, RC4, and more USE [LearnItFirst.com] -- The db to protect GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE LearnItFirstCert -- Cert from Step 2

Encryption

• To enable TDE: – Step 1: Create a master key in the master database – Step 2: Create a certificate based on the master key – Step 3: Create a database encryption key – Step 4: Turn it on!

Encryption

• Step 4: Turn it on! USE [LearnItFirst.com] -- The db to protect GO ALTER DATABASE [LearnItFirst.com] SET ENCRYPTION ON GO

TDE

• A few tips for working with TDE: – Backup master and your DB before beginning – Configure on multiple test servers and databases until you are very comfortable before rolling into production – TDE requires time to do the encryption so schedule over a weekend or during planned downtime – Once you enable TDE, tempdb will be encrypted as well • This lowers the performance of SQL Server for non-TDE databases

TDE

• Backup and Restore – Take backups of your service master key, your master key and certificate(s) at the same time you turn on TDE – Store these backups in a separate location from your database backup – Your database cannot be attached or restored without these!

TDE

• Backing up TDE databases requires more… – The service master key will be covered later… BACKUP SERVICE MASTER KEY TO FILE = „F:\SMK_Timestamp.bak„ ENCRYPTION BY PASSWORD = „p@55w0rD!'

TDE

• Backing up TDE databases requires more… BACKUP MASTER KEY TO FILE = „F:\MasterKey.bak„ ENCRYPTION BY PASSWORD = „n3Wp@55!‟

TDE

• Backing up TDE databases requires more… BACKUP CERTIFICATE LearnItFirstCert TO FILE = „F:\LearnItFirstCert.bak‟

TDE

• Restoring TDE databases is exciting! – Make sure you take your heart medicine first… – Step 1: Restore Service Master Key (if needed) – Step 2: Restore Master Key (if needed) – Step 3: Re-create the certificate (if needed) – Step 4: Restore the database

TDE

• We will cover restoring service master keys in chapter 6 • Once all keys are restored and the certificate is created, just restore the database – No need to go through any hoops if restoring to the same server (all keys and certs are present!)

SQL Server 2008 Security: Linked Servers and Distributed Queries Presented by Scott Whigham

• Database principals • Schemas • Roles • Assigning Database permissions

More….

• Chapter Overview • Reducing the attack surface area • An Overview of SQL Server Security Model • Logins and Permissions

Database-level Security

Server-level Security

In this video

• Encryption overview • Linked servers • SIDs and User accounts

Distributed Queries Overview

• You are able to query additional data sources using Transact-SQL: – Linked servers are pre-configured for granular security and repeated use – OPENQUERY(), OPENROWSET() and OPENDATASOURCE() allow you to specify data source at run-time

• These are called distributed queries

Distributed Queries Overview

• Distributed queries often involve multiple data source types – You can JOIN a SQL Server table to an Oracle table, for example

Linked Servers

• Linked Servers: – Allow you to write easy Transact-SQL statements against multiple data sources without having to enter login credentials with each query – Login credentials are stored with the linked server’s definition – Used whenever you need to repeatedly access an external data source from within SQL Server and do not want to have to enter credentials each time

Linked Servers

• Linked Servers are configured by the DBA – In SSMS, go to Server Objects

Linked Servers

• Let’s view a demo!

SQL Server 2008 Security: Linked Servers and Distributed Queries Presented by Scott Whigham

• Database principals • Schemas • Roles • Assigning Database permissions

More….

• Chapter Overview • Reducing the attack surface area • An Overview of SQL Server Security Model • Logins and Permissions

Database-level Security

Server-level Security

In this video

• Encryption overview • Linked servers • SIDs and User accounts

Pass-through queries

• Pass-through queries are queries whose parsing is done completely by the remote data source – Are written in the language of the underlying data source – The SQL Server query parser does not even parse the statement

OPENQUERY()

• OPENQUERY() allows us to issue passthrough queries against linked servers – Anyone can execute – Permission is checked in the linked server – Can issue any SELECT, INSERT, UPDATE and DELETE

OPENQUERY()

-- If we have a linked server named -- JUPITER to a SQL Server 2008 -- instance SELECT * FROM OPENQUERY(Jupiter, „SELECT FirstName, LastName FROM dbo.Customer‟)

OPENQUERY()

-- OPENQUERY is not necessary here -- This is not a pass-through query: SELECT FirstName, LastName FROM Jupiter.TheDb.dbo.Customer -- Notice the four-part address -- Required for linked servers

OPENQUERY()

• OPENQUERY is generally used with nonSQL Server-based linked servers – Like Microsoft Access or Excel, Oracle, DB2 – For SQL Server-based linked servers, we generally just use “regular old four-part queries” SELECT FirstName, LastName FROM Jupiter.TheDb.dbo.Customer

OPENDATASOURCE() and OPENROWSET()

• OPENDATASOURCE() and OPENROWSET() are functions that allow ad-hoc queries to remote data sources – No linked servers – Require the server-level option “Ad Hoc Distributed Queries” to be enabled EXEC sp_configure „Ad Hoc Distributed Queries‟, 1 RECONFIGURE WITH OVERRIDE GO

OPENROWSET()

• OPENROWSET() is extremely flexible – Can bulk load data from text files – Can load data from Oracle, Access, et al -- Example query to MS Access database SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';'admin ';'', Orders) AS x

OPENROWSET()

• Example showing OPENROWSET() -- Example query to a .txt file SELECT * FROM OPENROWSET(BULK 'c:\test\values.txt„)

OPENDATASOURCE()

• OPENDATASOURCE() allows four-part addressing without using a linked server – Not that common

OPENDATASOURCE () -- Example query to MS Access database SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';'admin ';'', Orders)...[Customers] -- Notice the “...”

-- Example query to SQL Server instance SELECT * FROM OPENDATASOURCE('SQLNCLI', 'Data Source=London\Payroll;Integrated Security=SSPI') .AdventureWorks.HumanResources.Employee

• Database principals • Schemas • Roles • Assigning Database permissions

More….

• Chapter Overview • Reducing the attack surface area • An Overview of SQL Server Security Model • Logins and Permissions

Database-level Security

Server-level Security

In the next video

• Encryption overview • Linked servers • SIDs and User accounts

SQL Server 2008 Security: SIDs and Users - What You Need to Know Presented by Scott Whigham

• Database principals • Schemas • Roles • Assigning Database permissions

More….

• Chapter Overview • Reducing the attack surface area • An Overview of SQL Server Security Model • Logins and Permissions

Database-level Security

Server-level Security

In this video

• Encryption overview • Linked servers • SIDs and User accounts

What is a SID?

• SID is an acronym for Security IDentifier • When you create a login, it is given a SID: – Windows-based login SIDs are the same as the Windows account/group SID – SQL Server login SIDs are unique values for that instance

Example

• SIDs are important: – What happens if we want to restore a database on another server? Let’s look at a scenario…

Example

• Your production server has a SQL Server authentication login, “BigLogin” and a database, “OurDb” – BigLogin can access the OurDb database using BigLogin’s user account, “BigUser” – Your application is configured to use the BigLogin login to access SQL Server

Example

• You want to create an offsite copy of the database on another server – You take a backup of OurDb and restore it onto your new SQL Server – You configure your application to use this new server

• Will your application be able to connect?

Example

• No! – You have not created the BigLogin login on the new server

• Lesson learned… So you go create the BigLogin SQL Server authentication login on the new server • Will your application be able to connect?

Example

• Maybe! – It depends on whether or not you have set the default database for the connection string to be OurDb • If you set it, you cannot log in • If you did not set it (unlikely), then you can log in

• Right now, the login on the new server cannot access OurDb!

Example

• Here’s the key take away from this video: – When you create a SQL Server authentication login, it creates it with a random SID by default

Example CREATE LOGIN Scott WITH PASSWORD='p@55w0rD!' GO SELECT sid FROM sys.server_principals WHERE name = 'Scott' GO DROP LOGIN Scott GO CREATE LOGIN Scott WITH PASSWORD='p@55w0rD!' GO SELECT sid FROM sys.server_principals WHERE name = 'Scott„

Example

• When you create a SQL Server authentication login, it creates it with a random SID by default

Example

• So back to our scenario… – When you created the new login, it created it with a random SID – Main point: The user account in the OurDb database on the new server is not associated with your new login!

• Why not?

Example

• Because the SIDs for the login BigLogin are different on each server! • Enough for now… We’ll revisit this in the next chapter and show examples and how to resolve!

Example

• Speaking of the next chapter… • Up next: Chapter 6: Backup and Recovery