SQL Server 2022 - Polybase External Tables - Create a master key

Question

SQL Server 2022, CU 10 on Windows 2022.

I am migrating a SQL Server 2019 database to a new SQL Server 2022 and when attempting to SELECT rows from an external Polybase driven table, I get the following error:

enter image description here

The service master key on this server is a restored copy of the service master key from the original server.

I verified that information by comparing the GUID of the ##MS_ServiceMasterKey## in the master database on both instances.

The External Table is accessible on the original server.

If the service master key in the master database is the same as the key in the original server, what other steps do I need to take?

The password that was used to create the database master key is no longer available. Polybase is enabled for the instance and the Engine and Data Movement services are running.

Thank you,
Craig

• Sean Gallardy: What's the output of the following query in that database: SELECT ke.crypt_type_desc FROM sys.symmetric_keys sk INNER JOIN sys.key_encryptions ke on sk.symmetric_key_id = ke.key_id WHERE sk.name = '##MS_DatabaseMasterKey##'
• user4659: @SeanGallardy ENCRYPTION BY MASTER KEY and ENCRYPTION BY PASSWORD V2 on both the original and new instance

Answer 1

The database master key password is not requested when using Polybase, so you need to drop the existing and create a new one, since you don't have the password you specify a new one. Once done, the master key will be open and you will be able to use Polybase normally.