locked
SQL Data Warehouse Security RRS feed

  • Question

  • Hi there! 

    I'm in the process of building a data warehouse in SQL 2014. The data warehouse is protected based on roles with username and password. My question is how to prevent leaks when exposing these data to other systems.

    For example, if a user connects to the server and imports these data to PowerPivot, what happens to the password protection?

    My goal is to have data stored only on SQL Server, but allow power users to consume these data. For instance, I want to prevent power users from storing data in excel files, but being able to analyze as read-only from the server. Is this possible? Any suggestions?

    Thanks in advance for your support!









    • Edited by isa7667 Thursday, February 26, 2015 3:08 AM
    Thursday, February 26, 2015 2:06 AM

Answers

  • no.

    the data persisted in the powerpivot model in excel will remain.

    the credentials will only be checked if the model is refreshed. But a refresh failure will just stop new data being added, the existing data will remain.

    Once it's in excel it's up for grabs for anyone that has access to the file. Same as if it was pasted into an email or word doc etc.

    Best bet is to educate users re: data sensitivity or have processes in place where the powerpivot models are only saved/stored in unc/sharepoint locations that are themselves secured.

    But the second it's copied to the desktop etc (and it will be!), it's out of your control.


    Jakub @ Adelaide, Australia Blog

    • Marked as answer by isa7667 Monday, March 2, 2015 8:56 PM
    Monday, March 2, 2015 2:04 AM
  • Hi there,

    I see that you are concerned about data sencitivity and security. So how we handle this is:

    If the Power Pivot report created is to be shared with multiple users, use secured ways such as Sharepoint or have the report stored in a Windows folder which is secured by controlling the ACL.

    Define which data is sensitive and which not. For Sensitive data you can control the number of people who access, and have a list of who can access. 

    Always use the Windows Authentication, and control the access via Windows Active Diretory groups. Define owners for each group and do a recertification every 6 months or an year to revoke inactive user access.


    Vinish Viswanathan

    • Marked as answer by isa7667 Monday, March 2, 2015 8:57 PM
    Monday, March 2, 2015 6:20 PM

All replies

  • Hi Isa7667,

    According to your description, you need to restrict the users to view the data only on SQL Server Management Studio, and prevent users from storing data in excel files, right?

    I have tested it on my local. Grant only the select permission to a login, and the user can login to SSMS and view the data and copy it to an Excel sheet using the method in the following link.
    https://community.dynamics.com/gp/b/gpdba/archive/2013/05/16/two-ways-to-export-data-from-ssms-to-excel.aspx
    Based on my research, there is no such a functionality to prevent users from storing data in excel files in current version.

    If you have any concern about this feature, you can submit a feedback at http://connect.microsoft.com/SQLServer/Feedback and hope it is resolved in the next release of service pack or product. Your feedback enables Microsoft to make software and services the best that they can be, Microsoft might consider to add this feature in the following release after official confirmation.

    Thank you for your understanding.

    Regards,


    Charlie Liao
    TechNet Community Support


    Friday, February 27, 2015 3:14 AM
  • Hi Charlie,

    This is correct! The objective is to implement data security.

    I was thinking about the following case scenario:

    • A user is granted access to the database.
    • The user extracts data from the Data Warehouse using Power Pivor add-in.
    • Data is imported to Power Pivot

    My question is what happens with data security when importing to Excel? Will the user be prompted for the DB passoword everytime he/she opens the xls file? 

    Thanks for your support!

    Ignacio

    Friday, February 27, 2015 1:10 PM
  • no.

    the data persisted in the powerpivot model in excel will remain.

    the credentials will only be checked if the model is refreshed. But a refresh failure will just stop new data being added, the existing data will remain.

    Once it's in excel it's up for grabs for anyone that has access to the file. Same as if it was pasted into an email or word doc etc.

    Best bet is to educate users re: data sensitivity or have processes in place where the powerpivot models are only saved/stored in unc/sharepoint locations that are themselves secured.

    But the second it's copied to the desktop etc (and it will be!), it's out of your control.


    Jakub @ Adelaide, Australia Blog

    • Marked as answer by isa7667 Monday, March 2, 2015 8:56 PM
    Monday, March 2, 2015 2:04 AM
  • Hi there,

    I see that you are concerned about data sencitivity and security. So how we handle this is:

    If the Power Pivot report created is to be shared with multiple users, use secured ways such as Sharepoint or have the report stored in a Windows folder which is secured by controlling the ACL.

    Define which data is sensitive and which not. For Sensitive data you can control the number of people who access, and have a list of who can access. 

    Always use the Windows Authentication, and control the access via Windows Active Diretory groups. Define owners for each group and do a recertification every 6 months or an year to revoke inactive user access.


    Vinish Viswanathan

    • Marked as answer by isa7667 Monday, March 2, 2015 8:57 PM
    Monday, March 2, 2015 6:20 PM