10 Deadly Sins of SQL Server Configuration - APPSEC CALIFORNIA 2015

10 Deadly Sins of SQL Server Configuration The untold stories of a pentest monkey

Scott Sutherland Network and Application Penetration Tester Twitter: @_nullbind Code: https://github.com/nullbind https://github.com/netspi Slides: http://slideshare.net/nullbind http://slideshare.net/netspi Blog: https://blog.netspi.com/author/scott-sutherland/ Who am I?

Presentation Overview • Why security breaks • Where security breaks • SQL Server security basics • Finding SQL Servers • 10 deadly configurations • What can be done • Questions

Why Security Breaks • NOT the right skill set ‒ Most dev-ops and IT admins aren’t DBAs ‒ Modern DBMS can be complicated • NOT a high priority / requirement ‒ Functionality ‒ Availability ‒ Performance ‒ Security • NOT enough time

Where Security Breaks …at points of integration and trust • Access to external sources ‒ Other databases ‒ Other servers ‒ On the file system / shares • Cached authentication • User impersonation • Excessive privileges • Explicit and implicit trusts

SQL Server Security Basics: Services What is SQL Server? • SQL Server is software • Each installation is called an “instance” which runs as a set of Windows services separate process, port, etc • Services run with privileges of the Windows service account

SQL Server Security Basics: Services

SQL Server Security Basics: Principals Windows Server Level • Windows accounts and groups SQL Server Level • SQL Server logins and SQL Server roles Database Level • Database users and database roles

SQL Server Security Basics: Principals Windows Server Level • Used to log into SQL Server SQL Server Level • Used to log into SQL Server Database Level • Database users are mapped to a login/account • Used to access databases and data

SQL Server Security Basics: Principals Windows Operating System SQL Server Instance 1 (Windows Service) Database 1 Table 2 Table 2 Table 3 Windows account Domainmyuser2 Maps to database user myuser2 Maps to database user myuser1 SQL Server login myuser1 SQL Server login Domainmyuser2

SQL Server Security Basics: Roles Important Server Roles • Sysadmin role = DBA • Public role = Everyone with connect Important Database Roles • Database owner = owns the database • Db_owner role = any action in the database

Finding SQL Servers: Unauthenticated TCP/UDP Port Scanning • Pros: Finds non domain instances • Cons: Can be slow Tools • Metasploit • Nessus • SQLping3 • OSQL/SQLCMD

Finding SQL Servers: Authenticated Service Principal Names (SPN) • Pros: Fast and returns most SQL Servers on the domain • Cons: Will miss instances on non domain systems Tools • setspn.exe • adfind.exe • Get-Spn.psm1

10 Deadly Configurations 1. Logins with Sysadmin Privileges 2. Logins with IMPERSONATE Privileges 3. Database User Privileges 4. Procedures with SQL Injection 5. Public EXECUTE on Dangerous Procedures 6. Service Account Privileges 7. Domain User Privileges 8. Database Link Chaining and Excessive Privileges 9. Weak and Default Passwords 10. No Transport Encryption

#1 Logins with Sysadmin Privileges

#1 Logins with Excessive Privileges What’s the issue? • Applications connecting to SQL Server with the “sa” login • Applications connecting to SQL Server with another login with the sysadmin role

#1 Logins with Excessive Privileges Why is it a problem? • Full access to all databases on server • Often full access to the Windows server • Free tools available for taking over the server ‒ Metasploit mssql_payload module ‒ Metasploit mssql_payload_sqli module

#1 Logins with Excessive Privileges What are the attack requirements? • SQL injection or a direct connection with credentials to log into SQL Server

#1 Logins with Excessive Privileges What’s the fix? • Don’t use the “sa” login for your application • Don’t assigned sysadmin privileges • Do assign only the privileges necessary for the application to meet functional requirements

#2 Logins with IMPERSONATE Privileges

#2 Logins with IMPERSONATE privilege What’s the issue? • SQL Server logins with IMPERSONATE privileges

#2 Logins with IMPERSONATE privilege Why is it a problem? • Intended to decrease privileges • Often used to increase privileges • Allows on demand escalation with no constraints • Sometimes results in sysadmin privileges

#2 Logins with IMPERSONATE privilege What are the attack requirements? • SQL injection or a direct connection with credentials to log into SQL Server • Login has privs to impersonate another login • For sysadmin, login must of have privs to impersonate a sysadmin or additional escalation path

Why is it a problem? #2 Manual Attack Find logins that can be impersonated

#2 Manual Attack Impersonate logins

#2 Automating the Attack New Tools Released • PowerShell ‒ Invoke-SqlServer-Escalate-ExecuteAs.psm1 • Metasploit ‒ mssql_escalate_execute_as.rb ‒ mssql_escalate_execute_as_sqli.rb

Why is it a problem? #2 Automating the Attack PowerShell

Why is it a problem? #2 Automating the Attack Metasploit

#2 Logins with IMPERSONATE privilege What’s the fix? • Don’t use the IMPERSONATE privilege to access external resources • Do consider using signed stored procedures as an alternative

#3 Database Users with Excess Privileges

#3 Database User Privileges What’s the issue? • Application logins used to connect to SQL Server are mapped to database users that can create stored procedures • Example = db_owner database role

#3 Database User Privileges Why is it a problem? • Database users can create stored procedures that EXECUTE AS OWNER • Sysadmins own a lot of application databases • So…database users can execute queries as sysadmins

#3 Database User Privileges What are the attack requirements? • SQL injection or a direct connection with credentials to log into SQL Server • To escalate to sysadmin ‒ Database user can create procedures ‒ Sysadmin owns the database ‒ Database is flagged as trustworthy

Why is it a problem? #3 Manual Attack Db_owner Example USE MyAppDb GO CREATE PROCEDURE sp_escalate_me WITH EXECUTE AS OWNER AS EXEC sp_addsrvrolemember 'MyAppUser','sysadmin' GO

Why is it a problem? #3 Manual Attack Db_owner Example USE MyAppDb GO CREATE PROCEDURE sp_escalate_me WITH EXECUTE AS OWNER AS EXEC sp_addsrvrolemember 'MyAppUser','sysadmin' GO SYSADMIN is the OWNER

#3 Automating the Attack New Tools Released • PowerShell ‒ Invoke-SqlServer-Escalate-Dbowner.psm1 • Metasploit ‒ mssql_escalate_dbowner.rb ‒ mssql_escalate_dbowner_sqli.rb

#3 Automating the Attack PowerShell

#3 Automating the Attack Metasploit

#3 Database User Privileges What’s the fix? • Don’t provide database users with privileges to create procedures • Don’t allow sysadmins to own application databases • Don’t flag databases as trustworthy (when possible)

#4 Procedures with SQL Injection

#4 Procedures with SQL Injection What’s the issue? • Stored procedures using dynamic SQL insecurely • Stored procedures configured to run as a login with excessive privileges

#4 Procedures with SQL Injection Why is it a problem? • Can be vulnerable to SQL injection • Can provide unauthorized data access • Can be used to escalate privileges in some cases

#4 Procedures with SQL Injection What are the attack requirements? • SQL injection or a direct connection with credentials to log into SQL Server • Dynamic SQL is used in the procedure • Concatenating strings

#4 Procedures with SQL Injection What are the attack requirements? • Privilege escalation requirements ‒ WITH EXECUTE AS OWNER • Database does have to be marked as trusted ‒ Signed with a certificate login • Database does NOT have to be marked as trusted

Find Signed Stored Procedures with Dynamic SQL #4 Manual Attack

Review Code #4 Manual Attack CREATE PROCEDURE sp_sqli2 @DbName varchar(max) AS BEGIN Declare @query as varchar(max) SET @query = 'SELECT name FROM master..sysdatabases where name like ''%'+ @DbName+'%'' OR name=''tempdb'''; EXECUTE(@query) END GO

Review Code #4 Manual Attack CREATE PROCEDURE sp_sqli2 @DbName varchar(max) AS BEGIN Declare @query as varchar(max) SET @query = 'SELECT name FROM master..sysdatabases where name like ''%'+ @DbName+'%'' OR name=''tempdb'''; EXECUTE(@query) END GO PURE EVIL

Inject Query to Execute OS Commands #4 Manual Attack EXEC MASTER.dbo.sp_sqli2 'master'';EXEC master..xp_cmdshell ''whoami''--';

Inject Query to Execute OS Commands #4 Manual Attack EXEC MASTER.dbo.sp_sqli2 'master'';EXEC master..xp_cmdshell ''whoami''--'; INJECTION

Review Code #4 Manual Attack CREATE PROCEDURE sp_sqli2 @DbName varchar(max) AS BEGIN Declare @query as varchar(max) SET @query = 'SELECT name FROM master..sysdatabases where name like ''%master';EXEC master..xp_cmdshell ''whoami''- -%'' OR name=''tempdb'''; EXECUTE(@query) END GO

Inject Query to Execute OS Commands #4 Manual Attack

#4 Automating the Attack New Tools Released • PowerShell ‒ Get-SqlServer-Escalate-SpSource.psm1

#4 Automating the Attack New Tools Released • PowerShell ‒ Get-SqlServer-Escalate-SpSource.psm1 Export Stored Procedures

#4 Automating the Attack New Tools Released • PowerShell ‒ Get-SqlServer-Escalate-SpSource.psm1 View Output

#4 Procedures with SQL Injection What’s the fix? • Do use parameterized queries • Don’t concatenate strings in evil ways • Don’t use EXECUTE AS OWNER to access external resources • Don’t flag databases are trustworthy

#4 Procedures with SQL Injection What’s the fix? • Do consider using signed procedures 1. Create certificate 2. Create login from certificate 3. Only assign required privileges to the certificate login 4. Sign procedures with certificate to provide access to required local and external resources

#4 Procedures with SQL Injection What’s the fix? -- Create procedure with sqli fix CREATE PROCEDURE sp_sqli_fix @DbName varchar(max) AS BEGIN SELECT name FROM master..sysdatabases WHERE name = 'tempdb' OR name = @DbName; END GO No EXECUTE AS OWNER No concatenating strings

#5 Public EXECUTE on Dangerous Procedures

#5 Execute on Dangerous Procedures What’s the issue? • Dangerous stored procedures and functions are available to the public server role by default

#5 Execute on Dangerous Procedures Why is it a problem? • Remember, public = all logins • Impact varies depending on procedure or function

#5 Execute on Dangerous Procedures Why is it a problem? • xp_regread - Read registry as service account • xp_dirtree - Capture/crack service account NetNTLMv2 password hashes (35 billion a sec) • SUSER_NAME - Enumerate SQL Server logins • SUSER_SNAME - Enumerate domain users

#5 Execute on Dangerous Procedures What are the attack requirements? • SQL injection or a direct connection with credentials to log into SQL Server

SUSER_SNAME Example: Get domain #5 Manual Attack Domain of SQL Server

SUSER_SNAME Example: Get Sample RID with SUSER_SID #5 Many Attack Full RID of Domain Admins group

SUSER_SNAME Example: Extract Domain SID #5 Manual Attack Grab the first 48 Bytes of the full RID RID = 0x0105000000000005150000009CC30DD479441EDEB31027D000020000 SID = 0x0105000000000005150000009CC30DD479441EDEB31027D0

SUSER_SNAME Example: Create new full RID #5 Manual Attack 1. Start with number, 500 2. Convert to hex, F401 3. Pad with 0 to 8 bytes, F4010000 4. Concatenate the SID and the new RID SID = 0x0105000000000005150000009CC30DD479441EDEB31027D0 RID = 0x0105000000000005150000009CC30DD479441EDEB31027D0F4010000

SUSER_SNAME Example: Enumerate Domain Account #5 Manual Attack 1. Start with number, 500 2. Convert to hex, F401 3. Pad with 0 to 8 bytes, F4010000 4. Concatenate the SID and the new RID SID = 0x0105000000000005150000009CC30DD479441EDEB31027D0 RID = 0x0105000000000005150000009CC30DD479441EDEB31027D0F4010000 Enumerated domain user

SUSER_SNAME Example: Enumerate All Domain Accounts, Groups, and Computers #5 Manual Attack 1. Increment number 2. Repeat 10,000 or more times

SUSER_SNAME Example: Network Takeover #5 Manual Attack 1. Dictionary attack 2. Escalate privileges locally 3. Escalate privileges on the domain

#5 Automating the Attack New Tools Released • PowerShell ‒ Get-SqlServer-Enum-SqlLogins.psm1 ‒ Get-SqlServer-Enum-WinAccounts.psm1 • Metasploit ‒ mssql_enum_sql_logins.rb ‒ mssql_enum_domain_accounts.rb ‒ mssql_enum_domain_accounts_sqli.rb

#5 Execute on Dangerous Procedures What’s the fix? • Do deny execute privileges on dangerous stored procedures and functions • Do use one of the many hardening guides available online or provided by Microsoft and others

#6 Service Accounts with Excessive Privileges

#6 Service Account Privileges What’s the issue? • SQL Server service (Windows) accounts configured with local or domain admin privileges • The same SQL Server service (Windows) account is often used to run multiple unrelated servers or “shared”

#6 Service Account Privileges Why is it a problem? • Shared SQL Server service accounts have inherit trust relationships, because the service account has sysadmin privileges

#6 Service Account Privileges Why is it a problem? • Sysadmins can impersonate the SQL Server service account ‒ xp_cmdshell ‒ agent options like cmdexec, PowerShell, and vbscript ‒ Custom stored procedure

#6 Service Account Privileges Why is it a problem? • Oh yeah, don’t forget Public logins can steal service account password hashes

#6 Service Account Privileges What are the attack requirements? • SQL injection or a direct connection with credentials to log into SQL Server • Service account is configured with local or domain admins privileges • xp_cmdshell, xp_dirtree, or xp_fileexists procedure can be used

#6 Manual Attack: Execute as Service Running OS commands as service account Service account is a local administrator

#6 Manual Attack: Shared Accounts InternetDMZIntranet DB2 Captain Evil DB1 Share Account + Xp_cmdshell+ osql -E

#6 Manual Attack: SMB Relay Captain Evil Server A Server B 1 2 3 4 5

#6 Service Account Privileges What is the fix? • Non-clustered servers ‒ Don’t run services as LocalSystem ‒ Don’t use local or domain accounts with local administrator privileges ‒ Do use virtual service accounts • Like a sandboxed NetworkService account

#6 Service Account Privileges What is the fix? • Clustered servers ‒ Do use domain accounts configured with least privilege ‒ Don’t use the same service account across servers that house unrelated applications

#7 Domain Users assigned Excessive Privileges

#7 Domain User Privileges What’s the issue? • SQL Server Express installed on a domain system gives ALL domain accounts CONNECT privileges (through privilege inheritance) ‒ It can then carry over during upgrades • Database administrators often provide all domain accounts with database access

#7 Domain User Privileges Windows Operating System SQL Server Express Database 1 Table 2 Table 2 BUILTINUsers NT AUTHORITYAuthenticated Users Domain Users (EVERYONE)

#7 Domain User Privileges Why is that a problem? • All domain accounts have unauthorized access to database servers • During network penetration tests it often leads to privilege escalation paths that end in Domain Admin

#7 Domain User Privileges What are the attack requirements? • A domain account • List of SQL Servers ‒ SPNs can be dumped from Active Directory No scanning required 

#7 Automating the Attack New Tools Released • PowerShell ‒ Get-SqlServer-Escalate-CheckAccess.psm1 Other Tools • Metasploit mssql_sql module

#7 Automating the Attack New Tools Released • PowerShell ‒ Get-SqlServer-Escalate-CheckAccess.psm1 PS C:Get-SqlServer-Escalate-CheckAccess -ShowSum | export-csv c:tempsql-server-excessive-privs.csv [*] ---------------------------------------------------------------------- [*] Start Time: 04/01/2014 10:00:00 [*] Domain: mydomain.com [*] DC: dc1.mydomain.com [*] Getting list of SQL Server instances from DC as mydomainmyuser... [*] 5 SQL Server instances found in LDAP. [*] Attempting to login into 5 SQL Server instances as mydomainmyuser... [*] ---------------------------------------------------------------------- [-] Failed - server1.mydomain.com is not responding to pings [-] Failed - server2.mydomain.com (192.168.1.102) is up, but authentication/query failed [+] SUCCESS! - server3.mydomain.com,1433 (192.168.1.103) - Sysadmin: No - SvcIsDA: No [+] SUCCESS! - server3.mydomain.comSQLEXPRESS (192.168.1.103) - Sysadmin: No - SvcIsDA: No [+] SUCCESS! - server4.mydomain.comAppData (192.168.1.104) - Sysadmin: Yes - SvcIsDA: Yes [*] ---------------------------------------------------------------------- [*] 3 of 5 SQL Server instances could be accessed. [*] End Time: 04/01/2014 10:02:00 [*] Total Time: 00:02:00 [*] ----------------------------------------------------------------------

#7 Automating the Attack New Tools Released • PowerShell ‒ Get-SqlServer-Escalate-CheckAccess.psm1

#7 Domain User Privileges What’s the fix? • Don’t provide the “Domain Users” group with privileges to log into any SQL Server • Do remove the default login associated with the “BUILTINUsers” group

#8 Database Link Chaining & Excessive Privileges

#8 Excessive Database Link Privileges What the issue? • Database links are being configured with excessive privileges • Database links can be crawled via OPENQUERY • xp_cmdshell can be used via OPENQUERY

#8 Excessive Database Link Privileges Why is that a problem? • Attackers can often gain sysadmin privileges by crawling database link chains • Move from low value database to high one • Take over Windows server via xp_dirtree or xp_cmdshell • Cached credentials can be recovered by admins

#8 Excessive Database Link Privileges What are the attack requirements? • SQL injection or a direct connection with credentials to log into SQL Server • One or more database links • Database links preconfigured with sysadmin privileges

#8 Excessive Database Link Privileges

#8 Excessive Database Link Privileges Penetration Test Stats • Database links exist (and can be crawled) in about 50% of environments we’ve seen • The max number of hops we’ve seen is 12 • The max number of server crawled is 226 • Usually executed through SQL injection

#8 Automating the Attack New Tools Released • PowerShell ‒ Get-MSSQLLinkPasswords.psm1 By Antti Rantasaari Old Tools Released • Metasploit ‒ mssql_linkcrawler.rb ‒ mssql_linkcrawler_sqli.rb

#8 Excessive Database Link Privileges What’s the fix? • Don’t use database links if you don’t need them • Do configure them with least privilege • Do configure them to inherit the privileges of the current login when possible

#9 Weak or Default Passwords What’s the issue? • Default sa account password • Default vendor account passwords • Weak passwords ‒ test:test ‒ sa:password ‒ Etc…

#9 Weak or Default Passwords Why is it a problem? • Attackers can quickly gain unauthorized access to servers and data • Tools for attack are everywhere ‒ Metasploit ‒ Hydra ‒ SQLPing3 ‒ Etc..

#9 Weak or Default Passwords What are the attack requirements? • List of SQL Servers ‒ Usually requires scanning

#9 Weak or Default Passwords What’s the fix? • Do set strong password policies ‒ They can be inherited from the domain • Do change default vendor passwords • Do disable the default sa account • Do enforce development environments

#10 No Transport Encryption What’s the issue? • By default, database communications are not encrypted

#10 No Transport Encryption What is it a problem? • Sensitive data can be exposed via MITM • SQL injection via MITM ‒ Can result in database and system compromise • Free tools available ‒Atticuss/SQLViking (Go see the talk!) ‒ Ettercap and fancy filters

#10 No Transport Encryption What are the attack requirements? • Man in the middle position or local admin on the client/server

#10 No Transport Encryption What’s the fix?? • Do enable SSL encryption http://support.microsoft.com/kb/316898

What can be done? Prevent Unauthorized Access • Enforce least privilege everywhere • Use secure impersonation methods • Parameterize queries in stored procedures Detect Attempted Attacks • Profiler (server access) • DML Triggers (data mods) • DDL Triggers (structure mods) • SQL Server Audit ( server/database level)

10 Deadly Sins of SQL Server Configuration - APPSEC CALIFORNIA 2015

More Related Content

Similar to 10 Deadly Sins of SQL Server Configuration - APPSEC CALIFORNIA 2015

More from Scott Sutherland

Recently uploaded

10 Deadly Sins of SQL Server Configuration - APPSEC CALIFORNIA 2015

Editor's Notes