To follow this tutorial you will need to have jailbroken your iPhone and installed OpenSSH. You can find instructions on how to jailbreak here and instructions on how to install OpenSSH here. Don't forget to change your password!
Locate a sound clip you would like to use as your SMS alert tone and drag it into your iTunes Music Library.
Select Preferences from the iTunes Menu.
Click the Import Settings button in the General Preferences Tab.
Choose Import Using AIFF Encoder from the Import Settings popup window.
Close the Preference windows and then Control+Click on the sound file you just added to iTunes. Select Create AIFF Version from the contextual menu.
Notice a duplicate copy of your tone will appear in the iTunes Music Library. Drag this duplicate to your desktop.
The iPhone has slots for six incoming sms tones. They are named sms-received1.caf through sms-received6.caf. Select the file you just created on the desktop and rename it to one of these six possible filenames. For example, my tone was siren.aif and I renamed it to sms-received6.caf so it would occupy the sixth slot.
You may be asked to confirm the extension change. Click the use .caf button.
Launch Fugu from your applications folder.
When Fugu opens you will be asked for the information required to SSH into your iPhone. In the Connect To field enter your iPhone's IP Address. Input root as the Username then click the Connect button. If you do not know your iPhone's IP Address you can find it using these instructions.
When prompted for a password, input the password you set for OpenSSH. If you haven't set your own password then alpine is the default. Click the Authenticate button to continue.
Click the Go To... icon on your toolbar.
Input /System/Library/Audio/UISounds/ into the Go To field, make sure Remotely is selected, then click the Go button.
Locate the sms-received file you would like to replace in the right window pane. In this example I want to replace sms-received6.caf with my own sound file.
Right click or control+click the existing file and select Rename from its contextual menu.
Rename the file to something like this: sms-received6.caf.bak.
Navigate to the desktop using the left window pane and locate the .caf file we just created. Drag this file from the left pane into the right to copy it to your iPhone.
Now reboot your iPhone to let the changes take effect then launch Settings from the Springboard.
Press to select Sounds from the Settings menu.
Select New Text Message from the Sounds menu.
Now you can select your customized SMS alert tone from the list. Notice the names haven't changed. The top selection is actually sms-received1.caf and the bottom is sms-received6.caf. So if you copied over a new sms-received6.caf you would select Electronic from the list.