Updating external data sources in SQL Server

January 22nd 2021 Microsoft SQL Server

I recently had to create a copy of multiple Azure SQL databases. They were all interconnected, i.e. each database had some other databases registered as external data sources. After the import, these data sources had to be updated with new database locations and credentials.

Since I didn't know which external data sources were registered in each database, I started by listing them:

SELECT * FROM sys.external_data_sources;

And also the credentials they used:

SELECT * FROM sys.database_scoped_credentials;

From here on, I only had to update them all with new information:

  • For credentials, I set the new login and password:

      IDENTITY = 'remoteUser',
      SECRET = 'remoteUserPassword';
  • For external data sources, I set the new server and database names:

      LOCATION = N'new-server.database.windows.net',
      DATABASE_NAME = N'NewDatabase';

To make sure the configuration was correct, I tried querying an external table from each external data source.

After creating a copy of a database with external data sources, these need to be updated to point to corresponding copies. Otherwise, they will still point at the original database instead of the copy.

Get notified when a new blog post is published (usually every Friday):

If you're looking for online one-on-one mentorship on a related topic, you can find me on Codementor.
If you need a team of experienced software engineers to help you with a project, contact us at Razum.
Creative Commons License