I was hoping someone who uses SQL Server more than I do would respond. You should probably look at a SQL Server discussion group, perhaps in a Wrox forum.
I think SQL Server allows you to attach a remote database and use it as if it were local. In that case, you would supply the password information when you make the connection.
If you mean you want a remote program to log on to your database using the password, do some stuff, and then disconnect, you might look into Web Services. You could make a Web Service on your machine that does the actual work, connecting to the database, processing the request, and returning the result. Then you would need to secure the service. Unfortunately I don't have a lot of experience with that, particularly with the new WCF.
Sorry but I really haven't done enough of this sort of thing lately to give you a definitive answer. Anyone else want to chime in?
Check out my book:
"Expert One-on-One Visual Basic 2005 Design and Development"
Sign up for the free VB
Helper Newsletters at http://www.vb-helper.com/newsletter.html