3

I need to connect via Azure Automation to an Azure SQL Server using my Azure Active Directory Admin account that is set as the Azure SQL Server AZ AD Admin.

I am able to connect to Azure SQL:

  1. Using SSMS with the Azure AD Admin Account
  2. Using the PowerShell ISE with the Azure AD Admin Account in a SQL ConnectionString
  3. Using Azure Automation with the Azure SQL Admin account (the one created when a new Azure SQL Server is created) in a SQL ConnectionString

However, when attempting to connect to Azure SQL in Azure Automation using the Active Directory Admin account in Azure Automation in a SQL ConnectionString, I get the following error:

New-Object : Exception calling ".ctor" with "1" argument(s): "Keyword not supported: 'authentication'."

Here is my connection attempt:

$server = "tcp:myazuresql.database.windows.net,1433"
$database = "TestDB"
$adminName = "test@mytest.onmicrosoft.com"
$adminPassword = "test1234"

$connectionString = "Server=$server;Database=$database;User ID=$adminName;Password=$adminPassword;authentication=Active Directory Password;"
$connection = New-Object -TypeName System.Data.SqlClient.SqlConnection($connectionString)

Any ideas on why I can connect via PowerShell ISE and SSMS but not Azure Automation with the Azure Active Directory Admin? I can also connect via Azure Automation and the Azure SQL Admin account (the default admin account you create with Azure SQL).

The only way I can't connect is when using the Azure Active Directory Admin tied to Azure SQL when using Azure Automation.

Kode
  • 3,073
  • 18
  • 74
  • 140
  • Are you using the Import-Module AzureAutomationAuthoringToolkit? – Zach Olinske Jul 13 '17 at 21:49
  • I am using Azure Automation without importing the module. What does that module do? Executing the same code in Powershell ISE works without issue – Kode Jul 13 '17 at 21:52
  • 1
    You can read about the Module here: https://www.powershellgallery.com/packages/AzureAutomationAuthoringToolkit/0.2.3.8 I have only done it using this way. I will provide the code with your info. – Zach Olinske Jul 13 '17 at 21:55

3 Answers3

3

Using Azure AD to connect to SQL is not yet supported with Azure Automation Account.This feature requires .NET Framework 4.6 and currently Azure Automation workers only had .NET Framework 4.5.

Suggestion:

Thamarai
  • 76
  • 5
1

Using the Azure Automation Module

   ## Using Azure Automation ISE Add-on
    #Install-Module -Name AzureAutomationAuthoringToolkit
    Import-Module AzureAutomationAuthoringToolkit
    $SqlServer = "myazuresql.database.windows.net"
    $SqlServerPort = "1433"
    $Database = "TestDB"
    $Table = ""
    $SqlCredentialAsset = ""
    $SqlCredential = Get-AutomationPSCredential -Name $SqlCredentialAsset 
    if ($SqlCredential -eq $null) 
        { 
            throw "Could not retrieve '$SqlCredentialAsset' credential asset. Check that you created this first in the Automation service." 
        }   
    $SqlUsername = $SqlCredential.UserName 
    $SqlPass = $SqlCredential.GetNetworkCredential().Password 
    $Conn = New-Object System.Data.SqlClient.SqlConnection("Server=tcp:$SqlServer,$SqlServerPort;Database=$Database;User ID=$SqlUsername;Password=$SqlPass;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;")

    $Conn.Open() 
    $Cmd=new-object system.Data.SqlClient.SqlCommand("SELECT COUNT(*) from $Table", $Conn) 
    $Cmd.CommandTimeout=120 
    $Conn.Close()

Code for inside RunBook

#Runbook
Param
(
[Parameter(Mandatory=$true)]
[String]
$AureConnectionName
)

$AzureConn = Get-AutomationConnection -Name $AzureConnectionName

If ($AuzreConn -eq $null)
{
    throw "Could not retrieve '$SqlCredentialAsset' credential asset."
}
$Certificate = Get-AutomationCertificate -Name $AzureConn.AutomationCertificateName

if ($Certificate -eq $null)
{
 throw "Could not retrieve '$AzureConn.AutomationCertificateName' certificate asset." 
}

$cred = Get-Credential -Credential Domain\User
Login-AzureRmAccount -Credential $cred
Get-AzureRmSubscription | Select-AzureRmSubscription
Zach Olinske
  • 517
  • 2
  • 14
  • The issue I am running into is that without designating the Authentication type of "Active Directory Password" it doesn't work. Using this it works in PowerShell ISE but not the actual Azure Automation Runbook executed from Azure Automation – Kode Jul 13 '17 at 22:12
  • Using the Runbook Azure Automation is hard for me to explain. You have to create AutomationCertificate, and then do what you would do in PowerShell ISE. I haven't touched this method since RM. It was only in Azure Classic. – Zach Olinske Jul 13 '17 at 23:01
  • Appreciate the attempt. I am using the AZ Auto GUI and it's strange that the code works in PowerShell ISE and not Azure Automation proper – Kode Jul 13 '17 at 23:05
  • What I remember is that you have to create a certificate, but i will add some more code to the answer I provided for Runbook. – Zach Olinske Jul 13 '17 at 23:22
  • Thanks Zach. It's not the AZ Auto connection but trying to connect to SQL Server from Azure Automation using the Azure Active Directory Admin. I can connect via Azure Automation using the admin account, just not the Active Directory Admin account (there are two). That is why I added the Authentication = Active Directory Password to the SQL string (which works in PowerShell ISE but not Azure Automation) – Kode Jul 14 '17 at 02:21
1

Please refer to this similar question.

If you want to connect SQL server with Azure AD user, ADAL SQL library should install on your VM. Now, Azure automation account does not install library. If you want use Azure AD user login your SQL server, you could select hybrid workers.

Runbooks in Azure Automation cannot access resources in your local data center since they run in the Azure cloud. The Hybrid Runbook Worker feature of Azure Automation allows you to run runbooks on machines located in your data center to manage local resources. The runbooks are stored and managed in Azure Automation and then delivered to one or more on-premises machines.

Shui shengbao
  • 18,746
  • 3
  • 27
  • 45
  • Hi Walter. I am logging into Azure SQL, not SQL hosted in a VM. I am able to login using the Azure SQL Admin and Azure Automation, just not the Azure Active Directory Admin that you can set with Azure SQL from Azure Automation. I am able to login from PowerShell ISE with the Azure Active Directory account set as the admin on my Azure SQL Server (PaaS not an VM). – Kode Jul 14 '17 at 02:59
  • 1
    @Kode Based on my knowledge, for now, it is not possible to login SQL server with Azure AD user with runbook. Because it needs ADAL SQL library, but automation does not install it. – Shui shengbao Jul 14 '17 at 03:01
  • That's good to know. Any clue why it works from PowerShell ISE? – Kode Jul 14 '17 at 03:03
  • Or is there a module we can add to Azure Automation for the ADAL SQL Library? – Kode Jul 14 '17 at 03:04
  • 1
    On your local, you could install the library on your PC and test again. On automation account, I also don't know how to do it. Maybe you could create a new module and import to Azure automation account. – Shui shengbao Jul 14 '17 at 03:05
  • @Kode You could check download link. `The Active Directory Authentication Library for SQL Server is a single dynamic-link library (DLL) containing run-time support for applications authenticating to Microsoft Azure SQL Database using Azure Active Directory. The Active Directory Authentication Library for SQL Server should only be used in conjunction with a SQL Server driver that supports Azure Active Directory authentication. Developing applications that directly call the Active Directory Authentication Library for SQL Server is not supported.` – Shui shengbao Jul 14 '17 at 03:07
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/149185/discussion-between-walter-msft-and-kode). – Shui shengbao Jul 14 '17 at 03:09