Securing SQL Server Processes with Certificates Robert Davis

Securing SQL Server Processes with Certificates Robert, Davis, Database Engineer, Blue. Mountain Capital Management Moderated By: Ivan Sanders

Technical Assistance If you require assistance during the session, type your inquiry into the question pane on the right side. Maximize your screen with the zoom button on the top of the presentation window. Please fill in the short evaluation following the session. It will appear in your web browser.

Thank You to Our Sponsors Empower users with new insights through familiar tools while balancing the need for IT to monitor and manage user created content. Deliver access to all data types across structured and unstructured sources. Redgate Software makes ingeniously simple software used by 650, 000 IT professionals who work with SQL Server, . NET, and Oracle. More than 100, 000 companies use Redgate products, including 91% of the Fortune 100. Redgate’s philosophy is to design highly usable, reliable tools which elegantly solve the problems that developers and DBAs face every day.

Join PASS is a not-for-profit organization which offers year-round learning opportunities to data professionals. Access to online training and content Join Local Groups and Virtual Groups MEMBERSHIP IS FREE, JOIN TODAY www. pass. org Get advance notice of member exclusives Enjoy discounted event rates

Robert L Davis MCM/MCSM, MVP, DBA SQL Server Certified Master, Data Platform MVP, and experienced DBA, evangelist, speaker, writer, and trainer. Currently works as a Database Engineer at Blue. Mountain Capital Management. Blogger, writer facebook. com/robert. l. davis. 75 twitter. com/SQLSoldier linkedin. com/in/robertldavis/ Check out my blog at www. sqlsoldier. com where I also have links to the many whitepapers I have written as well as articles for SQL Server Pro magazine. PASS Security Virtual Chapter Co-founder and co-leader of the PASS Security Virtual Chapter. If interested in speaking or volunteering for the Security VC, contact us at securityvc@sqlpass. org

Securing SQL Server Processes with Certificates Robert, Davis, Database Engineer, Blue. Mountain Capital Management

Robert L Davis MCM/MCSM, MVP, DBA SQL Server Certified Master, Data Platform MVP, and experienced DBA, evangelist, speaker, writer, and trainer. Currently works as a Database Engineer at Blue. Mountain Capital Management. Blogger, writer facebook. com/robert. l. davis. 75 twitter. com/SQLSoldier linkedin. com/in/robertldavis/ Check out my blog at www. sqlsoldier. com where I also have links to the many whitepapers I have written as well as articles for SQL Server Pro magazine. PASS Security Virtual Chapter Co-founder and co-leader of the PASS Security Virtual Chapter. If interested in speaking or volunteering for the Security VC, contact us at securityvc@sqlpass. org

Securing SQL Server Processes with Certificates Managing Certificates

Securing SQL Server Processes with Certificates Managing Certificates Creating Logins and Users Mapped to Certificates

Securing SQL Server Processes with Certificates Managing Certificates Creating Logins and Users Mapped to Certificates Signing Procedures

Securing SQL Server Processes with Certificates Managing Certificates Creating Logins and Users Mapped to Certificates Signing Procedures for SQL Server Processes

Securing SQL Server Processes with Certificates Managing Certificates • Creating certificates

Securing SQL Server Processes with Certificates Managing Certificates • Creating certificates • CREATE CERTIFICATE

Securing SQL Server Processes with Certificates Managing Certificates • Creating certificates • CREATE CERTIFICATE • Backing up certificates

Securing SQL Server Processes with Certificates Managing Certificates • Creating certificates • CREATE CERTIFICATE • Backing up certificates • BACKUP CERTIFICATE

Securing SQL Server Processes with Certificates Managing Certificates • Creating certificates • CREATE CERTIFICATE • Backing up certificates • BACKUP CERTIFICATE • Restoring certificates

Securing SQL Server Processes with Certificates Managing Certificates • Creating certificates • CREATE CERTIFICATE • Backing up certificates • BACKUP CERTIFICATE • Restoring certificates • CREATE CERTIFICATE

Securing SQL Server Processes with Certificates Managing Certificates • Creating certificates • CREATE CERTIFICATE • Backing up certificates • BACKUP CERTIFICATE • Restoring certificates • CREATE CERTIFICATE … FROM FILE

Securing SQL Server Processes with Certificates Managing Certificates • Creating certificates • CREATE CERTIFICATE • Backing up certificates • BACKUP CERTIFICATE • Restoring certificates • CREATE CERTIFICATE … FROM FILE • Store securely

Securing SQL Server Processes with Certificates Managing Certificates • Creating certificates • CREATE CERTIFICATE • Backing up certificates • BACKUP CERTIFICATE • Restoring certificates • CREATE CERTIFICATE … FROM FILE • Store securely Demo

Securing SQL Server Processes with Certificates Creating Logins and Users Mapped to Certificates • Creating logins

Securing SQL Server Processes with Certificates Creating Logins and Users Mapped to Certificates • Creating logins • CREATE LOGIN … FROM CERTIFICATE

Securing SQL Server Processes with Certificates Creating Logins and Users Mapped to Certificates • Creating logins • CREATE LOGIN … FROM CERTIFICATE • Creating users

Securing SQL Server Processes with Certificates Creating Logins and Users Mapped to Certificates • Creating logins • CREATE LOGIN … FROM CERTIFICATE • Creating users • CREATE USER … FOR/FROM CERTIFICATE

Securing SQL Server Processes with Certificates Creating Logins and Users Mapped to Certificates • Creating logins • CREATE LOGIN … FROM CERTIFICATE • Creating users • CREATE USER … FOR/FROM CERTIFICATE Demo

Securing SQL Server Processes with Certificates Signing Stored Procedures • Grant permissions for a stored procedure without granting to user

Securing SQL Server Processes with Certificates Signing Stored Procedures • Grant permissions for a stored procedure without granting to user • Allows you to avoid common issues where you may otherwise be pressured to enable risky database options

Securing SQL Server Processes with Certificates Signing Stored Procedures • Grant permissions for a stored procedure without granting to user • Allows you to avoid common issues where you may otherwise be pressured to enable risky database options • Cross-database ownership chaining

Securing SQL Server Processes with Certificates Signing Stored Procedures • Grant permissions for a stored procedure without granting to user • Allows you to avoid common issues where you may otherwise be pressured to enable risky database options • Cross-database ownership chaining • Trustworthy

Securing SQL Server Processes with Certificates Signing Stored Procedures • Grant permissions for a stored procedure without granting to user • Allows you to avoid common issues where you may otherwise be pressured to enable risky database options • Cross-database ownership chaining • Trustworthy • Signing the procedures

Securing SQL Server Processes with Certificates Signing Stored Procedures • Grant permissions for a stored procedure without granting to user • Allows you to avoid common issues where you may otherwise be pressured to enable risky database options • Cross-database ownership chaining • Trustworthy • Signing the procedures • ADD SIGNATURE TO … BY CERTIFICATE … WITH PASSWORD

Securing SQL Server Processes with Certificates Signing Stored Procedures • Grant permissions for a stored procedure without granting to user • Allows you to avoid common issues where you may otherwise be pressured to enable risky database options • Cross-database ownership chaining • Trustworthy • Signing the procedures • ADD SIGNATURE TO … BY CERTIFICATE … WITH PASSWORD • Executes as certificate which is mapped to a user and/or login

Securing SQL Server Processes with Certificates Signing Stored Procedures • Grant permissions for a stored procedure without granting to user • Allows you to avoid common issues where you may otherwise be pressured to enable risky database options • Cross-database ownership chaining • Trustworthy • Signing the procedures • ADD SIGNATURE TO … BY CERTIFICATE … WITH PASSWORD • Executes as certificate which is mapped to a user and/or login Demo

Securing SQL Server Processes with Certificates Signing Stored Procedures for SQL Server Processes • Relies on everything we’ve learned so far

Securing SQL Server Processes with Certificates Signing Stored Procedures for SQL Server Processes • Relies on everything we’ve learned so far • Can be used to execute signed procedure via Service Broker

Securing SQL Server Processes with Certificates Signing Stored Procedures for SQL Server Processes • Relies on everything we’ve learned so far • Can be used to execute signed procedure via Service Broker • Can be used to grant rights to CLR assemblies

Securing SQL Server Processes with Certificates Signing Stored Procedures for SQL Server Processes • Relies on everything we’ve learned so far • Can be used to execute signed procedure via Service Broker • Can be used to grant rights to CLR assemblies • More work but more secure

QUESTIONS?

Coming up next! Protecting Data Across the Environment Brian Kelley

THANK YOU FOR ATTENDING