When running a data collection job for a Standard like "CIS Security Configuration Benchmark for Microsoft SQL Server 2008 R2 Database v1.0.0", opening only the port 1433 (default SQL) is not enough to get successfull job. (Agentless data collector)
Following the documentation, only the port 1433 is required for SQL agentless data collection. This is correct for SQL only checks or queries.
An example of SQL only check/query -> List the User Name and their Role in the DB.
However, when you run a CIS Standard, this requires more than SQL only. Many checks are related to the SQL server itself and the filesystem/registry running on Windows.
An example of a Windows/SQL check -> Is service pack 3 or higher applied on SQL Server 2008 and service pack 1 or higher applied on SQL Server 2008 R2?
The check is a SQL related check, but it needs FileSystem access, so requiring the same port as Windows AgentLess Data collection.
Ports required :
SQL Agentless Only (TCP) -> 1433
Windows Agentless (TCP) -> 135,137,138,139,445,Ephemeral port range (49152 to 65535 - Different OS distributions use their own ranges)
Warning : If you are using SQL only checks, and wants to only use port 1433, you need to make sure not to use Windows Authentication method for SQL. You must use a SQL username only. Else this will require additional port from Windows for the verification of the user.