You can publish the connection to tableau server and have all your reports reuse the connection from the server. If your sql server name changes again in the future, you just have to edit the connection once in the server.
Mythili's suggestion of tableau server is a good one. Also, in tableau server you can find all data connections in all the published workbooks that refer to a particular server and change those published workbooks to point to a different servername.
Another possibility is to use the DNS of your network to advantage. Here at Tableau, we create logical names for our servers (like mssql2005). The machine has some other name, but in the network directory, the mssql2005 name acts as an alias for the physical machine that is currently running the server. That way if the physical machine changes, you can just point the alias at the new server. Of course, not everyone can access their network in this way. One dirty trick you can do is to edit your c:\windows\system32\drivers\etc\hosts file and point the old name at the new machines ip address. Certainly not a robust fix, but it might do in a pinch for some cases.
For more permanent changes if you don't have tableau server, you can collect your workbooks in a central location, then just write a script to edit the twb file. our workbooks use an xml file format and it's fairly straightforward to do a find and replace in all the files. If you have any packaged workbooks (twbx), you can use command line options to tableau to unpackage it, edit the twb file, then package it up again.
If you have ideas on a mechanism that would fit well in your environment, let us know how you'd like it to work. In the case of this connection profile, where would that be stored?
Thank you for your suggestion. We looked into publishing a data connection but I thought it would be a maintenance nightmare. According to the instructions located here: http://kb.tableausoftware.com/articles/knowledgebase/export-data-connection, you would need to create a data connection for every single report type you might want to build. So for example, if I have a database with 100 tables, I want to create one connection and publish it so that other users can point to that connection and choose the tables they want out of the 100. In the tutorial I linked to, it look like I have to choose the tables out of the 100 and then publish it. So if I have 100 report point to each of those 100 tables in my database, I would need to publish 100 data connection. Am I understanding that correctly?
Also, we do use server DNS here. The issue is our IT team refuse to redirect our old DNS name to the new server. Something about a new policy to reduce maintenance and support headache. They are forcing us to use a new server name so we don't have a choice in the matter.