@pavangayakwad - No, I'm not able to open the SDF DB file with SSMS either (same problem as you). SSMS doesn't appear to allow integrated security with a compact server. User ID and password is required in this mode. Please share if you have a work around.
I have had luck with the following:
1) Open SDF through Visual Studio "Connect to DataBase" option. Then I can browse tables with minimum functionality.
2) Create a MDF DB instead using default SQLExpress engine: A) Delete the existing SDF DB, B) set the SampleData class to inherit from DropCreateDatabaseAlways insead of if model changes (just to force a new DB), C) Delete the entire
web.config connectionstring to force default to SQLExpress and MDF.
Alternatively, you can probably change the connection string to ".... MvcMusicStore.mdf ... providerName="System.Data.SqlClient" , but I have not tried this yet myself.