Mapping Azure files share for Azure SQL Server IaaS

-

As mention in a previous post: A Gotcha when using the Azure SQL Server IaaS.  I am working on moving our company product to the Azure Cloud and having to use Azure SQL Server IaaS.

In this post, I will share how I used the Azure VM Custom Script Extensions to map a drive to Azure file share accessible for SQL Server services.

I couldn’t use the Azure portal script: 

1
2
3
4
5
6
7
8
9
$connectTestResult = Test-NetConnection -ComputerName mystorageaccount.file.core.windows.net -Port 445
if ($connectTestResult.TcpTestSucceeded) {
    # Save the password so the drive will persist on reboot
    cmd.exe /C "cmdkey /add:`"mystorageaccount.file.core.windows.net`" /user:`"Azure\mystorageaccount`" /pass:`"mystorageaccount.file.core.windows.net key `""
    # Mount the drive
    New-PSDrive -Name Z -PSProvider FileSystem -Root "\\mystorageaccount.file.core.windows.net\myfileshare" -Persist
} else {
    Write-Error -Message "Unable to reach the Azure storage account via port 445. Check to make sure your organization or ISP is not blocking port 445, or use Azure P2S VPN, Azure S2S VPN, or Express Route to tunnel SMB traffic over a different port."
}

The reason is the storage account key is stored in the windows credential manager (cmdkey) of the executing user.  I need this shared to be accessible by the SQL Server services.  While searching for a different method to mount a share, I came across this command: New-SmbGlobalMapping.  This function can create an SMB mapping to a drive, with the option to persist, and grant access for list of users.

As the script should be idempotent, I need to find a way to test to see if the mapping already exists and found the matching Get-SmbGlobalMapping and Remove-SmbGlobalMapping.  I was able to extend the portal script to use SmbGlobalMapping command lets.  Here is an example of my custom script:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
#Setting update Azure File Share.
param
(
    [string] $PassKey
)

$AzureUserName = "Azure\mystorageaccount" 
$StoreageAccountName = "mystorageaccount" 
$FileshareName = "myfileshare" 
$FileShareURI = Join-Path -Path ("\\{0}" -f $StoreageAccountName) -ChildPath $FileshareName
$password = ConvertTo-SecureString -String $PassKey -AsPlainText -Force
$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $AzureUserName, $password

#Test that a connection can be made
$connectTestResult = Test-NetConnection -ComputerName $StoreageAccountName -Port 445
if ($connectTestResult.TcpTestSucceeded) {
#See if a SMBGlobalMapping has already made    
    $Mapping = Get-SmbGlobalMapping -RemotePath $FileShareURI

#If there a mapping then remove it
    if($null -ne $Mapping )
    {    
        Remove-SmbGlobalMapping -RemotePath $FileShareURI -Force
    }
#Create a persistent SMB mapping for the all the services 
    New-SmbGlobalMapping -RemotePath $FileShareURI -Credential $cred -LocalPath Z: -FullAccess @( "NT AUTHORITY\SYSTEM", "NT AUTHORITY\NetworkService", "NT Service\MSSQLSERVER","NT Service\MsDtsServer150","NT Service\SQLSERVERAGENT", "$env:COMPUTERNAME\localVmAdmin") -Persistent $true

} 
else {
#write a error message.
    Write-Error -Message "Unable to reach the Azure storage account via port 445. Check to make sure your organization or ISP is not blocking port 445, or use Azure P2S VPN, Azure S2S VPN, or Express Route to tunnel SMB traffic over a different port."
}

The next challenge was to supply the storage key to the custom script.  There are no examples of passing parameters to a script.  I noticed that the script(s) are executed using the PowerShell command-line interface.

So all I had to was extend the value to the property: commandToExecute to include my parameter name and enclose the value within double-quotes.

Here is an example of my command property: 

"commandToExecute": "[concat ('powershell -ExecutionPolicy Unrestricted -File ', variables('firstFileName'), ' -EnviromentName', ' \"', parameters('EnvironmentName'), '\" ', '-PassKey', ' \"',listKeys(variables('AzureStorageAccountId'),variables('AzureStorageAccountApiVersion')).keys[0].value,'\"')]"

After using the script and setting value for commandToExecute for the custom script.  After provisioning Azure SQL Server with ARM template and copied the SQL Server back-up files to file share.  I was able to restore all the database from the mapped drive directly.


Comments

Post a Comment

Popular posts from this blog

Azure SQL Server IaaS SSIS: A required privilege is not held by the client

-
Image
As mention in a previous post, we are using Azure SQL Server IaaS to help our transition to the cloud. The main reason for this is how some of our SSIS projects, which we inherited, were created. Some were using SSIS to manage the transactions, which uses MSDTC not supported in Azure. Use extra .Net libraries to read JSON and office files; external programs to do file operations like decompression. Migration timelines and priorities we didn't have time to refactor these projects. While ago we started to deploy our SSIS projects to our Azure SQL Server IaaS virtual machine, a Windows 2019 server with SQL Server 2019, to get the following error: SSIS Deployment Error 6522 "A required privilege is not held by the client". We able to resolve our problem by using the steps we found in the following blog posts: System.ComponentModel.Win32Exception: A required privilege is not held by the client while Deploying SSIS Project | Microsoft Docs . Fixing SQL 2012 SSIS Deployment Err
Read more

SSIS File handle leak in For Each Loop

-
I am currently working on a project which needs to load over a 1000 xml files. The files are stored across 10 subfolders. I am using a foreach loop with a file enumerator, which is configured at the top of the folder structure and traverses the subfolders. This loops through the files, load the data and then moves the file to another folder. The package executes fine for a few 100 files but then hangs; this happens after a different number of processed files each time the package is run. While trying to resolve the problem we ran performance counters and noticed that the number open handles increased significantly just about the time Dtexec looked like it had hanged and DTexec also then started taking a lot of the cpu processing time. Update: I put this on the Microsoft forums and got a intresting answer back See Here ; There seems to be a memory leak with foreach loop which should be fixed in sp1.
Read more

Cannot update a database that has been registered as a data-tier application

-
Within SQL Server 2008 R2 a new feature was introduced, which was the data-tier application database. This feature allow developers / database administrators to package a database schema so that it could be deployed to any other database server and versioned. More about this feature can be found here: http://msdn.microsoft.com/en-us/library/ee240739(v=sql.105).aspx or http://dacguy.wordpress.com/ This feature has been included, as I have just found out, to previous versions: SQL Server 2005 via Services Pack 4 and 2008 via Services Pack 2. However SQL Server Management Studio, for SQL Server 2008, hasn't been updated with the tools for the new feature, you have to use the SQL Server 2008 R2 version. The reason for including the support to the previous version might have something to do with the release of the new SQL Server Data Tools (SSDT), the replacement to visual studio database professional, which uses DAC Packs and the framework to deploy a database. I have been using the
Read more