My SQL experience tells me that the desktop should only care about where the SQL server is located and what port to talk to it through and not have to worry about a share. SQL should be managing all the SQL files and the desktop should be talking to SQL. Any one have any thoughts on how to get rid of the share and only use server port communication between desktop and server.
This is correct; however, if your application also stores physical files, OUTSIDE the database, then the application would want to always know where those files are located, hence the requirement for the shared drive.
Now in SQL Server 2008, that problem goes away, because it is possible for the Server to store and manage files in the file system, as if they were being stored in BLOBs (binary large objects in the database).
SQL 2008 also goes a long way in addressing the security (HIPAA) issue by the Transparent Data Encryption feature:
TDE is a new feature in SQL Server 2008; it provides real time encryption of data and log files. Data is encrypted before it is written to disk; data is decrypted when it is read from disk. The "transparent" aspect of TDE is that the encryption is performed by the database engine and SQL Server clients are completely unaware of it. There is absolutely no code that needs to be written to perform the encryption and decryption. There are a couple of steps to be performed to prepare the database for TDE, then the encryption is turned on at the database level via an ALTER DATBASE command.
