My company has a website with data stored in Microsoft SQL Server on the production server. The production server is not in Active Directory. The firewall allows connections to the production SQL server from the company offices.
I need to import data from this production server into Excel regularly. The data import should be runnable by 2-5 different persons.
The Excel file is a complex workbook with many tabs. The data from the SQL Server should be imported into one of the tabs. Users are used to doing it via clicking on Data/Refresh all in Excel few times a week.
Requiring users to manually download the CSV file with the data export and manually import it into the Excel is not practical. However, if it can be automated so the user get a data after the click on one button in Excel, it would be OK.
Which authentication option should I use?
The following options are considered:
SQL Server authentication
Easy, but the password to the database is stored in Excel as clear text. The Excel file can be shared easily, and I will not have any control over it. I can mitigate some risks by using the SQL server account that has access only to the minimal set of views needed for the data import and hoping that even if the Excel is shared outside the organization the external attacker would not be able to gain any access to the production server because it will be blocked by the firewall.
Windows authentication with the same password
The production server and the office are not in the same AD. I can create a Windows user account on the production server for the guys from the office with the same username and the same password as they have in AD. The Windows auth will work for Excel data import without requiring to enter the password. The downside is that I'm creating accounts on the production server for people, who don't need it for anything else than the data import. If the client machine is compromised by malware, the risk of spreading it to the production server is greater, because there is no need to enter the password again.
Windows authentication with different passwords
Same as above, but the password has to be entered each time the data import is performed. It will make it harder for the malware to spread. The inconvenience of entering the password repeatedly can be mitigated by using the password manager.
Database replica in the office plus AD Authentication
In this scenario, the production database is replicated automatically to the machine located in the office that is in the AD. Users from the office would be able to access the data using Windows authentication. The downside of it is setting up the database replication and maintaining it.
Best practices
What are the best security practices for importing data from the production database to Excel? What would you recommend?
