Yes, I have heard of folks that have issues with SCCM and it is not a popular tool to use in patching SQL Server. WSUS acts a little different than the Windows Update agent on a server/desktop. WSUS gets a list of all patches available after they are released from Microsoft. There is a sync task that is used to "sync" your WSUS server with Microsoft servers for the patches that are available and shown needed within your servers/desktops.

It has been a few years since I administered a WSUS environment but of what I do remember you would pretty much put your SQL Servers in a group. Within that group you tell WSUS what product to check for patches on, yours being SQL Server. It will then sync with Microsoft to find out what the current patches/CUs are and if that server has them installed. You can require prior approval before deploying the patch or CU that is needed.

As far as Internet connectivity goes there are ways around that I believe with the new version of WSUS. I do believe you can manually put the patch or CU within the repository for WSUS. With firewalls I have seen environments that will have one WSUS that is allowed Internet access only to the Microsoft WSUS server to download the patches. Then the internal WSUS servers all talk to that server like a parent-child relationship. Each WSUS server will sync up to the one WSUS that has Internet access. This provides a more secure environment and does not require opening up Internet access to all your servers.