sql 2005 access control- allow excel to connect but not access?

i have an excel app that is distributed to a few people in my company that has a connection to SQL 2005.

is there a way i can stop enterprising individuals from using the connection string in 开发者_JAVA百科excel (that's plainly visible) from using it in their own access or other tools from connecting to and using sql server resources based on the details they can pull from the connection string from excel?

---

The short answer is that if you reveal your connection string and your source code, smart users will find a way to replicate it. No way to get around that. But....

If your Excel app uses macro code, you can always use a SQL application role. Your code which creates the connection object immediately calls sp_setapprole which switches the security context of the connection. Give the user in your connection string no rights to do anything. Grant limited rights to the app role. Now you just need to find a way to obfuscate the sp_setapprole call so your smarter users don't figure out how to do it too.

At the very least, make sure that the user account that Excel is using only has rights to a very limited set of objects. Put all complicated SQL into views or stored procedures on the server side so you don't reveal your database schema.

---

If they have the source available it is impossible to hide the credentials from your users.

A possible solution to prevent misuse of the database:

• Assign these users to a database role that can only access the DB with limited permissions (e.g. read-only rights and access to only the objects used by the application)
• Provide read-only views that limits the number of rows returns (select TOP(x)...)
• Updates to occur only through stored procedures