First some background. The reason why I even started investigating how to do this manually was that when I tried running the PowerShell script supplied by Microsoft in the wizard.
|Press the "i" icon to get a window containing the PowerShell Script that Microsoft recommends for setting up the Key Vault.|
First step is to make sure you have all your data straight. The power shell script is good for this. Check out Scott clip if you want to know how to find the different strings. He shows it very clearly.
Just copied from the PS-Script:
$subscriptionId = '<subscription ID>' $keyvaultName = 'MyVault' $secretName = 'MySecretName' $location = 'North Europe' $connectionString = 'Server=tcp:<db-name>,1433;
Persist Security Info=False;
$organizationIdList = '<DYN365GUID>'$tenantId = '<AZURE TENANT ID>'
The highlighted parts have to be replaced by your settings. I will use these variables to have something to reference to further in this article.
|Search for Key Vault and add the "Key vault", the top one in this picture|
Then we have to set it up. Not so tricky if you have worked with Azure before. Consider if you want to work in an existing Resourcegroup or if you want to create a new one. Typically you need to have Azure SQL services running as well so it might be good to keep them all together to be able to see the costs and control who has access why a resource group might be a good idea. But that should hence already exist. If not, you can create it. I would recommend keeping Azure SQL and Key vault in the same, not sure if it actually works in different resource groups, probably does, but I haven't tested.
|Creating the key vault - in this case I am creating a new resource group, normally it would already exist|
Now we need to open the Key vault and select the "Secrets" section in the menu on the left hand side and press the button:
Then you have to enter you Secret name ($secretName) and the connection string ($connectionString) into the value.
|Creating a secret - $secretname in Name and $connectionstring in Value|
You should now return to the previous screen and see a row for your secret.
It should open the settings panel for the Secret, press the "Tags" part which is located in the middle and add a tag which has $OrgIdList ($organizationIdList) as the key and Tennant ($tenantId) as value. I have blurred them out below as they are rather private.
|Adding a tag with OrgIdList and tenantId to a Secret|
You then need to go back to the Key Vault and click on the "Access Policies" menu item, you should then see yourself as the principal as this was set when we created the key vault. We now need to add Data Export Service as a valid Principal with read access rights.
So click "Add", click "Select Principal" and search for "b861dbcc-a7ef-4219-a005-0e4de4ea7dcf" which is the ID for Data Export Service. It should show up like this:
It needs to have "Secret Management Operations - GET" permissions and nothing else.
Now, go back to the Secret and copy the URI to the Secret.
|Getting the URI for the Key Vault Secret|
Being too cheap with the Azure SQL level
If you don't go for a Azure SQL P1 and choose a lower tier, you might get this warning:
Might have to set activation date on secret
Seems that you might have to set an activation date on the secret. Not sure why this is, the PS-script doesn't seem to do this. But not very hard.
|Added activation date on the Secret from June 4.th|
Using Database schema that is not created
The default database schema is "dbo" in the Data Export Service Wizard. If you change this to something else like "crm" and you haven't created this in the database, you will get an error. It is simple to fix, you just have to go into the database and create the schema. To create the schema "crm" open a query and run:
CREATE SCHEMA crm
For more information on how to create schemas, check this site: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-schema-transact-sql?view=sql-server-2017
Once the schema has been created, there should be no problem using it, as long as the user has permissions using it.
I hope this works for you. If you have any questions, don't hesitate to leave a comment.
MVP, Founder and Principal Consultant at CRM-konsulterna AB