Unable to locate database.mdf in production server
Might be a rights issue. Have a look at the security settings on the physical .mdf file and make sure the user you are using for SSMS has access to that .mdf file.

Most probably, as you are supplying credentials, you are logging on with your Windows user id, so make sure that you have read-write priviliges on the .mdf file.

To get to the security settings:
Explore -> Locate your file -> Right click -> Properties -> Security Tab

Your user group or your user should be in that list.