Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Best Practice security when using Excel to connect to SQL


Best Practice security when using Excel to connect to SQL

Author
Message
Maddave
Maddave
Old Hand
Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)

Group: General Forum Members
Points: 362 Visits: 1460
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.
Andreas.Wolter
Andreas.Wolter
SSC-Enthusiastic
SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)

Group: General Forum Members
Points: 155 Visits: 1056
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
Erland Sommarskog
Erland Sommarskog
SSC Eights!
SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)

Group: General Forum Members
Points: 931 Visits: 866
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
Maddave
Maddave
Old Hand
Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)

Group: General Forum Members
Points: 362 Visits: 1460
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search