Lesson Learned #228: Using sp_validatelogins in Azure SQL Database

[Jose_Manuel_Jurado]

Today, we worked on a service request that our customer needs to use sp_validatelogins to verify if the user exists or not in their windows environment. 

Right now this function doesn't work in Azure SQL Database returning the following error message: Msg 2812, Level 16, State 62, Line 10
Could not find stored procedure 'sp_validatelogins'. In this article I would like to share a possible workaround.

Well, the first thing that I found was, what is the TSQL script behing this function, for this reason, running sp_helptext 'sp_validatelogins' I saw the following content.

create procedure sys.sp_validatelogins
AS
	-- Must be securityadmin (or sysadmin) to execute
	if is_srvrolemember('securityadmin') = 0 and is_srvrolemember('sysadmin') = 0
	begin
		raiserror(15247,-1,-1)
		return 1
	end

	-- Use get_sid() to determine if nt name is still valid (builtin is only available from system procs!)
	select 'SID' = sid, 'NT Login' = loginname from master.dbo.syslogins
		where isntname = 1 and get_sid(loginname) is null
	return 0 -- sp_validatelogins

I found that dbo.syslogins is an invalid object name in Azure SQL Database, so, I need to obtain the users in another way. In this case, I'm going to use the DMV per database called  select * from sys.database_principals where type IN ( 'X','E'), this DMV will filter all the AAD groups and users to validate. 

Finally, using Get-AzADUser to find if the user or group exists. In my proof of concept, I wrote this PowerShell script that connecting to any user database, reads all the AAD users and groups and check if exists or not.

Connect-AzAccount -Subscription "xxxxxx-xxx-xxx-xxx-xxxxx"


$server = "servername.database.windows.net" #ServerName parameter to connect,for example, myserver.database.windows.net
$user = "username" #UserName parameter  to connect
$password = "password" #Password Parameter  to connect
$Db = "databasename" #DBName Parameter  to connect. 


#----------------------------------------------------------------
#Function to connect to the database using a retry-logic
#----------------------------------------------------------------

Function GiveMeConnectionSource()
{ 
  for ($i=1; $i -lt 10; $i++)
  {
   try
    {
      logMsg( "Connecting to the database..." + $DB + ". Attempt #" + $i) (1)
      $SQLConnection = New-Object System.Data.SqlClient.SqlConnection 
      $SQLConnection.ConnectionString = "Server="+$server+";Database="+$Db+";User ID="+$user+";Password="+$password+";Connection Timeout=60;Application Name=PerfCollector" 
      $SQLConnection.Open()
      logMsg("Connected to the database.." + $DB) (1)
      return $SQLConnection
      break;
    }
  catch
   {
    logMsg("Not able to connect - " + $DB + " - Retrying the connection..." + $Error[0].Exception) (2)
    Start-Sleep -s 5
   }
  }
}

function bExistAADLoginUser($Name,$Type)
{

  If($Type -eq 'E' ) 
  {
   logMsg("Checking the User...") (3)
   $Data = Get-AzADUser -Mail $Name
  }

  If($Type -eq 'X' ) 
  {
   logMsg("Checking the Group...") (3)
   $Data = Get-AzADUser -DisplayName $Name
  }
  return $Data.Count
}

#--------------------------------
#Log the operations
#--------------------------------
function logMsg
{
    Param
    (
         [Parameter(Mandatory=$true, Position=0)]
         [string] $msg,
         [Parameter(Mandatory=$false, Position=1)]
         [int] $Color,
         [Parameter(Mandatory=$false, Position=2)]
         [boolean] $Show=$true, 
         [Parameter(Mandatory=$false, Position=3)]
         [boolean] $bShowDate=$true

    )
  try
   {
    if($bShowDate -eq $true)
    {
      $Fecha = Get-Date -format "yyyy-MM-dd HH:mm:ss"
      $msg = $Fecha + " " + $msg
    }
    
    $Colores="White"
    $BackGround = 
    If($Color -eq 1 )
     {
      $Colores ="Cyan"
     }
    If($Color -eq 3 )
     {
      $Colores ="Yellow"
     }

     if($Color -eq 2 -And $Show -eq $true)
      {
        Write-Host -ForegroundColor White -BackgroundColor Red $msg 
      } 
     else 
      {
       if($Show -eq $true)
       {
        Write-Host -ForegroundColor $Colores $msg 
       }
      } 


   }
  catch
  {
    Write-Host $msg 
  }
}


#--------------------------------
#Validate Param
#--------------------------------
function TestEmpty($s)
{
if ([string]::IsNullOrWhitespace($s))
  {
    return $true;
  }
else
  {
    return $false;
  }
}


try
{
Clear

logMsg("-------------------- Header Filter details --------------") (1)
logMsg("  ServerName:           " + $server) (1)
logMsg("  DB:                   " + $DB) (1)
logMsg("-------------------- Footer Filter details --------------") (1)

$SQLConnectionSource = GiveMeConnectionSource
if($SQLConnectionSource -eq $null)
    { 
     logMsg("It is not possible to connect to the database") (2)
     exit;
    }
   $commandDB = New-Object -TypeName System.Data.SqlClient.SqlCommand
   $commandDB.CommandTimeout = 6000
   $commandDB.Connection=$SQLConnectionSource
   $commandDB.CommandText = "select name, type, principal_id from sys.database_principals where type IN ( 'X','E')"
      
   $ReaderDB = $commandDB.ExecuteReader(); 
   while($ReaderDB.Read())
   {
      logMsg("Checking the ADD object:" + $ReaderDB.GetValue(0).ToString() + " with PrincipalID: " + $ReaderDB.GetValue(2).ToString() ) (1)
      $Count = bExistAADLoginUser $ReaderDB.GetValue(0).ToString()  $ReaderDB.GetValue(1).ToString()
      If( $Count -eq 0 )
      {
        logMsg("The AAD Object doesn't exist:" + $ReaderDB.GetValue(0).ToString()) (2)
      }
   }

   $ReaderDB.Close();
   $SQLConnectionSource.Close() 

}
catch
  {
    logMsg("sp_validatelogins Script was executed incorrectly ..: " + $Error[0].Exception) (2)
  }
finally
{
   logMsg("sp_validatelogins Script finished - Check the previous status line to know if it was success or not") (2)
} 

Basically, executing in any user or master database in Azure SQL Database I was able to identify and obtain the same results of this function in On-Premises. 

Enjoy!