Compartir a través de

SQL Server 2008 Security

  • Artículo

Here are some notes on “SQL Server 2008 Security” I took while attending an advanced class on SQL Server taught by Greg Low (from https://sqlblog.com/blogs/greg_low/ and https://www.sqldownunder.com/).

Please note that, although these notes were taken during the class, I might have added some of my own (mis)interpretation :-). Always check your facts on Books Online (I try to provide links when applicable). As with anything else you get from a blog, never use any of this in production before you thoroughly validate it a test environment. Please keep in mind that some of those will be hard to follow without some pre-requisite knowledge and the right context. Reading the post from top to bottom will help.

Authentication

SQL Authentication

  • CREATE LOGIN … WITH PASSWORD = …
  • See https://msdn.microsoft.com/en-us/library/ms189751.aspx
  • Login handshake encrypted with SLL (uses certificate)
  • Careful – Downlevel clients will fall back to non-encrypted. You can set to require.
  • SQL Authentication is not un-secure
  • When to consider: No windows users, non-Windows clients, double-hop issues, app logins
  • Enforces account polity (lockout, password changes, complexity, history, change password on login)
  • Polices for login: requires Windows Server 2003 or later, obtains policy from domain
  • Policies also apply to app roles and other items
  • Leave CHECK_POLICY ON, set CHECK EXPIRATION ON, set MUST_CHANGE for new logins
  • See https://msdn.microsoft.com/en-us/library/ms161959.aspx

Windows Authentication

Schemas

  • Schemas: Grouping related objects together, can grant permissions by schema
  • Introduced in SQL Server 2005
  • Securable scope, schema has an owner
  • ALTER AUTHORIZATION ON SCHEMA - Useful when going from old dbo… to new schema
  • Discussion: Ownership chaining – Pros and cons
  • SQL Server Best Practices: Implementation of Database Object Schemas
  • See https://msdn.microsoft.com/en-us/library/dd283095.aspx

Additional settings

  • Applications that absolutely require relaxing security: give them their own instance
  • Admin privileges: only when needed, avoid dependency on builtinadministrators
  • DB ownership and trust: Distinct owners for databases
  • Leave Cross-database ownership chaining (CDOC) setting off
  • See https://msdn.microsoft.com/en-us/library/ms188694.aspx

Trustworthy databases

  • ALTER DATABASE … SET TRUSTWORTHY ON/OFF
  • Per database, only sysadmin can set
  • See https://msdn.microsoft.com/en-us/library/ms187861.aspx
  • Avoid turning it on, for cross-DB scenarios assign a low-privileged dbo
  • Careful – If TRUSTWORTHY ON and dbo is a sysadmin = full access
  • Cross-database authentication: Trusted servers
  • Cross-database authentication: Certificates

Endpoints

  • Exposed services: TSQL itself, SOAP (deprecated), Service Broker, Database Mirroring
  • Both TCP and HTTP work as a transport (HTTP is deprecated)
  • Some endpoints are created by default
  • See https://msdn.microsoft.com/en-us/library/ms191220.aspx 
  • Default endpoints: DAC, TSQL (shared memory), TSQL (named pipes), TSQL (TCP), TSQL(VIA)
  • Check with SELECT * FROM sys.endpoints
  • See https://msdn.microsoft.com/en-us/library/ms189746.aspx
  • SQL Server 2005 – Kerberos only on TCP connections
  • SQL Server 2008 – Kerberos available on TCP, named pipes and shared memory
  • CONNECT permission. Connection to TSQL is granted to public by default.
  • See https://msdn.microsoft.com/en-us/library/ms187811.aspx 
  • Surface area configuration (SAC) – not in SQL Server 2008 – Set options via sp_configure
  • For services and connections: use SQL Server Configuration Manager

Linked Servers

  • Make remote data access work similar to local data
  • Keeps information about servers, connection credentials
  • Enabled with sp_addlinkedserver
  • See https://msdn.microsoft.com/en-us/library/ms190479.aspx
  • You can use four-part name server without worrying about connection info
  • Consider using OPENQUERY instead
  • See https://msdn.microsoft.com/en-us/library/ms188427.aspx 
  • Authentication: Windows login and delegation, SQL logins
  • Careful – Do not combine with cross database ownership chaining (CDOC)
  • Careful - Collation compatibility is important
  • Note – Linked Servers replace the old remote servers
  • Note – You can use linked servers to many sources, including Excel
  • Careful – Provider availability and reliability
  • Careful – Results sets in MemtoLeave area, trouble if too large
  • See https://support.microsoft.com/kb/271624 
  • Consider using certificates: if servers need to communicate, not in the same domain

Login

  • Encrypting connections – SSL (login handshake encrypted, can be forced)
  • Resolving certificate problems
  • Auditing (default server trace, errorlog, windows event log, can audit login success as well)
  • Password changes – Users can change themselves, apps need to handle the specific error
  • Password reset – ALTER ANY LOGIN, CONTROL SERVER required

Login failures

Security context

Delegation or Impersonation

Demo

  • CREATE DATABASE ...
  • CREATE TABLE testtable …
  • CREATE LOGIN tesuser WITH PASSWORD =’…’, CHECK_POLICY=OFF
  • CREATE USER testuser FROM LOGIN testuser
  • GRANT SELECT ON testtable TO testuser
  • Connect to server using SSMS, SELECT - All is OK
  • sp_detach_db …
  • DROP  USER
  • CREATE DATABASE … FOR ATTACH
  • Connect to server using SSMS, SELECT, user not able to access
  • CREATE USER testuser … FOR LOGIN testuser - already exists
  • SELECT * FROM sys.server_principals
  • SELECT * FROM sys.database_principals
  • Problem – SID mismatch
  • Use sp_change_users_login or ALTER USER – fixes the problem replacing the SID
  • See https://support.microsoft.com/kb/240872
  • Consider specifying a SID when creating the login

Authorization

  • Securable objects (classes): what we can secure
  • Principals: who we grant permissions to, Server principals – Logins, Database principals – Users
  • Permissions: what we assign to principals to control access
  • Wide variety of permission depending on securable classes.
  • Much more fine grained in SQL Server 2005
  • For instance: VIEW DEFINITION permissions
  • sys.server_principals, sys.database_principals, sys.securable_classes, sys.fn_builtin_permissions()
  • sys.server_permissions, sys.database_permissions, fn_my_permissions()
  • See https://msdn.microsoft.com/en-us/library/ms191291.aspx

Managing permission

Special permissions

  • CREATE DATABASE – allows attaching database
  • SecurityAdmin – Creates logins, grants permission
  • ALTER ANY LOGIN – can reset passwords
  • DBO – Can restore database

Network

Encryption over the wire

Login issues

  • Can’t login due to “not associated with a trusted connection” - SQL authentication while not in mixed mode
  • Kerberos issues with time out of sync between servers
  • Bad username/password – sa with no password, weak passwords, plain text config files, etc…
  • Temporary account – stay on for years... Set expiration date!
  • Service account – avoid “local system” – SQL Server 2008 helps
  • SQL Injection attacks – Most can be avoided in the code (use parameters), but there’s a lot of old code…
  • SQL Injection attacks – Don’t trust anything that comes from an input

Application Role

EXECUTE AS

Users without logins

  • CREATE USER … WITHOUT LOGIN
  • See https://msdn.microsoft.com/en-us/library/ms173463.aspx
  • It’s like a container for permissions. You can EXECUTE AS, but not really login.
  • No access to outside database. Requires IMPERSONATION rights.
  • Better option than application roles.

Auxiliary principals

Credentials

Proxy Accounts

Module signing

Related blog posts:

Comments