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