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.

Be aware that when you use DNS CNAME with a SQL database you need to be aware of the TTL setting. This setting tells a system how long the DNS entry should be cached for before a trip back to the DNS server is required. You will probably need to adjust the TTL in advance so that when you do make the switch, you don't have client systems that have the old entry cached. You can run 'ipconfig /flushdns' command to flush the cache, but it must be run on every computer individually.