September 19, 2013 at 10:00 am
I would be interested in peoples thoughts on what the best practice should be for users to connect Excel to SQL Server to view database data?
I have a number of users who want to connect Excel to sql, pull into a worksheet a load of financial data, save it as a spreadsheet which they will then put into a quarterly report or similar. As a DBA it rings alarm bells and conjures up images of users leaving usb keys with financial data stored on them, on trains etc!
Beyond controlling what they can access by using an AD group with the correct people as members, and controlling their access through SQL security controls, I am not sure what the best advice would be.
I would be interested to hear what others think are arguments for or against this practice.
September 19, 2013 at 11:15 am
Well,
if you already know all about only granting access to only the data they really do need to see – preferably via views or stored procedures, then what else would you want to do once the data is out in Excel - or any other application, which they might use, if they know how to connect?(!)
It’s hard to control/deny printing and exporting to USB/Email.. so I’d say you have to live with the fact that once the data is pulled from SQL Server, it’s “out”.
Now, the question is, what exactly do you want to protect yourself from, and can you do it once the choice is Excel?
Andreas
---------------------------------------------------
MVP SQL Server
Microsoft Certified Master SQL Server 2008
Microsoft Certified Solutions Master Data Platform, SQL Server 2012
www.insidesql.org/blogs/andreaswolter
www.andreas-wolter.com
September 19, 2013 at 3:29 pm
Just to add what Andreas says. There are various ways you can set up the server so that they cannot connect directly to SQL Server from Excel. However, it is unlikely that this will address you particular concerns. If you only expose the data through the application, you have better control of what data they can see and modify.
But no matter how you expose the data, users will expose to get it in a grid, so that they can export it to Excel. And once it's there, they can do all sorts of with it which they should not do.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 20, 2013 at 1:44 am
Thanks both of you, that is kind of what I thought would be the answer. As the DBA I am working with our IT Security team to make sure users are clear on the implications of extracting this data and to try and make sure they enforce some kind of control themselves. I.e dont save it off the network, dont email it to personal email accounts etc. I think, as you both say, that is the only thing that can be done. Once it's out, it's out!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply