Sure. An event with the following structure should do:
@IF
Turn
turn=1
@THEN
PlayWaveFile
FileName.wav
@ENDIF
Or if you want it to play every time a game is loaded:
@IF
ScenarioLoaded
@THEN
PlayWaveFile
FileName.wav
@ENDIF
Put the sound file in a subdirectory called Sound within your scenario directory. Make sure it's in a format that Civ2 can read, ie, 8-bit, mono or stereo, sample rate 22 kHz, PCM.