SQL Server 2008 Security

Article
04/01/2009

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

Authentication: Who are you?
Two types: SQL Authentication / Windows Authentication
SQL Authentication – Built-in / Independent of the OS / login and password
Windows Authentication – Recommended in most cases
Modes: Windows Only or Mixed. Can be changed after installation
See https://msdn.microsoft.com/en-us/library/ms144284.aspx
Service accounts: look up requirements in books online
See https://msdn.microsoft.com/en-us/library/cc281953.aspx
Change service accounts using SQL Configuration Manager tool

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

CREATE LOGIN [domainuser] FROM WINDOWS
CREATE LOGIN [domaingroup] FROM WINDOWS
See https://msdn.microsoft.com/en-us/library/ms189751.aspx
Kerberos or NTLM

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

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

Client can’t connect to Server
Login is disabled
Passwords needs to be changed
Cant’s connect to domain
Token not enabled for delegation
Mismatched SID
Permission denied
See https://support.microsoft.com/kb/907272
See https://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx

Security context

Login token (Primary login SID, Windows group SIDs, server role membership IDs)
Login token (Primary user ID, Database role membership IDs)
SELECT * FROM sys.login_token
See https://msdn.microsoft.com/en-us/library/ms186740.aspx
SELECT * FROM sys.user_token – current database
See https://msdn.microsoft.com/en-us/library/ms188421.aspx
Use column – GRANT AND DENY vs. DENY-ONLY (user access control)
Token Caches
SELECT * FROM sys.dm_os_memory_cache_entries

Delegation or Impersonation

Discussion: double-hop issues
Impersonation: Use another user’s credentials to access resources
See https://msdn.microsoft.com/en-us/library/ms161965.aspx
Case – Web server trying to access database on behalf of the user
Delegation: System needs to be trusted for delegation to impersonate
Requirements: Login rights on both, AD properties, SPN registered, trusted for delegation
https://sqlcommunity.com/Blogs/tabid/70/EntryId/46/SPNs-What-They-Are-and-How-SQL-Server-2000-2005-Uses-Them-for-Kerberos-Authentication.aspx
SQL Server 2005 – SPN registration required, Kerberos only on TCP/IP connections
SQL Server 2008 – SPN registration not required, SPN in connection string, port not required in SPN
See https://msdn.microsoft.com/en-us/library/ms191153.aspx

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

Granting permissions: GRANT, DENY, REVOKE
See https://msdn.microsoft.com/en-us/library/ms188371.aspx
GRANT … WITH GRANT OPTION
Why do we need DENY? GRANT to a role, DENY to user
DENY: It’s an exception to a rule. Use with care.
Careful – Deny not honored in ownership chaining, DENY on column / GRANT on table.
Covering permissions. CONTROL on table gives you SELECT on table.
Covering permissions. SELECT on schema, gives you SELECT on table.
See https://msdn.microsoft.com/en-us/library/ms177450.aspx
Permission on parent scope: ALTER ANY CREDENTIAL, ALTER ANY EVENT NOTIFICATION
Public role: implicit server and database role, applies to all logins and users
Public role: equivalent to Everyone in Windows. By default is nothing.
See https://msdn.microsoft.com/en-us/library/ms175892.aspx
Guest: Built-in user, disable by default except in master and tempdb
Object ownership: Change with ALTER AUTHORIZATION ON … TO …
See https://msdn.microsoft.com/en-us/library/ms187359.aspx
Permission checks in applications: has_perms_by_name(…)
https://msdn.microsoft.com/en-us/library/ms189802.aspx
Lockdown SPs: Leave system stored procedures in place, avoid enabling xp_cmdshell

Special permissions

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

Network

Enable minimal required protocols and ports
TCP/IP - Default ports for SQL Server: 1433 and 1434
See https://msdn.microsoft.com/en-us/library/cc646023.aspx
Consider changing and blocking the default ports
Grant access to specific endpoint
Careful - Exposing to internet
SQL Server Browser Service
See https://msdn.microsoft.com/en-us/library/ms181087.aspx
Discussion: Browser Service vs. Aliases

Encryption over the wire

Login credentials encryption, Use SSL certificate
Consider option to ‘Force Protocol Encryption'
See https://msdn.microsoft.com/en-us/library/ms189067.aspx

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

Need to grant user access, but only while using an application
CREATE APPLICAITION ROLE … WITH PASSWORD…
See https://msdn.microsoft.com/en-us/library/ms181491.aspx
In the application, use sp_setapprole and provide the password
See https://msdn.microsoft.com/en-us/library/ms188908.aspx
Your regular user permissions are replaced with the application permissions
Password-based, contained in the database, cannot access server level metadata
Subject to password policy. Do not hard code in the application.
Can use sp_unsetapprole to revert (new in SQL Server 2005)
See https://msdn.microsoft.com/en-us/library/ms365415.aspx
Careful – Application needs to know the password

EXECUTE AS

Executes in the context of another use. Must have impersonate permission.
Provides controlled escalation of privileges. Not for sandboxing.
EXECUTE AS / CREATE PROCEDURE… WITH EXECUTE AS / BATCH
Server scope (EXECUTE AS LOGIN) or database scope (EXECUTE AS USER)
See https://msdn.microsoft.com/en-us/library/ms188332.aspx
Can use REVERT, is stackable (multiple levels)
See https://msdn.microsoft.com/en-us/library/ms178632.aspx
EXECUTE [AS CALLER] – default behavior, use caller’s context
ORIGINAL_LOGIN() - returns the non-impersonated context, good for auditing
See https://msdn.microsoft.com/en-us/library/ms189492.aspx
Example: Activation stored procedure for Service Broker
SETUSER – deprecated, not stackable, no revert
GRANT IMPERSONATE – very powerful permission
See https://msdn.microsoft.com/en-us/library/ms178640.aspx
Impersonation tokens – lives in the database, full user token.
See with SELECT * FROM sys.user_token
See https://msdn.microsoft.com/en-us/library/ms188421.aspx
Consider setting context on modules
See https://msdn.microsoft.com/en-us/library/ms191296.aspx

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

CREATE USER … FROM CERTIFICATE …
See https://msdn.microsoft.com/en-us/library/ms173463.aspx
Cannot be used for login
Cannot be directly impersonated using EXECUTE AS

Credentials

CREATE CREDENTIAL … WITH IDENTITY ….
See https://msdn.microsoft.com/en-us/library/ms189522.aspx
Need to store information to access resources outside SQL
Commonly a username and password
One credential can map to multiple logins
Mapped user CREATE/ALTER LOGIN
Check with SELECT * FROM sys.credentials
See https://msdn.microsoft.com/en-us/library/ms189745.aspx

Proxy Accounts

Allow subsystems to make user of credentials for external access
Must create the credential first
Fixes the scenario where the SQL Server Agent has high permissions
More fine grained control
See https://msdn.microsoft.com/en-us/library/ms190698.aspx

Module signing

Attaches a cryptographic signature to a module
Guarantees the source of code, code not tampered
Implicit impersonation - Certificate as an authentication
Principal is mapped – Certificate as a secondary identity
See https://msdn.microsoft.com/en-us/library/ms345102.aspx
ADD SIGNATURE TO … BY CERTIFICATE …
See https://msdn.microsoft.com/en-us/library/ms181700.aspx

SQL Server 2008 Security

Additional resources