How can I restrict access to my SQL Server so that it only allows certain programs to connect?

A. A. SQL Server 6.5 and below have no built-in tools/facilities to do this, nor does it allow a stored-procedure to be run on client connection that could be used to do this. Of course if all your data is protected correctly with SQL/Integrated security and all auditing/business rules are held with constraints/triggers then you won't need to worry as it doesn't matter what tool people use to connect. However, in a real-world application, these pre-requisites are unlikely to be there.

SQL Server 7.0 has the idea of application ROLES, as well as user roles. If you have SQL 7.0 then use application roles.

If you are still on 6.5 or below then you can try one of the below - but they are all kludges :-

1. Write your own ODS Gateway and point the clients at that instead of the SQL Server - the ODS Gateway will then do the checking. However, there is nothing stopping clients figuring out the correct SQL client-config entries to point straight at the SQL Server. There are examples of ODS code in the SQL Programmers Toolkit - available for free download from the Microsoft website. 

2. Write a constantly running/scheduled stored-procedure that checks the relevant column in sysprocesses (program_name), and then issues a KILL command for any processes that should not be running. This way allows people to connect and possibly make changes before they are spotted and killed.

3. Change servers to use std security. When the apps connect they request a trusted connection and get dumped into a new database - the only one their trusted account can connect to. This database contains a table holding a lookup between the users NT account and an alternative login name, password (encrypted) and perhaps server and database name to use. The only runnable object is a stored proc that returns the user's rows from this table. The front-end calls the sp, decrypts the password and re-connects to the server. One downside to this sort of approach is that the users "real" name and password could potentially be cracked with an ODBC trace (several versions of the ODBC sdk tools allowed passwords to be displayed) or network sniffer.

4. Another option is to place a CHECK constraint on the sensitive tables which validates the application name. Something like :-

CHECK APP_NAME() = 'Name of your VB app'