Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Best Practice security when using Excel to connect to SQL Expand / Collapse
Author
Message
Posted Thursday, September 19, 2013 10:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:00 AM
Points: 138, Visits: 391
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.
Post #1496477
Posted Thursday, September 19, 2013 11:15 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 11:21 AM
Points: 153, Visits: 946
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
Post #1496502
Posted Thursday, September 19, 2013 3:29 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, April 17, 2014 4:43 AM
Points: 756, Visits: 631
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.



Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1496612
Posted Friday, September 20, 2013 1:44 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:00 AM
Points: 138, Visits: 391
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!
Post #1496736
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse