[Bug] Copy-DbaLogin fails on Azure

[hbuckle]

Environmental information

#### PowerShell version:

Name                           Value                                                                                                                                           
----                           -----                                                                                                                                           
PSVersion                      5.1.18362.145                                                                                                                                   
PSEdition                      Desktop                                                                                                                                         
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}                                                                                                                         
BuildVersion                   10.0.18362.145                                                                                                                                  
CLRVersion                     4.0.30319.42000                                                                                                                                 
WSManStackVersion              3.0                                                                                                                                             
PSRemotingProtocolVersion      2.3                                                                                                                                             
SerializationVersion           1.1.0.1                                                                                                                                         

#### dbatools Module version:

Name    : dbatools
Path    : C:\Users\henry.buckle\Documents\WindowsPowerShell\Modules\dbatools\1.0.83\dbatools.psd1
Version : 1.0.83

SQL Server:

Microsoft SQL Azure (RTM) - 12.0.2000.8     Dec  4 2019 21:24:18    Copyright (C) 2019 Microsoft Corporation

Report

Copy-DbaLogin fails on an Azure Sql Server Looking at the code it's only checking for versions 0,8,9 and the default command does not work on Azure (sys.server_principals is not supported)

Host used

• [x ] powershell.exe

Errors Received

Copy-DbaLogin -Source server1.database.windows.net -Destination server2.database.windows.net -SourceSqlCredential $cred -DestinationSqlCredential $cred -Login testy -Verbose
VERBOSE: [15:07:40][Connect-DbaInstance] Data Source=TCP:server1.database.windows.net,1433;User
ID=azureuser;Password=xxx;MultipleActiveResultSets=False;Connect Timeout=30;Encrypt=True;TrustServerCertificate=False;Application Name="dbatools PowerShell module -  dbatools.io"
VERBOSE: [15:07:40][Connect-DbaInstance] Connecting to Azure: server1.database.windows.net
VERBOSE: [15:07:41][Connect-DbaInstance] Data Source=TCP:server2.database.windows.net,1433;User
ID=azureuser;Password=xxx;MultipleActiveResultSets=False;Connect Timeout=30;Encrypt=True;TrustServerCertificate=False;Application Name="dbatools PowerShell module -  dbatools.io"
VERBOSE: [15:07:41][Connect-DbaInstance] Connecting to Azure: server2.database.windows.net
VERBOSE: [15:07:41][Copy-DbaLogin] Attempting Login Migration.
VERBOSE: [15:07:41][Copy-Login] server2.database.windows.net does not have Mixed Mode enabled. [testy] is an SQL Login. Enable mixed mode authentication after the migration    completes to use this type of login.
VERBOSE: Performing the operation "Adding SQL login testy" on target "server2.database.windows.net".
VERBOSE: [15:07:41][Copy-Login] Attempting to add testy to server2.database.windows.net.
VERBOSE: [15:07:41][Copy-Login] Setting testy SID to source username SID.
VERBOSE: [15:07:41][Copy-Login] Setting login language to us_english.
VERBOSE: [15:07:41][Copy-Login] Set testy defaultdb to master.
Exception calling "ExecuteWithResults" with "1" argument(s): "Execute with results failed for Database 'master'. "
At C:\Users\henry.buckle\Documents\WindowsPowerShell\Modules\dbatools\1.0.83\allcommands.ps1:8165 char:29
+ ...             $hashedPassDt = $sourceServer.Databases['master'].Execute ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : FailedOperationException

You cannot call a method on a null-valued expression.
At C:\Users\henry.buckle\Documents\WindowsPowerShell\Modules\dbatools\1.0.83\allcommands.ps1:8169 char:29
+ ...                        if ($hashedPass.GetType().Name -ne "String") {
+                                ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Type             Name  Status Notes
----             ----  ------ -----
Login - SqlLogin testy Failed

[wsmelton]

We do not support copying logins on Azure SQL.

[wsmelton]

The Copy commands are meant for SQL Server migrations, primarily on-premises. At this time there are no plans to support migrating objects with Azure SQL DB.

[potatoqualitee]

This would be a whole new command - Copy-DbaDbContainedUser I believe

[potatoqualitee]

we should actually catch that error prior if possible, too. perhaps we should have an azureopt-in instead of an azure opt out like we have now?

[hbuckle]

Just to be clear, it is server logins I want to copy, not contained database users. It's required for failover groups I believe the statement in case 9 should work, it just needs to be applied to versionMajor 15

[potatoqualitee]

ahh we do it a different way for the older ones. we will accept a PR if you'd liek to make it.

[tkwj]

I looked into fixing this bug but there are further issues than the system view being used to fetch the login.

• Azure SQL DB does not support the PASSWORD option "HASHED", it only accepts the actual password as input. *Other options used here (DEFAULT_DATABASE, CHECK_POLICY, CHECK_EXPIRATION, DEFAULT_LANGUAGE) are also unsupported.

There is no workaround that fits the intent of "Copy-DbaLogin", so perhaps the AzureUnsupported flag should be added to make this clear.

Reference: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-login-transact-sql?view=azuresqldb-current

[andreasjordan]

Ok, I will open a PR to add the AzureUnsupported flag and then close this.