SQL Server External tables are a great resource and potentially a terrible security risk.
Summary
- It's bad practice but people often restore a prod database to another server for DEV\UAT purposes.
- External Data Sources for External tables in restored prod databases will Still Reference Production
How do External Tables work in SQL Server?
External tables are a great feature allowing us to query data in a different database as if it were a local table in the database we are working on.
They work via an “external data source” which defines the remote server & database we want to query along with a “database scoped credential” which securely holds authentication details for the remote server/database.
How can it all go wrong?
The External Data Source AND Credentials are Stored as objects in the database.
So, if we restore a prod database to UAT/Dev we restore the Data Source & Credentials as well. Which risks the scenario below where we have a UAT/Dev database but external tables are referencing production.
Clearly, if we are following best practices & Prod is on a different network with no route to UAT/Dev we will just see an error but this is not always the case.
Conclusion
Be mindful of the consequences of restoring prod databases to UAT/Dev.
At Version 1 we would strongly advocate & implement a strong forward-only CI/CD process where UAT/ Dev configuration can be promoted to Production but prod cannot be demoted to UAT/Dev.
Keep UAT/Dev separate & move just data if you absolutely have to, but not the whole database with all its unexpected baggage.
Ideally use a data anonymisation process or synthetic data in Dev/UAT.
About the Author:
Mike Knee is an Azure Data Developer here at Version 1.
