CloudSphere can detect and scan MS SQL engine running on Windows endpoints. The network and Windows requirements are the same for any other Windows endpoint.
Login Type Support
The only login option supported by Illuminate 360 access to SQL Server is through Windows Authentication; this requirement must be met for Illuminate 360 to be able to retrieve information from the target MS SQL Server.
The SQL Server supports either ‘Windows Authentication’ mode or the mixed mode 'SQL Server and Windows Authentication mode' but the only user type supported for login by Illuminate 360 is a Windows Authentication user.
As an example, the following SQL server is set up with both authentication methods.
Some example users that would be suitable in the following screenshot are two Windows logins ( Number 1 is a local machine user, and Number 2 is a domain user). The ‘sa’ user cannot be used by Illuminate 360 to login into SQL server.
Login Permissions
The preferred permission set for retrieval of Illuminate 360 relevant information is a user that has sysadmin level permissions.
However, if this is not preferred then permissions must be sufficient to allow the execution of the following commands (and return values) in the following section.
MS SQL Server Permissions
To gather information required for an audit of Microsoft SQL Server, the Scan Engine needs to logon to the database instance being scanned and access certain system objects. For a system admin level login, these permissions will already be present. However, for other users the following additional permissions are required for the user identity that is used to scan the target SQL instance:
|
Permission |
Description |
|---|---|
|
View Server State |
Required to get sessions, license details, and high availability configuration. |
|
View Any Definition |
Required to get login and database details including |
|
Select on sys.sysaltfiles |
(optional) Required to get database details on SQL Server (for SQL Server 2000) |
USE master;
GRANT VIEW SERVER STATE TO [<USERNAME>];
GRANT VIEW ANY DEFINITION TO [<USERNAME>];
-- May be required on older SQL server versions
-- GRANT SELECTON sys.sysaltfiles TO [<USERNAME>];
GO
SQL Commands requiring permissions
This is the current list of SQL commands that require execution permissions (and should return row values).
SELECT name FROM sysdatabases
SELECT CASE WHEN SERVERPROPERTY('IsAdvancedAnalyticsInstalled') = 0 THEN 'false'
WHEN SERVERPROPERTY('IsAdvancedAnalyticsInstalled') = 1 THEN 'true'
WHEN SERVERPROPERTY('IsAdvancedAnalyticsInstalled') IS NULL THEN 'false'
END as [MachineLearning]
SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly')
WHEN 1 THEN 'Windows Authentication'
WHEN 0 THEN 'Windows and SQL Server Authentication'
END as [ServerAuthentication]
select A.monitor_server As [*serverName], A.primary_database As [*databaseName],
A.backup_directory AS [*backupDirectory],
A.backup_share AS [*backupShare], (A.backup_retention_period / (24 * 60)) As [*retentionPeriod],
C.last_backup_file As [*lastBackupFile],C.last_backup_date As [*lastBackupDate],
C.backup_threshold As [*threshold],
case when C.threshold_alert_enabled=1
Then 'true'
else 'false'
end AS [*alertEnabled]
from msdb.dbo.log_shipping_primary_databases A
left join msdb.dbo.log_shipping_monitor_primary C on A.primary_database = C.primary_database
left join sys.databases B on A.primary_database = B.name
SELECT sqlserver_start_time AS [uptime]
FROM sys.dm_os_sys_info
SELECT CASE SERVERPROPERTY('IsFullTextInstalled')
WHEN 1 THEN 'true'
WHEN 0 THEN 'false'
END as [FullTextSearch]
SELECT alloutput.*
FROM (
SELECT Serverproperty('MachineName') AS [*computerName],
@@servicename AS [*instanceName],
Replace(LEFT(@@version, Charindex(' - ', @@version)),' (RTM)','') [*productName],
Serverproperty('productversion') AS [*version],
Serverproperty ('edition') AS [*edition],
[name] AS [*databaseName],
[*databaseSize],
[*logSize],
Isnull(cpu_time_ms, 0) AS [*CPUTime],
Isnull(Cast([CPU_Time_Ms] * 1.0 / Sum([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)), 0) AS [*CPUTimePercent],
[*state],
Isnull(io_in_mb, 0) AS [*IOCount],
Isnull(Cast(io_in_mb/ Sum(io_in_mb) OVER() * 100.0 AS DECIMAL(5,2)), 0) AS [*IOCountPercent]
FROM (
SELECT Db_name([database_id]) AS [Database Name],
Db_name([database_id]) AS [name],
CONVERT(BIGINT, size/128.0) AS [*databaseSize]
FROM sys.master_files WITH (nolock)
WHERE physical_name LIKE '%.MDF%' ) a
LEFT OUTER JOIN
(
SELECT Db_name([database_id]) AS [Database Name],
CONVERT(BIGINT, size/128.0) AS [*logSize]
FROM sys.master_files WITH (nolock)
WHERE physical_name LIKE '%.lDF%')ytd
ON a.[Database Name] = ytd.[Database Name]
LEFT OUTER JOIN
(
SELECT databaseid,
Db_name(databaseid) AS [DatabaseName],
Sum(total_worker_time) AS [CPU_Time_Ms]
FROM sys.dm_exec_query_stats AS qs
CROSS apply
(
SELECT CONVERT(INT, value) AS [DatabaseID]
FROM sys.Dm_exec_plan_attributes(qs.plan_handle)
WHERE attribute = N'dbid') AS f_db
GROUP BY databaseid)b
ON a.[Database Name] = b.[DatabaseName]
LEFT OUTER JOIN
(
SELECT Db_name(database_id) AS [DatabaseName],
state_desc AS [*state]
FROM sys.databases) s
ON a.[Database Name] = s.[DatabaseName]
LEFT OUTER JOIN
(
SELECT Db_name(database_id) AS [DatabaseName],
Cast(Sum(num_of_bytes_read + num_of_bytes_written)/1048576 AS DECIMAL(12, 2)) AS io_in_mb
FROM sys.Dm_io_virtual_file_stats(NULL, NULL) AS [DM_IO_STATS]
GROUP BY database_id)c
ON a.[Database Name] = c.databasename) allOutput
LEFT OUTER JOIN
(
SELECT Db_name(database_id) AS databasename,
Count (1) * 8 / 1024 AS usedmemoryinmb
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
) MemousedDB ON alloutput.[*databaseName] = memouseddb.databasename