I connect excel to external data sources (mainly sql server DBs) alot and I am looking for a way to hide/secure the data string that is used for this connection. Currently if I embed a query into the excel workbook i need to tell it the server, username and password. When a user uses the file if they are savvy they can see this connection string and acquire the username and password to the DB/Server, an obvious issue.


I am guessing it is easy to avoid this by using an external file for the connections and then lock down the external file? If that is the case does the user also need at least read access to this external file?
Hopefully I am missing a very easy solution..

We use ODBC Windows Datasources to get data from SQL server to excel in our firm.

1. For SQL database the access of users is controlled through Domain User Groups - i.e. when user is logged in into Domain, and is member of allowed User Group, it has rights given to this group;
2. Excel users which must connect to some SQL database must have an ODBC Datasource defined on his/her computer. The Datasource has usually access to only one SQL Database - when the user needs the access to several databases from separate workbooks, he/she must have a separate datasource for every one of them. When some application needs access to several SQL Databases, then the user must belong to several User Groups, or his User Group must have access rights in all needed databases;
3. Excel application connects to SQL Server database through ODBC datasource. As all rights are managed in Domain and SQL server, there is no password saved in Excel;
4. Passwords for access to databases may be stored in Datasources too (p.e. in cases when access to database is for database-defined user). They are there protected too - the user cant see the password as it is masked.