SQL Server and Application Security for Developers Mladen Prajdić SQL Server MVP [email protected] @MladenPrajdic About me Welcome to Slovenia The sunny side of alps!
Download ReportTranscript SQL Server and Application Security for Developers Mladen Prajdić SQL Server MVP [email protected] @MladenPrajdic About me Welcome to Slovenia The sunny side of alps!
SQL Server and Application Security for Developers Mladen Prajdić SQL Server MVP [email protected] @MladenPrajdic About me Welcome to Slovenia The sunny side of alps! Security Usability Price Pick two Company Attack Vectors • Website • • • • SQL Injection XSS, CSRF DDOS Other • Social Engineering • People impersonation • Direct person interaction • Others that I haven’t thought of • GCHQ, NSA, CIA, etc SQL Injection http://xkcd.com/327 SQL Injection 83% of hacks Stats by FireHost.com SQL Injection SQL Injection • Website attack with malicious SQL • Error based • Union based • Blind • Data destruction • Data stealing • Spam Redirects SQL Injection - Prevention Tries • Stored procedures • Because they have parameters, right? CREATE PROC spIAmVerySafe @TableName varchar(256) AS EXEC('SELECT * FROM ' + @TableName); GO; CREATE PROC spNowIAmSafe @ID int AS SELECT ID, FirstName, LastName FROM Person WHERE ID = @ID GO; SQL Injection - Prevention Tries • Input validation • Usually server and client keywords blacklists • Replace all single quotes to 2 single quotes ‘ ->’’ • They are all USELESS! DECLARE @s VARCHAR(MAX) = CONVERT(VARCHAR(MAX), 0x53454C454354202A2046524F4D207379732E7461626C6573); EXEC(@s); SELECT * FROM sys.tables SQL Injection - The Only Protection SQL Parameters Use them properly! SqlCommand cmd = new SqlCommand(sqlText, sqlConnection); cmd.Parameters.Add("@IntParam", System.Data.SqlDbType.Int); cmd.Parameters["@IntParam"].Value = 6; SqlDataReader reader = cmd.ExecuteReader(); Cross-Site Scripting (XSS) • Exploits the trust a user has for a particular site • Perfect attack vector to use with SQL Injection • Since 2007 about 84% of all client attacks • About 70% of all websites are likely open to it • Inject javascript into Web pages viewed by other users • Various JS client libraries bugs • HTML, JS, Attribute encode/decode everything Cross-Site Request Forgery (CSRF) • Exploits the trust that a site has in a user's browser • Attacks extremely under-reported • Involve sites that rely on a user's identity • Bank • Exploit the site's trust in that identity • Stored Cookie of the person you’re attacking • Trick browser to send HTTP request to a target site • Cookie authenticates and goes to the bank • Involve HTTP requests that have side effects • Withdraw money DEMO Distributed Denial Of Service (DDOS) • Exploits the resources of your computer • On average at least 1 person in your extended family is unknowingly working for the Russian mafia • Extortion, Political agenda • Feedly, Evernote • Code Spaces • Out of business Amateurs hack systems, professionals hack people Social Engineering • Exploits a person’s kindness and willingness to help • Investment in security awareness in non-IT employees: Minimal • It is much easier to trick someone into giving a password for a system than to spend the effort to crack into the system (Kevin Mitnick) Social Engineering - Profiling Social Engineering – Contact • Calling employees • Call centers, pretending to be support or customer, … • Getting various system information • OS, Broswer, VPN client, WiFi, Anti-virus,… • Phishing with XSS and CSRF included • Giving away information not perceived to be important • Smart small talk • Advanced target level • Hot women in bars • “Forgotten” or free USB sticks Social Engineering - Prevention • Stanley Mark Rifkin defrauded the Security Pacific National bank in Los Angeles managed to steal $10,200,000 in a single social engineering attack • In 1978! Educate people Use two-factor authentication Social Engineering Success rate? 100% Social Engineering Clean up cost for company between $25,000 and $100,000 per incident Securing SQL Server for Developers So how can we as developers protect our Applications and SQL Servers? Security Mechanisms Overview • Run the SQL Server under a special domain account • Create a new “SqlRunner” user in AD • Give it minimal permission to the domain and computer • Use it to run SQL Server • DBA realm • Transparent DB encryption • SQL Server Audit • Reducing the possible surface attack vector Security Mechanisms Overview • Securables • Objects that can be secured with permissions • Principals • People/Processes that access securables • GRANT, DENY, REVOKE • DENY always has priority • Various Cryptographic functions • EncryptBy*, DecryptBy*, SignBy*, HASHBYTES, … Permissions Hierarchy - Principals Windows Server Database Windows Group SQL Server Login Database User Windows Domain Login Fixed Server Role Fixed Database Role Windows Local Login User-defined Fixed Server Role User-defined Database Role Permissions Hierarchy - Securables Server Database SQL Server Login Schema Endpoint User, Certificate, Role, … Database Table, View, Function, Stored Procedure, Type, … Permissions Hierarchy - Example Windows Domain Login OR SQL Server Login Database User Maps 1:1 Depending on permissions from Treat the database access objects as an interface User Permissions User Roles Certificates Return data from Object Access Schema DEMO SET TRUSTWORTHY ON “hole” • If DB is trustworthy • If DB owner login is a sysadmin • If YourAppLogin’s user is member of db_owner role • YourAppLogin can elevate himself to sysadmin • Let’s secure it properly: • YourAppLogin with no default permissions • DB owner’s login in public role only • No users in database in db_owner role DEMO Things to Remember - SQL • Use login/user with least privileges • Run SQL Server service with a custom account • Use SQL parameters • No SysAdmin (SA) or SET TRUSTWORTHY ON • No sysadmin database owners • Treat the database access objects as secure interface Things to Remember - .Net • Machine.config <system.web> <deployment retail="true" /> • Web.config </system.web> • Redirect to custom error pages <customErrors mode="On" defaultRedirect="defaultURL" > <error statusCode="404" redirect="url" /> </customErrors> • HTML encode/decode all traffic from/to DB • Microsoft Web Protection Library (AntiXSS) • Nuget • Also part of the Microsoft SDL tools Things to Remember - Social • Watch out for hot blondes in the bar • Split your security budget • 80%: sysadmin education • 20%: people education • Metasploit • Social-Engineer Toolkit (SET) The less data you store the safer you are