Dangers of connecting to SQL Server

[yawnzzzz]

Hi,

I've been using Excel VBA for a while to connect to MS SQL Servers at my company, but now my company's DBA has decided to start denying access to these SQL Servers if I'm choosing to connect via Excel. His knowledge in SQL is obviously above my own, so I'm looking for some understanding from others on whether his claims are well-founded.

For example, he appears fine with me using a local vbscript to connect to a SQL Server, read data, and store the data in Excel. But when I question on using Excel VBA, he states:
"MS Office VBA in general causes issues within SQL regardless of whether the connection strings and queries are the same. Office is not a front-end tool."

Is there truth in this? If so, please let me know what type of issues it can cause.

[Ron Coderre]

Your DBA should only be concerned about applications that store the database username/password locally, which of course is a huge security concern. Since Excel does not create secure compiled applications, I can understand the objection. A possible workaround would be for the DBA to assign VBA developers to a specific role with restricted access (perhaps to certain views or stored procedures) and use Active Directory validation. My choice is to use VBA to run SQL Server Reporting Services (SSRS) reports (that are structured like data dumps) into CSV files and load those files to Excel. SSRS security prevents unauthorized users from accessing the reports.

[yawnzzzz]

The SQL users I've requested are read-only and do not have have access to databases with sensitive information. The VBA Projects are locked as well, but I understand it's not the most secure scenario.

Do you know of any non-security related issues? His concerns seem to be founded in that the method in which Office products connect to SQL Servers will cause performance issues with the primary application that would be using the same database.

[slx]

The only real danger is as Ron said, your excel workbook holding the username and password is not safe. Everything else is server side and what tool you use to get the data is moot. You screwing up a database has nothing to do with vba, it has to do with your account being allowed do that. If your DBA doesn't want you to screw up something, your login should not let you do anything that inserts/updates/deletes.

[yawnzzzz]

So to better frame-up my question, "Are there any known risks associated with using a read-only SQL user login to run select queries on a database with Excel that wouldn't exist with supported reporting tools like Crystal Reports?"

[yawnzzzz]

Your DBA should only be concerned about applications that store the database username/password locally, which of course is a huge security concern. Since Excel does not create secure compiled applications, I can understand the objection. A possible workaround would be for the DBA to assign VBA developers to a specific role with restricted access (perhaps to certain views or stored procedures) and use Active Directory validation. My choice is to use VBA to run SQL Server Reporting Services (SSRS) reports (that are structured like data dumps) into CSV files and load those files to Excel. SSRS security prevents unauthorized users from accessing the reports.

If I put all functions related to the database in a COM Add-in, would that be viewed as secure? Anyone know of any other alternatives to store that information more securely?

[Izandol]

If you are connecting to the server using VBA and ADO in excel then I do not think there can be a difference to connecting using VBScript. If you are connecting using Excel's external data functions (even using Querytables in VBA) then there can be a difference. The statement "Office is not a front-end tool." is wrong however.