Configuration of MSSQL-based assets
This guide describes how to configure assets whose data is read by a MSSQL instance. MSSQL instances are accessed via Powershell. Asset-specific steps are marked accordingly.
Requirements
To access MSSQL instances via powershell, the hosts running the MSSQL instances must be configured as described in Configuring Windows Hosts via Domain Controller.
Configuration of the MSSQL instance
Without exception, the configuration is carried out via the Microsoft SQL Server Management Studio. The CIO Cockpit user created in Configuration of Windows Hosts via Domain Controller is used as user.
1. Login for CIO Cockpit users
The CIO Cockpit user is authorized to log on to the database instance.
Navigation: "Object Explorer" -> "Security" -> "Logins": In the context menu "New Login". The "Login - New" window appears. Select "General" (left bar) -> Enter the CIO Cockpit user in the box next to "Login name:". The search function located to the right of the box ("Search…") may be helpful here. If the user cannot be found, the correct domain should be selected under "Locations". The window can be closed via "OK".
2. Roles of the CIO Cockpit user on system views
The CIO Cockpit user gets the role "db_datareader" on the "master" and "msdb" view of the instance.
Navigation: "Object-Explorer" -> "Security" -> "Logins" -> CIO Cockpit-User: Select "Properties" in the context menu. The "Login Properties" window opens. Go to the left bar and click on "Select User Mapping". Go to the "Users mapped to this login" table for the database "master" and "msdb" in the lower table under "Database role membership for: (master or msdb)" and set the hook at "db_datareader". The window can be closed via "OK".
2a. Veeam only: Additional role of the CIO Cockpit user on Veeam database
The CIO Cockpit user gets the role "db_datareader" on the Veeam database, analogous to step 2.
3. Permissions on MSSQL instance
The CIO Cockpit user needs the "View any definition" and "View server state" permissions on the MSSQL instance.
Navigation: In the "Object Explorer" open the context menu on the MSSQL instance and select "Properties". The "Server Properties" window opens. Select "Permissions" in the left bar, and in the right section of the window select the previously authorized CIO Cockpit user from the list under "Logins or roles". Permissions for this user can be set in the table "Permissions for (CIO Cockpit User)" under the list "Logins or roles". Under the tab "Explicit" the permissions "View any definition" and "View server state" must be set by checking the "Grant" column. The window can be closed via "OK".
