none
Securing External Consultants

    Question

  • Hi, I am a network admin and sometimes SQL admin. I have been asked to allow a consultant to build a database reporting server on our network. He will VPN into our network through a Palo Alto firewall and use RDP to access a single non-domain server called "Reports." On this server, the consultant will have a local non-admin user account and db_owner access to SQL Server. From Reports, he will also be able to "connect" via SQL server to two other SQL servers (i.e., DB1 and DB2) so he can query and gather data. He will have a db_reader account on those servers.

    My question is, does anyone see any holes in this scheme? I have not allowed this type of external access before. The Palo Alto will do a good job with the VPN, but I want to make sure the consultant can't do anything else on the internal network except build a database on Reports and query/collect data on the DB1 and DB2 servers. 

    I worry that their systems may not be secure and could provide a channel into my network for the boogy man. Any advice is appreciated.
    • Edited by SBAdmin Thursday, May 31, 2018 1:15 AM
    Thursday, May 31, 2018 1:12 AM

Answers

  • You're welcome

    Am I assuming that by "reader account" you mean "db_datareader" on certain target databases?
    No problem with that, if you take into accont that he can read from any table in those databases then.
    Also I would recommend to have Windows authentication with Kerberos in place to ensure mutual authentication.

    If you don't have database ownership chaining active he is bound inside those.

    Sometime I will pre-create dedicated stored procedures (sometimes views) for such cases, so really only certain data is visible. Sometime one can also restrict access to certain schemas. (Schema-design for SQL Server: recommendations for Schema design with security in mind)
    But this may not be a practical option for you.

    A firewall zone is a good thing to do. So you have kind of a "DMZ" for his server.

    Without DDL-permissions outside his database you are safe from modifications which are harder to secure.

    What you also should look at is the other direction: "is there any possibility that a possible intruder can access his report server to get more date outside the company?" I assume you already make sure of this, but I want to make sure this aspect is not forgotten.

    Good luck


    Andreas


    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012
    MCM SQL Server 2008
    MVP Data Platform MCSE Data Platform
    MCSM Charter Member, MCITP Charter Member etc.
    www.SarpedonQualityLab.com
    (Founder)

    • Marked as answer by SBAdmin Wednesday, June 06, 2018 10:49 PM
    Wednesday, June 06, 2018 6:29 PM

All replies

  • For me your concept sounds well.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, May 31, 2018 6:27 AM
  • It's hard to put a whole security concept into a few paragraphs. So allow me to ask some questions:

    How do you ensure the safety of the consultants computer?

    How exactly is the Authentication from Report-Server to the others being done?

    Is it all one network?

    Why does he need to be db_owner? Isn't ddl_admin, datareader etc sufficient? Or do you want him to take care of backups and files as well?

    so, while it sounds solid, some details might be adjustable

    Andreas


    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012
    MCM SQL Server 2008
    MVP Data Platform MCSE Data Platform
    MCSM Charter Member, MCITP Charter Member etc.
    www.SarpedonQualityLab.com
    (Founder)

    Monday, June 04, 2018 10:22 AM
  • Andreas,

    Thanks for the response. I can't be sure of the security of the consultants computer so I want to lock things down pretty tight. Good point about db_owner. I will dumb those permissions down. 

    Regarding authentication to the other servers, the consultant will log in using a SQL Server reader account that will not have any other permissions on the server. What am I missing in terms of security using this approach?

    Originally, I thought this would all be on one network, but I am putting the Report-Server is a Palo Alto firewall zone and will use strict rules to limit the traffic to the other SQL servers which are on our business network. 

    Appreciate any comments or suggestions.

    Tom

    Wednesday, June 06, 2018 5:11 PM
  • You're welcome

    Am I assuming that by "reader account" you mean "db_datareader" on certain target databases?
    No problem with that, if you take into accont that he can read from any table in those databases then.
    Also I would recommend to have Windows authentication with Kerberos in place to ensure mutual authentication.

    If you don't have database ownership chaining active he is bound inside those.

    Sometime I will pre-create dedicated stored procedures (sometimes views) for such cases, so really only certain data is visible. Sometime one can also restrict access to certain schemas. (Schema-design for SQL Server: recommendations for Schema design with security in mind)
    But this may not be a practical option for you.

    A firewall zone is a good thing to do. So you have kind of a "DMZ" for his server.

    Without DDL-permissions outside his database you are safe from modifications which are harder to secure.

    What you also should look at is the other direction: "is there any possibility that a possible intruder can access his report server to get more date outside the company?" I assume you already make sure of this, but I want to make sure this aspect is not forgotten.

    Good luck


    Andreas


    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012
    MCM SQL Server 2008
    MVP Data Platform MCSE Data Platform
    MCSM Charter Member, MCITP Charter Member etc.
    www.SarpedonQualityLab.com
    (Founder)

    • Marked as answer by SBAdmin Wednesday, June 06, 2018 10:49 PM
    Wednesday, June 06, 2018 6:29 PM
  • Andreas,

    I trust this consultant to build and write to databases on this system. I will take you advice and assign only db_ddladmin, db_datawriter, and db_datareader. I did some reading and I think this should be sufficient to allow him to build tables, write statements, and so on.

    To your last point, I don't trust the consultant's computers. He takes his laptops to other businesses and plugs them in, so there is a very good chance he has some infection that has not be detected by his use antivirus software. That's a whole other topic. 

    Thanks for your comments. You've sent me on some good research trails.

    Tom

    Wednesday, June 06, 2018 11:01 PM