I would define the class like this:

[ComVisible(true)]
public class TestSecureString : IUnsecurePassword
{
    public SecureString Password
    {
        get;
        set;
    }

    string IUnsecurePassword.Password
    {
        get
        {
            if (Password == null)
                return null;

            IntPtr ptr = Marshal.SecureStringToBSTR(Password);
            string bstr = Marshal.PtrToStringBSTR(ptr);
            Marshal.ZeroFreeBSTR(ptr);
            return bstr;
        }
        set
        {
            if (value == null)
            {
                Password = null;
                return;
            }

            Password = new SecureString();
            foreach (char c in value)
            {
                Password.AppendChar(c);
            }
        }
    }
}

[ComVisible(true)]
public interface IUnsecurePassword
{
    string Password { get; set; }
}

Then in C++, the IUnsecurePassword interface would be exported like this:

  virtual HRESULT __stdcall get_Password (/*[out,retval]*/ BSTR * pRetVal ) = 0;
  virtual HRESULT __stdcall put_Password (/*[in]*/ BSTR pRetVal ) = 0;

It does not prevent someone from using the IUnsecurePassword interface if he really wants to, but it raises the bar.