Movere only scans SQL Server if it has confirmed that a SQL engine is both present and running. This is determined by enumerating the services present on the targeted endpoint via the CIM. For example, if a SQL engine is present, but it is a passive instance, then Movere makes no attempt to connect. Movere also makes no attempt to connect to SQL Reporting, Analysis or Integration instances.
Once a running instance of a SQL engine has been confirmed, Movere will only connect via TCP. No attempt is made to bypass TCP (e.g. Movere does not use named pipes, which is one of the primary reasons why Movere does not usually connect to SQL Express instances as networking protocols, including TCP, are disabled by default in Express Edition). To connect to SQL Server, Movere will query the registry to identify the port number each running instance is listening on. Movere will only attempt to connect to SQL Server using the server’s name via the port number(s) identified (e.g. Movere will not attempt to connect using the devices IP address nor will it use random port numbers, nor will it assume every SQL instance is leveraging the default SQL port 1433). The only exception to this is a SQL Server cluster. If a SQL server cluster is identified from an active node, Movere will only attempt to connect to SQL Server using the cluster name, NOT the node name.
The connection to SQL Server is identical whether the device is scanned locally or remotely, and it is always done using the .NET native SQL client libraries.
The Movere Inventory and Actual Resource Consumption (ARC) bots require ‘db_datareader’ access to the SQL Servers ‘master’ database. The only exception to this is the SQL Servers ‘msdb’ database which stores Log Shipping configurations. If SQL log shipping is used and Movere cannot access the ‘msdb’ database, then these configurations will not be visible on the Movere website.
It is not uncommon for the user running Movere to have limited or no access to SQL Server. With local administrator rights, Movere can still access the Windows layer and will, at a minimum, retrieve the name and size of each database on the server in any state other than ‘OFFLINE’. If a database is not ‘OFFLINE’, then it can be in one of several states including online, restoring, recovering, suspect, or emergency. When Movere has no access to SQL Server and can only retrieve a list of databases from the Windows layer, instead of assuming each database is in an ‘ONLINE’ state, Movere reports the state as ‘UNCONFIRMED’.
How does Movere connect to Exchange and Skype?
Movere connects to on-prem Exchange and Skype via PowerShell embedded in .NET.
How does Movere cycle through Windows and SQL Server credentials?
Movere uses the credentials assigned to a domain in the order they are entered. Movere always starts with Windows credentials, and the Movere Console forces the user to enter at least one set of Windows credentials before any scanning can begin. If these credentials fail, Movere will attempt to connect using any other Windows credentials assigned to the targeted domain.
For SQL Server, Movere will cycle through all Windows credentials provided for the targeted domain first. If these fail, Movere will attempt all SQL Server credentials provided and, if these fail, Movere will make one last attempt to connect using the account that started the scan, which could be the credentials of the Movere user or the local system account.
When running Movere, there are five categories of SQL Server data.
- List of databases (including state, size, creation date), clustering, mirroring and availability groups (master db),
- SQL Server performance and connection information including CPU usage per min by instance, CPU usage by DB, and DB connections,
- Log shipping details (msdb db),
- SQL Server-based data e.g. SCCM; and
- SQL DB and drive statistics.
Items 1 and 2 are the easiest to gather and do NOT require any special service account creation for Movere to use. Movere can collect these items by leveraging the ‘NT AUTHORITY\SYSTEM’ account, which it will do by default. This account must be enabled for login and granted permission to connect to the database engine, which is the default setting. The only server role it requires is ‘public’, which is also enabled by default. Now this is where it gets interesting. For SQL 2008R2 and prior, the ‘NT AUTHORITY\SYSTEM’ was given ‘sysadmin’ privileges by default. Microsoft removed ‘sysadmin’ privileges from this account beginning with SQL2012, but as I mentioned above, this account is given ‘public’ by default across all versions. If the customer disables this account, then the database state will come through on the Movere website as UNCONFIRMED.
Here are the results from a scan run with the ‘NT AUTHORITY\SYSTEM’ account disabled:
Here are the results when run with ‘NT AUTHORITY\SYSTEM’ enabled, with the ‘public’ server role only:
As you can see, we can now see each databases state and creation date.
This brings us to item 3, log shipping. This ONLY impacts log shipping, the other SQL HA features can be extracted with the settings above, but to get log shipping details, the ‘NT AUTHORITY\SYSTEM’ account must be manually given ‘db_datareader’ access to the ‘msdb’ db if the only server role it has is ‘public’. Alternatively, you can grant the ‘sysadmin’ server role to the ‘NT AUTHORITY\SYSTEM’ account and Movere will then be able to gather log shipping details, if log shipping is used on that server of course. Alternatively, a service account created by the customer for Movere to use can be added to the SQL Server and given ‘db_datareader’ access to the ‘msdb’ db. Most customers wouldn’t bother with this as it’s easier to just give the existing ‘NT AUTHORITY\SYSTEM’ account ‘db_datareader’ access to the ‘msdb’ database, but if the SQL Server is housing a secondary data source that they need Movere to collect, then a service account can be added and given ‘db_datareader’ access to both the ‘msdb’ database and the secondary database. NOTE: The service account only requires the ‘public’ server role. Here’s what that would look like for account ‘IO\sqltest’:
Giving a service account these settings would allow Movere to gather both log shipping settings AND extract SCCM data from the SCCM database ‘CM_ULI’, in addition to SQL items 1 and 2.
The last category (5) is the SQL DB and drive statistics. This level of detail requires ‘sysadmin’ permissions, which is why we turned SQL ARC’ing off by default, as this category would generate an error message on each ARC beat for these data points if they were using an account that did not have ‘sysadmin’ privileges. If you need this data then grant ‘sysadmin’ access to either the ‘NT AUTHORITY\SYSTEM’ account OR give it to the service account you want Movere to use:
Below you will see the difference in data returned when you give Movere ‘sysadmin’ access versus when you only give it ‘public’ only access.
SCCMSQL.io.priv was given NT AUTHORITY\SYSTEM ‘public’ only while ALTSQL.io.priv was given NT AUTHORITY\SYSTEM ‘sysadmin’ access.
SQL Server CPU instance level data is returned from both servers:
SQL CPU database level data is also returned from both servers:
SQL disk utilization is only returned for the ALTSQL server (scanned using ‘sysadmin’ access):
SQL Server database connections are returned from both servers:
SQL Server DB read/write data is only returned for ALTSQL:
SQL Server DB Used vs. Total is only returned for ALTSQL:
As you can see, the only thing you lose by only using ‘public’ access is the disk level detail from SQL Server.
REMEMBER: Movere turns CollectSQL off by DEFAULT. You need to set this option to ‘true’ in both the ARC2 and ARC4 configuration files to gather SQL Server data performance data during an ARC scan: