Overview
The managed Microsoft SQL Server product is a managed system that includes the installation and configuration of a managed OS with managed MS SQL DBMS Database Management System on top. Configurations on OS and DBMS are made according to Microsoft Best Practices. It uses the same basic blueprint described in the MS SQL overview.
Preconditions and basic requirements
The user has to have the needed entitlements to request the blueprint via the catalog or the API.
OS configurations and options
The product has the following functions included:
Malware Protection
The OS is protected by a malware protection agent. More info about malware protection.
Patching
The user can select from a service provider predefined set of monthly patching windows in which the OS will be automatically patched.
Permissions at OS level
Permissions at the OS level are reserved for Swisscom. RDP connections are not allowed.
Connections to the SQL Server can be established through SQL Server Management Studio (SSMS), SQL Server Data Tools (SSDT) or Visualstudio (VS) from a remote host.
AD Integration
None yet
SQL configurations and options
SQL Services
| Service | Startup | Service Account |
|---|---|---|
| SQL Server (MSSQLServer) | Automatic | Local System |
| SQL Server Agent (MSSQLServer) | Automatic | Local System |
SQL Server configurations
| Setting | Configuration |
|---|---|
| Max Degree of Parallelism | Equal no. of CPU, max. 8 |
SQL permissions
The aim of the permission model is to allow customers to managed their databases with a high degree of autonomy. The approach to achieve this goal is to separate the DBMS, which is managed by Swisscom, and the DB management as far as possible.
Overview
We differentiate between three levels of permissions. The table summarize the permission model at high level for a first understanding.
| Level | Description |
|---|---|
| SQL Instance (DBMS) | Restrictive permissions are applied at this level as SCS will mainly provide the management at DBMS level. |
| Databases | The customer can create and drop DB's, as well he managed the access to databases assigning permissions according to his business requirements. This means that the customer is responsible for his data and who is going to access it. |
| Agent (Job Scheduling) | Capability to create and manage own jobs is provided. |
A full segregation of permission cannot be implemented as the above mentioned three level are interconnected (SQL server security design). The customer should be able to change a defined set of DBMS settings if needed. This is achieved through a predefined set of configurable options made available on the portal and/or through API.
Beside portal/API operations the customer will connect to the DBMS through SSMS and/or SSDT to execute DB and permission actions.
SQL instance permission level
The sysadmin server role is reserved to SCS. The default sa user is disabled. For SCS admin tasks a SQL login named scs_sa is created and member of the sysadmin server role. This user is reserved to SCS.
By a SQL managed service order the customer will be required to define a login name along with a password. This login will be defined as a "customer DBA". A login with enhanced permissions, which will be described below. Basically this user can manage databases, access and own jobs on the SQL instance. Therefore we recommend to assign this role to administrators only.
At instance level the following server-level roles are not available and cannot be assigned:
- dbcreator
- diskadmin
- securityadmin
- serveradmin
- sysadmin
These roles are available and can be assigned through the customer DBA to any login.
- bulkadmin
- setupadmin
The "customer DBA" capabilities
On every managed instance the server role customerAdmin will be configured. The "customer DBA" mentioned above will be member of the customerAdmin role.
The customer has the rights to add other logins to this server role, i.e. to enroll other users as "customer DBA".
The customerAdmin is member of the followings role
- Member of the processadmin server role in order to open the activity monitor. This permission enable the "customer DBA" to kill processes.
- Member of the SQLAgentUserRole and SQLAgentReaderRole in order to create and manage job.
and has been granted with the following instance level rights:
| Server Role | Permission | State |
|---|---|---|
| customerAdmin | ALTER ANY SERVER AUDIT | DENY |
| customerAdmin | ALTER ANY AVAILABILITY GROUP | DENY |
| customerAdmin | ALTER ANY CREDENTIAL | DENY |
| customerAdmin | ALTER ANY EVENT NOTIFICATION | DENY |
| customerAdmin | ALTER ANY LOGIN | GRANT_WITH_GRANT_OPTION |
| customerAdmin | ALTER ANY LINKED SERVER | GRANT_WITH_GRANT_OPTION |
| customerAdmin | ALTER RESOURCES | DENY |
| customerAdmin | ALTER SERVER STATE | GRANT_WITH_GRANT_OPTION |
| customerAdmin | ALTER SETTINGS | DENY |
| customerAdmin | ALTER TRACE | GRANT_WITH_GRANT_OPTION |
| customerAdmin | AUTHENTICATE SERVER | DENY |
| customerAdmin | CREATE AVAILABILITY GROUP | DENY |
| customerAdmin | CREATE ANY DATABASE | GRANT_WITH_GRANT_OPTION |
| customerAdmin | CREATE DDL EVENT NOTIFICATION | DENY |
| customerAdmin | CREATE ENDPOINT | DENY |
| customerAdmin | CREATE TRACE EVENT NOTIFICATION | DENY |
| customerAdmin | SHUTDOWN | DENY |
| customerAdmin | VIEW ANY DEFINITION | GRANT_WITH_GRANT_OPTION |
| customerAdmin | VIEW ANY DATABASE | GRANT_WITH_GRANT_OPTION |
| customerAdmin | VIEW SERVER STATE | GRANT_WITH_GRANT_OPTION |
| customerAdmin | EXTERNAL ACCESS ASSEMBLY | DENY |
| customerAdmin | UNSAFE ASSEMBLY | DENY |
| customerAdmin | ALTER ANY EVENT SESSION | GRANT |
The following table summarize the most important capabilities of user who are granted membership to the customerAdmin role
| Capability | How to |
|---|---|
| Create DB | By statement, SSMS/SSDT or Exec scs_AdminDB.customer.sp_createNewDB |
| Delete DB | By statement or SSMS/SSDT |
| Import DB from bacpac file | SSMS/SSDT |
| Import DB from dacpac file | SSDT |
| Export DB to bacpac files | SSMS/SSDT |
| Export DB to dacpac files | SSMS/SSDT |
| Create login | By statement or SSMS/SSDT |
| Delete login | By statement or SSMS/SSDT |
| Grant membership to bulkadmin and setupadmin roles | Exec scs_AdminDB.customer.sp_grantSrvRole |
| Revoke membership to bulkadmin and setupadmin roles | Exec scs_AdminDB.customer.sp_revokeSrvRole |
| Grant membership to the SQLAgentUserRole | Exec scs_AdminDB.customer.sp_grantAgentJob |
| Revoke membership to the SQLAgentUserRole | Exec scs_AdminDB.customer.sp_revokeAgentJob |
| Set trace flags 1204 and 1222 | Exec scs_AdminDB.customer.sp_setTraceFlag |
| Disable trace flags 1204 and 1222 | Exec scs_AdminDB.customer.sp_disableTraceFlag |
| Get trace flags status | Exec scs_AdminDB.customer.sp_statusTraceFlag |
| Grant the customer admin rights to other logins | Exec scs_AdminDB.customer.sp_createAdmin |
| Revoke customer admin rights for logins | Exec scs_AdminDB.customer.sp_dropAdmin |
| Read SQL Server Error Log or SQL Agent Error Log | Exec scs_AdminDB.customer.sp_viewSqlLog |
| List DB size | Exec scs_AdminDB.customer.sp_Collect_DB_Space_Stats |
| List DB files details | Select scs_AdminDB.customer.VW_DisplayDbFileInformation |
| List member of server roles | Select scs_AdminDB.customer.VW_DisplayDbRolesMembers |
| List user granular permissions on DB | Select scs_AdminDB.customer.VW_DisplayDbUserPermissions |
| List DB users | Select scs_AdminDB.customer.VW_DisplayDbUsers |
| Create jobs | SSMS or statement |
| Delete jobs | SSMS or statement |
| Modify jobs | SSMS or statement |
| Create Extended Events Session | SSMS or statement |
| Delete Extended Events Session | SSMS or statement |
Server settings configuration
At SQL instance level the customer is not allowed to change directly any settings (i.e. through SSMS).
The options listed below can be configured via Portal. We advice the customer to follow Microsoft recommendations configuring the setting. The links refer to the last SQL version, make sure that the advices apply to older versions also.
Note: some changes can heavily affect the server performance or generate unwanted behaviour.
| Name | Configured value | Microsoft usage description | Remarks |
|---|---|---|---|
| Ad Hoc Distributed Queries | 0 | https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/ad-hoc-distributed-queries-server-configuration-option | |
| clr enabled | 0 | SQL 2017 & newer and safe mode only https://docs.microsoft.com/en-us/sql/relational-databases/clr-integration/clr-integration-enabling?view=sql-server-2017 | |
| cost threshold for parallelism | 5 | https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-cost-threshold-for-parallelism-server-configuration-option | |
| cross db ownership chaining | 0 | https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/enabling-cross-database-access-in-sql-server | |
| cursor threshold | -1 | https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-cursor-threshold-server-configuration-option | |
| fill factor (%) | 0 | https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-fill-factor-server-configuration-option | |
| in-doubt xact resolution | 0 | https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/in-doubt-xact-resolution-server-configuration-option | |
| max degree of parallelism | 0 | https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option | |
| Max value configurable | 8 | ||
| max full-text crawl range | 4 | https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/max-full-text-crawl-range-server-configuration-option | |
| max worker threads | 0 | https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-worker-threads-server-configuration-option | |
| nested triggers | 1 | https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-nested-triggers-server-configuration-option | |
| network packet size (B) | 4096 | https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-network-packet-size-server-configuration-option | |
| Ole Automation Procedures | 0 | https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/ole-automation-procedures-server-configuration-option | |
| PH timeout (s) | 60 | https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/ph-timeout-server-configuration-option | |
| remote data archive | 0 | https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-remote-data-archive-server-configuration-option | |
| transform noise words | 0 | https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/transform-noise-words-server-configuration-option |
The customer DBA is granted membership to the SQLAgentReaderRole group, which enable the user to see all jobs on the server, however to edit the own jobs only (refer to https://technet.microsoft.com/en-us/library/ms188283(v=sql.110).aspx).
Furthermore the customer DBA can assign other logins in the SQLAgentUserRole group.
Please note that the user should be granted access to the database objects used in the jobs.
The following actions are not supported:
- replication jobs
- cmd batch (xp_cmdshell)
- command-line scripts by using ActiveX
- PowerShell
Manually start, stop, or restart SQL Server Agent can be done through the portal only.
Email notifications through SQL Server Agent are not available.
SQL database level permissions
The customer DBA is added automatically as db_owner on each DB created on the instance and it will remain it until it is explicitly removed from a different customer DBA or member of the db_owner role.
The scs_sa login will be set as database owner for each DB created on the server and cannot be changed.
If the DB is created through the provided stored procedure an additional user called dbo_dbname will be automatically added as db_owner.
The customer DBA will be able to manage access to the DB's.
For information about the SQL built-in database roles please refer to the Microsoft documentation.
NB Membership to the customerAdmin server role or db_owner gives the capability to backup databases. A backup without hint "copy only" will break the backup chain and therefore it can affect a restore point in time (as far the DB is in full recovery mode).
Password Policy for "customer DBA" and dbo
Swisscom imposes a minimum acceptable level of complexity against customer's created passwords through the ESC portal UI or APIs such as:
Passwords must have enough characters (minimum 8 chars and max 99) in length and
- Passwords must include characters like numerical digits, upper case letters, lower case letters, special characters
- Passwords must not be identical to the user ID and must not contain the user ID
- Passwords must not be easy to guess and should be as random as possible
- Passwords must not consist of easy-to-guess sequences of characters (e.g. “12345678”, “abcdefgh”, “aaaabbbb”, “qwertzui”)
- Passwords must not contain the following special characters: {"&" ," " , "/" , ";" , '"'}