SQL Server 2005 Security

1. SQL Server 2005 Security Date :13-3-2011 Palestinian Land Authority

2. These people can access your server or the SQL Server instance no matter what you do • anyone with physical access to the server. • domain admins of the network. • anyone with the sa password. • a windows group with local admin and/or sa rights (which implies group policy etc). Palestinian Land Authority

3. SQL Server 2005 permissions capabilities • Principalsare entities that can request SQL Server resources. • Windows-level principals Windows Domain Login Windows Local Login • SQL Server-level principal SQL Server Login • Database-level principals Database User Database Role Application Role • Securable :User, Table,Schema .. • Permissions: Alter,Update,Create, Backup ,….. Palestinian Land Authority

4. Notes: • Every SQL Server 2005 securable (USER, TABLE,..) has associated permissions (ALTER,UPDATE,CREATE, BACKUP, …..) that can be granted to a principal . • The owner of the server-level scope of permissions is the sysadmin that owns the instance of SQL Server. • The owner of database- level scope of permissions is thedbo. • The SQL Serversa login is a server-level principal. It is created by default when an instance is installed. the default database of sa is master. Palestinian Land Authority

5. FixedServer-Level Roles • sysadmin Members can perform any activity in the server. By default, all members of the Windows BUILTIN\Administrators group, the local administrator's group, sa ,are members of the sysadmin fixed server role. They have full permissions on all objects in all user and system databases. • securityadmin Members manage logins and their properties. They can GRANT, DENY, and REVOKE server-level permissions AND database-level permissions. Additionally, they can reset passwords for SQL Server logins. • serveradmin Members can change server-wide configuration options and shut down the server. • setupadminMembers can add and remove linked servers, and also execute some system stored procedures. • dbcreator Members can create databases, and can alter and restore their own databases. • bulkadmin Members can run the BULK INSERT statement. • diskadminis used for managing disk files. Palestinian Land Authority

6. Fixed Database-Level Roles • db_owner Members can perform all configuration and maintenance activities on the database. And they can drop a database. Only user accounts in the db_owner role can create objects owned by dbo. • db_securityadmin Members can modify role membership and manage permissions. Adding principals to this role could enable unintended privilege escalation. • db_accessadmin Members can add or remove access for Windows logins, Windows groups, and SQL Server logins. • db_denydatawriter Members cannot add, modify, or delete any data in the user tables within a database. • db_denydatareader Members cannot read any data in the user tables within a database. • db_ddladmin Members can run any Data Definition Language (DDL) command in a database. • db_datawriterMembers can add, delete, or change data in all user tables. • db_datareaderMembers can run a SELECT statement against any table or view in the database. • db_backupoperatorMembers can backup the database. Palestinian Land Authority

7. when you design security for your application, You must also consider the public role, the dbo user account, and the guest account. • The public Database Role • The public role is contained in every database, which includes system databases. It cannot be dropped and you cannot add or remove users from it. Permissions granted to the public role are inherited by all other users and roles because they belong to the public role by default. • the VIEW ANY DATABASE permission is granted to the public role. Therefore, by default, every user that connects to an instance of SQL Server 2005 can see all databases in the instance. • To limit visibility to database metadata, deny a login the VIEW ANY DATABASE permission. After this permission is denied, a login can see only metadata for master, tempdb, and databases that the login owns. • Every database user belongs to the public database role. When a user has not been granted or denied specific permissions on a securable, the user inherits the permissions granted to public on that securable. Palestinian Land Authority

8. Database Users • Logins must be mapped to database user accounts in order to work with database objects. Database users can then be added to database roles, inheriting any permission sets associated with those roles. • A database user is a principal at the database level. Every database user is a member of the public role. • After a user has been authenticated and allowed to log in to an instance of SQL Server, a separate user account must exist in each database the user has to access. Requiring a user account in each database prevents users from connecting to an instance of SQL Server and accessing all the databases on a server. Palestinian Land Authority

9. User Account Database Owner (dbo) • The dbo, is a user account that has implied permissions to perform all activities in the database. Members of the sysadmin fixed server role are automatically mapped to dbo. • The dbo user account is frequently confused with the db_owner fixed database role. The scope of db_owner is a database; the scope of sysadmin is the whole server. Membership in the db_owner role does not confer dbo user privileges. • The dbo user cannot be deleted and is always present in every database. • Only objects created by members of the sysadmin fixed server role (or by the dbo user) belong to dbo. • Objects created by any other user who is not also a member of the sysadmin fixed server role (including members of the db_owner fixed database role): Belong to the user creating the object, not dbo. Are qualified with the name of the user who created the object. Palestinian Land Authority

10. The guest User Account • The guest account is a built-in account in all versions of SQL Server. By default, it is disabled in new databases. If it is enabled, you can disable it by revoking its CONNECT permission by executing the Transact-SQL REVOKE CONNECT FROM GUEST statement. • A special user, guest, exists to permit access to a database for logins that are not mapped to a specific database user. Because any login can use the database through the guest user, it is suggested that the guest user not be enabled • INFORMATION_SCHEMA and sys Every database includes two entities that appear as users in catalog views: INFORMATION_SCHEMA and sys. These entities are required by SQL Server. They are not principals, and they cannot be modified or dropped. Palestinian Land Authority

11. SQL Server 2005 contains a new type of user, a user that is not mapped to a login. • Users that are not mapped to logins provide an alternative to using application roles. You can invoke selective impersonation by using the EXECUTE AS statement (and allow that user only the privileges needed to perform a specific task. • Using users without logins makes it easier to move the application to a new instance and limits the connectivity requirements for the function. You create a user without a login using DDL. Palestinian Land Authority

12. Permissions Types To perform any activity in a database, user must have the appropriate permissions. These permissions fall into three categories, which we call permissions types: • Object permissions ,Permissions to work with data and execute procedures . SELECT,INSERT,DELETE,UPDATE,EXECUTE ON SECURABLE LIKE ( tables, stored procedures and views,…) DRI (declarative referential integrity)Enables a user to add foreign key constraints on a table. • Statement permissions, Permissions to create a database or an item in the database BACKUP DATABASE ,BACKUP LOG ,CREATE DATABASE. CREATE DEFAULT,CREATE FUNCTION.CREATE PROCEDURE. CREATE RULE.CREATE TABLE,CREATE VIEW. . • Implied permissions, Permissions to utilize permissions granted to predefined roles . These are the permissions granted to the predefined roles (such as fixed server roles or fixed database roles) Palestinian Land Authority

13. Managing Permissions • You can use the GRANT, DENY, and REVOKE statements to give or take away permission from a user or role. • The GRANT statement is used to give permissions to a user or role. GRANT SELECT ON authors TO Alex • Use the WITH GRANT OPTION setting very carefully, because in this case users can grant permissions to the objects to other users and it will be more difficult to manage security. Palestinian Land Authority

14. Application Roles: • Application Roles offer a better, more secure way for users to gain access to SQL Server data. • In short, if each user has their own login, they can access SQL Server directly and potentially cause some damage. • This brings us to application roles. You create them and assign permissions to them just like regular database roles but you can't put users in them. • Here's how application roles work. You set up each user to have an account on the SQL Server with practically no rights. All they should be able to do is log in to the server and run a system stored procedure called sp_setapprole. This procedure accepts a couple parameters, including the name and password for the application role. Running sp_setapprole will immediately endow the user with all the permissions that you set up on the application role for the current session only. • So what does this mean for security? As long as the password for activating the application role is only known to the application, your users will not have any rights when they login to the SQL Server directly. In order to have the permissions they need, they will be required to use the application that knows the password and can unlock the permission for the application role. Now you can have SQL Server manage individual logins and still have a secure environment that uses the rules and filters in place within your applications. • Application roles restrict users to access data through a specific application only. Database permissions can be gained only by using specific applications and a user cannot logon directly to a database. Palestinian Land Authority

15. Encryption • Encrypting data requires secure encryption keys and key management. • A key management hierarchy is built into SQL Server 2005. Each instance of SQL Server has a built-in service master key that is generated at installation; specifically, the first time that SQL Server is started after installation. The service master key is encrypted by using both the SQL Server Service account key and also the machine key. Both encryptions use the DPAPI (Data Protection API). Palestinian Land Authority

16. Encryption is one of several defenses-in-depth that are available to the administrator who wants to secure an instance of SQL Server. • Strong encryption generally consumes more CPU resources than weak encryption. • Long keys generally yield stronger encryption than short keys. • Asymmetric encryption is stronger than symmetric encryption using the same key length, but it is relatively slow. • Block ciphers with long keys are stronger than stream ciphers. • Long, complex passwords are stronger than short passwords. • If you are encrypting lots of data, you should encrypt the data using a symmetric key, and encrypt the symmetric key with an asymmetric key. • Encrypted data cannot be compressed, but compressed data can be encrypted. If you use compression, you should compress data before encrypting it. Palestinian Land Authority

17. Best practices for data encryption • Encrypt high-value and sensitive data. • Use symmetric keys to encrypt data, and asymmetric keys or certificates to protect the symmetric keys. • Password-protect keys and remove master key encryption for the most secure configuration. • Always back up the service master key, database master keys, and certificates by using the key-specific DDL statements. • Always back up your database to back up your symmetric and asymmetric keys. Palestinian Land Authority

18. Asymmetric Keys • An asymmetric key is made up of a private key and the corresponding public key. Each key can decrypt data encrypted by the other. Asymmetric encryption and decryption are relatively resource-intensive, but they provide a higher level of security than symmetric encryption. An asymmetric key can be used to encrypt a symmetric key for storage in a database. • Asymmetric keys can be used to encrypt and decrypt data but ordinarily they are used to encrypt and decrypt symmetric keys. • the public key can be safely used for data encryption, since the ability to perform decryption is restricted exclusively to the holder of the corresponding private key. • encryption algorithms for Asymmetric RSA is algorithm with keys 512, 1024, or 2048 bits long. Palestinian Land Authority

19. Certificates Are Asymmetric encryption key pairs with additional metadata such as: • The public key of the subject. • The identifier information of the subject, such as the name and e-mail address. • The validity period. • Issuer identifier information. • The digital signature of the issuer. Two remaining questions that need further explanation are • how the public and private keys are distributed to their intended users • how recipients of a public key can be certain that what they have received truly represents the identity of the holder of the corresponding private key. The solution comes in the form of digital certificates. • A certificate is a digitally signed piece of software that associates a public key with the identity of the privatekey owner, assuring its authenticity. • The signature of the certificate is created using the same asymmetric algorithm. with a private key of the certificate issuer Palestinian Land Authority

20. Symmetric Keys • A symmetric key consists of a single key that is used for encryption and decryption. Symmetric keys are generally used for data encryption because they are orders of magnitude faster than asymmetric keys for encryption and decryption. • SQL Server 2005 allows administrators and developers to choose from encryption algorithms. • encryption algorithms, for symmetric. DES, Triple DES,RC2,128-bit AES,192-bit AES,256-bit. • More advanced algorithms exist (such as AES), AES can only be used with Windows Server 2003 or above (which includes Windows Vista) are not supported by operating systems like (Windows XP or Windows Server 2000) . • Triple DES, as used by SQL Server, is non-deterministic. It does not produce the same results even if the same text is encrypted again with the same key. • Symmetric keys created with ALGORITHM = TRIPLE_DES use TRIPLE DES with a 128-bit key. • Symmetric keys created with ALGORITHM = TRIPLE_DES_3KEY use TRIPLE DES with a 192-bit key. Palestinian Land Authority

21. Encryption • The key algorithm and key length choice should be predicated on the sensitivity of the data. • SQL Server encrypts data on a cell level—data is specifically encrypted before it is stored into a column value and each row can use a different encryption key for a specific column. • To use data encryption, a column must use the VARBINARY data type. The length of the column depends on the encryption algorithm used and the length of the data to be encrypted. • The KEY_GUID of the key that is used for encryption is stored with the column value. • Use data encryption only when it is required or for very high-value sensitive data. • In some cases, encrypting the network channel or using SQL Server permissions is a better choice because of the complexity involved in managing keys and invoking encryption/decryption routines. • SQL Server 2005 can use DDL to define certificates, asymmetric keys, and symmetric keys on a per-database basis Palestinian Land Authority

22. How to: Encrypt a Column of Data The following steps have to be taken to be able to work with encrypted data: • create a master key (there can be more than one), CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>' ; GO • create a certificate, if necessary, • create a symmetric or an asymmetric key (a symmetric key can be additionally encrypted with a certificate or an asymmetric key), • open the key, • once the key has been opened, it is possible to work with encrypted data, • close the key. Palestinian Land Authority

23. USE DataBaseName; GO --If there is no master key, create one now. IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101) CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJKL969#ghf0%94467GRkjg5k3fd117r$$#1946kcj$n44nhdlj‘ GO CREATE CERTIFICATE CertificateName WITH SUBJECT = 'Employee Social Security Numbers'; GO CREATE SYMMETRIC KEY SSN_Key_01 WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE CertificateName; GO USE [DataBaseName]; GO ALTER TABLE HumanResources.Employee ADD EncryptedNationalIDNumber varbinary(128); GO OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE CertificateName; UPDATE HumanResources.Employee SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber); GO -- TO READ ENCRIPTION VALUE OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE CertificateName; GO SELECT NationalIDNumber, EncryptedNationalIDNumber AS 'Encrypted ID Number', CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber)) AS 'Decrypted ID Number' FROM HumanResources.Employee; GO Palestinian Land Authority

24. The new functions have been created to make data encryption possible in custom applications: • EncryptByKey. • DecryptByKey. • EncryptByPassPhrase. • DecryptByPassPhrase. • Key_ID :Returns the ID of a symmetric key in the current database. • Key_GUID: Returns the GUID of a symmetric key in the database. Palestinian Land Authority

25. EncryptByPassPhrase/DecryptByPassPhrase create table login_details(uid integer,username varchar(10),password varbinary(100)) insert into login_details(uid,username,password) values(1,'smith',EncryptByPassPhrase('12',’XXX’)) insert into login_details(uid,username,password) values(2,'kennal',EncryptByPassPhrase('12','YYY')) insert into login_details(uid,username,password) values(3,'staurt',EncryptByPassPhrase('12','ZZZ'))  select * from login_details select uid,username, DECRYPTBYPASSPHRASE ('12',password) as Password from login_details   In the above result the password still in VarBinary.So we have to Convert the VarBianry in Varchar by using Convert function as follows.  select uid,username,convert(varchar(10), DECRYPTBYPASSPHRASE ('12',password)) from login_details These new functoins use a "pass-phrase" (i.e. a medium to long sentence), which is use to generate a symmetric key at run-time, to be used for encryption an decryption Palestinian Land Authority

26. service master key • The service master key is the root of the encryption hierarchy in SQL Server. It should be backed up and stored in a secure, off-site location. Creating this backup should be one of the first administrative actions performed on the server. BACKUP SERVICE MASTER KEY TO FILE = '<complete path and filename>' ENCRYPTION BY PASSWORD = '<password>' ; GO Palestinian Land Authority

27. SQL Server 2005 Security Best Practices Palestinian Land Authority

28. surface area reduction Best practices for surface area reduction • Install only those components that you will immediately use. Additional components can always be installed as needed. • Enable only the optional features that you will immediately use. • Turn off unneeded services by setting the service to either Manual startup or Disabled. Palestinian Land Authority

29. Service Account Selection and Management When you select a Windows account to be a SQL Server service account, you have a choice of: • Domain user that is not a Windows administrator. • Local user that is not a Windows administrator. • Network Service account. • Local System account. • Local user that is a Windows administrator. • Domain user that is a Windows administrator. • When choosing service accounts, consider the principle of least privilege. • Always use SQL Server Configuration Manager to change service accounts. Palestinian Land Authority

30. Authentication Mode SQL Server has two authentication modes: • Windows Authentication mode . Windows accounts use a series of encrypted messages to authenticate to SQL Server,no passwords are passed across the network during the authentication process. • Mixed Mode Authentication: both Windows accounts and SQL Server-specific accounts (known as SQL logins) are permitted. When SQL logins are used, SQL login passwords are passed across the network for authentication. This makes SQL logins less secure than Windows logins. Best practices for authentication mode • Always use Windows Authentication mode if possible. • Use Mixed Mode Authentication only for legacy applications and non-Windows users. • Use the standard login DDL statements instead of the compatibility system procedures. • Change the sa account password to a known value if you might ever need to use it. • Always use a strong password for the sa account and change the sa account password periodically. • Do not manage SQL Server by using the sa login account; assign sysadmin privilege to a knows user or group. • Rename the sa account to a different account name to prevent attacks on the sa account by name. Palestinian Land Authority

31. Network Connectivity SQL Server 2005 can use an encrypted channel for two reasons: • to encrypt credentials for SQL logins, The reason for using SSL is to encrypt credentials during the login process for SQL logins when a password is passed across the network. If an SSL certificate is installed in a SQL Server instance, that certificate is used for credential encryption. If an SSL certificate is not installed, SQL Server 2005 can generate a self-signed certificate and use this certificate instead. Using the self-signed certificate prevents passive man-in-the-middle attacks, in which the man-in-the-middle intercepts network traffic, but does not provide mutual authentication. Using an SSL certificate with a trusted root certificate authority prevents active man-in-the-middle attacks and provides mutual authentication. • to provide end-to-end encryption of entire sessions Using encrypted sessions requires using a client API that supports these. The OLE DB, ODBC, and ADO.NET clients all support encrypted sessions; currently the Microsoft JDBC client does not. Palestinian Land Authority

32. Best practices for network connectivity • Limit the network protocols supported. • Do not enable network protocols unless they are needed. • Do not expose a server that is running SQL Server to the public Internet. • Configure named instances of SQL Server to use specific port assignments for TCP/IP rather than dynamic ports. • If you must support SQL logins, install an SSL certificate from a trusted certificate authority rather than using SQL Server 2005 self-signed certificates. • Use "allow only encrypted connections" only if needed for end-to-end encryption of sensitive sessions. Palestinian Land Authority

33. Lockdown of System Stored Procedures • SQL Server uses system stored procedures to accomplish some administrative tasks. • These procedures almost always begin with the prefix xp_ or sp_. • system procedures remain the only way to accomplish tasks such as sending mail or invoking COM components. • Because some system procedures interact with the operating system or execute code outside of the normal SQL Server permissions, they can constitute a security risk. System stored procedures such as xp_cmdshell or sp_send_dbmail are off by default and should remain disabled unless there is a reason to use them. • The system stored procedures should not be dropped from the database; dropping these can cause problems when applying service packs Palestinian Land Authority

34. Best practices for system stored procedures • Disable xp_cmdshell unless it is absolutely needed. xp_cmdshell - executes a command in the underlying operating system • Disable COM components once all COM components have been converted to SQLCLR. • Disable both mail procedures (Database Mail and SQL Mail) unless you need to send mail from SQL Server. Prefer Database Mail as soon as you can convert to it. • Use SQL Server Surface Area Configuration to enforce a standard policy for extended procedure usage. • Do not remove the system stored procedures by dropping them. • Do not DENY all users/administrators access to the extended procedures Palestinian Land Authority

35. Administrator Privileges • Minimizing the number of administrators who have sysadmin or CONTROL SERVER privilege. • The permission VIEW SERVER STATE is useful for allowing administrators and troubleshooters to view server information (dynamic management views) without granting full sysadmin or CONTROL SERVER permission. • Use administrator privileges only when needed. • Minimize the number of administrators. • Provision admin principals explicitly. • Have multiple distinct administrators if more than one is needed. • Avoid dependency on the builtin\administrators Windows group. Palestinian Land Authority

36. Database Ownership and Trust Best practices for database ownership and trust • Have distinct owners for databases; not all databases should be owned by sa. • Minimize the number of owners for each database. Palestinian Land Authority

37. Password Policy • Enforce Password History • Minimum and Maximum Password Age • Minimum Password Length • Password Must Meet Complexity Requirements • Account Lockout policies include: • Account Lockout Threshold (Number of invalid logins before lockout) • Account Lockout Duration (Amount of time locked out) • Reset Lockout Counter After n Minutes • run SQL Server 2005 on a Windows Server 2003 or later operating system. that supports NetValidatePasswordPolicy . Best practices for password policy • Mandate a strong password policy, including an expiration and a complexity policy for your organization. • If you must use SQL logins, ensure that SQL Server 2005 runs on the Windows Server 2003 operating system and use password policies. • Outfit your applications with a mechanism to change SQL login passwords. • Set MUST_CHANGE for new logins. Palestinian Land Authority

38. Schemas • think of schemas as containers to organize objects. • The owner of a schema can be a user, a database role, or an application role. • Having schemas that are role-based does not mean that it’s a good practice to have every user be a schema owner. Only users who need to create database objects should be permitted to do so . • The ability to create objects does not imply schema ownership; GRANTing Bob ALTER SCHEMA permission in the payroll_app schema can be accomplished without making Bob a schema owner. • Objects created in a schema are owned by the schema owner by default, not by the creator of the object. This makes it possible for a user to create tables in a known schema without the administrative problems that ensue when that user leaves the company or switches job assignments. Palestinian Land Authority

39. Best practices for using schemas • Group like objects together into the same schema. • Manage database object security by using ownership and permissions at the schema level. • Have distinct owners for schemas. • Not all schemas should be owned by dbo. • Minimize the number of owners for each schema. • If you do not wish to organize your database objects into schemas, the dbo schema is available. Palestinian Land Authority

40. Authorization • In SQL Server, authorization is accomplished via Data Access Language (DAL) rather than DDL or DML . • GRANT and REVOKE, SQL Server also contains a DENY DAL verb. DENY differs from REVOKE when a user is a member of more than one database principal . • The DAL statement that grants access to all securables in the payroll schema is: GRANT SELECT ON schema::payroll TO fred • A best practice for authorization is to encapsulate access through modules such as stored procedures and user-defined functions An example of this technique would be permitting access to the employee pay rate table only through a stored procedure "UpdatePayRate." Users that need to update pay rates would be granted EXECUTE access to the procedure, rather than UPDATE access to the table itself Palestinian Land Authority

41. Best practices for database object authorization • Encapsulate access within modules. • Manage permissions via database roles or Windows groups. • Use permission granularity to implement the principle of least privilege. • Do not enable guest access. • Use users without logins instead of application roles Palestinian Land Authority

42. Remote Data Source Execution There are two ways that procedural code can be executed on a remote instance of SQL Server: • configuring a linked server definition with the remote SQL Server . • configuring a remote server definition for it. • Linked servers allow more granular security than remote servers. Ad hoc queries through linked servers (OPENROWSET and OPENDATASOURCE) are disabled by default in a newly installed instance of SQL Server 2005. • Best practices for remote data source execution • Phase out any remote server definitions. • Replace remote servers with linked servers. • Leave ad hoc queries through linked servers disabled unless they are absolutely needed. Palestinian Land Authority

43. Thank you Palestinian Land Authority