Today Bob spoke the whole day about securing SQL Server, security itself, and encrypting

data. It was really hard content, and sometimes it was really hard to follow, because

of too much PowerPoint slides. I already miss Paul's whiteboard drawings… 😉 Here

are my study notes.

SQL Server Setup

• SQL Server VSS Writer
  • Needed for 3rd party backup products
• When you change the SQL Server service account password, you don't have to restart

  SQL Server

• When you change the service account through SQL Server Configuration Manager,

  the Configuration Manager will put the new user in the appropriate group

  • Permissions are granted on the group level, never on the user level
  • Service Master Key is also re-encrypted through the Configuration Manager
  • Registry permissions are also granted on the group level, instead of the account level
• NETWORK SERVICE exists only once on a computer
  • SQL Server would be shared with other applications, when they are also run under NETWORK

    SERVICE

• http://sqlskills.com/BLOGS/BOBB/post/About-SQL-Servers-usage-of-Service-SIDs.aspx
• Permissions are assigned to the Service SID of SQL Server
• Service SIDs can't be used for Clustering
• Clustering needs Domain User Accounts
• http://download.microsoft.com/download/1/2/A/12ABE102-4427-4335-B989-5DA579A4D29D/SQL_Server_2008_R2_Security_Best_Practice_Whitepaper.docx
• SQL Browser Service
  • Needed for dynamic port usage
  • http://www.mssqltips.com/tip.asp?tip=1946
  • http://www.bobpusateri.com/archive/2010/09/a-look-at-the-sql-server-browser-service/
• When SQL Server Agent Job is owned by sa
  • T-SQL steps are running as sysadmin
  • Non T-SQL steps are running as agent Service Account
    • You need the appropriate permission on the Agent Service Account, which is bad, when

      you need a l ot of different permissions

• When SQL Server Agent Job is NOT owned by sa
  • T-SQL steps
    • Agent logs into SQL Server
    • Uses EXECUTE AS WITH OWNER
  • Non SQL steps
    • Runs as SQL Agent Proxy
    • Create Credentials
      • The Credential gets the permissions from the configured identity
      • You have to change the credential when the password of the identity changes!
      • Credential needs the permission "Log on as a batch job" through secpol.msc
    • Create Proxy over Credential
      • Proxy is the security context for the SQL Server Agent Job Step
      • Proxy accesses the permissions from the configured identity, that is attached to the

        proxy account

• Kerberos needs a SPN (Service Principal Name) in the Active Directory
  • Format: MSSQLSvc/server1:1433

Authentication/Authorization

• Guest account can't be deleted, only deactivated
• sys.tables/principal_id shows the owner of the table
• sp_adduser should not be used any more
  • Use CREATE USER instead
  • sp_adduser creates a schema which is owned by the newly created user
• Application Roles
  • http://www.sqlservercentral.com/articles/Security/sqlserversecurityprosandconsofapplicationroles/1116/
  • http://support.microsoft.com/default.aspx?scid=kb;en-us;229564
• MIsmatched SIDs
  • sp_change_user_login
• TRUSTWORTHY property
  • Does the sysadmin trust the DBO?
• fn_my_permissions()
• sys.fn_builtin_permissions()
• Windows Groups can't have a default schema
  • A user can be in more than one group, which default schema from which group should

    be associated with the user?

  • The last added group (the login with the highest login_id) is used for default language/default

    database

• SQL Server executes stored procedures as CALLER
• Dynamic T-SQL breaks the Ownership-Chain!
• When the owner of the stored procedure is the same as the owner of the tables that

  are accessed, permissions are not checked

• The Owership Chain overrides a DENY
• Database Master Key is always encrypted with TRIPLE_DES
  • The same with the Service Master Key
• Service Master Keys are used to encrypt
  • Database Master Keys
  • Linked Server passwords
  • Credentials
• Service Master Key is encrypted by
  • DPAPI and Service Account
    • Needed for Cluster Failovers (the Service Account is on each node the same)
  • DPAPI and Local Machine Key
• Service Master Key is generated the first time, when SQL Server is started after

  installation

  • Therefore you can clone an installation
• You can regenerate the Service Master Key with the startup option "-K"
• Database Master Key can the optionally protected by the Service Master Key
  • You don't need a password for opening the Database Master Key
• When you backup a database (and therefore the Database Master Key) and restore

  it on another instance, you will loose the encryption by the Service Master Key

  • Therefore you have the option to open the Database Master Key through the provided

    password which is necessary

  • After you have opened the Database Master Key by password, you can add the encryption

    by the Service Master Key

• When you want to encrypt the Database Master Key by the Service Master Key, you have

  to supply the password that is used to encrypt the Database Master Key

• TempDb is also encrypted when you enable Transparent Database Encryption for a user

  database

Thanks for reading

-Klaus