Are CNAME aliases for connection strings a good idea?
One minor drawback would be that you would need to add a Subject Alternative Name (SAN) to the certificate that is bound to SQL Server if you are using encrypted connections.  This could be a significant pain if you have a "shared" server that hosts a number of smaller applications and you want to set up an alias for each application.

If you are using a separate alias for each application, you would need to generate and install a new certificate each time you add a new application to the server.  The same might apply if you want to make sure there are no unused/invalid SANs in the certificate, in that you would need a new certificate each time you removed an application.