none
Powershell Azure SQL connection RRS feed

  • Question

  • Hi,

    I am able to connect to my Azure SQL using SSMS using SQL authentication, is there a way to connect to SQL from PS using sql authentication?

    Using PS 5.1 and I have Azure module installed and imported

    Install-Module Azure -AllowClobber
    Import-Module Azure

    I am also able to authenticate (Add-AzureAccount) using my ad.onmicrosoft credentials and get access to all databases. 

    Get-AzureSqlDatabase -ServerName myservername

    Thanks 

    Wednesday, September 11, 2019 2:00 PM

All replies

  • Hi Net_Tech,

    The following Blog discusses how you can interactively and non-interactively pass SQL Database authentication in a Powershell session. Please see step 5: Windows Azure SQL Database Management with PowerShell

    You can use the Get-Credential cmdlet and use the input for a variable.

    Please let us know if you have additional questions.

    Regards,

    Mike

    Wednesday, September 11, 2019 5:18 PM
    Moderator
  • Did you get this resolved? Please let us know if you have additional questions or need assistance.

    Thank you!

    Tuesday, September 17, 2019 1:48 AM
    Moderator
  • no, I was not able to connect using the method suggested in the earlier post. 

    Wouldn't this be a better way to accomplish what I am trying to do?

    https://techcommunity.microsoft.com/t5/Azure-Database-Support-Blog/Azure-SQL-Database-Token-based-authentication-with-PowerShell/ba-p/369078 ?

    • Edited by net_tech Tuesday, September 17, 2019 6:11 PM
    Tuesday, September 17, 2019 1:54 AM
  • Hi net_tech,

    I may have misunderstood your question but if you want to use Azure AD authentication within a PowerShell environment, the doc you provided (Azure SQL Database Token-based authentication with PowerShell) explains how to accomplish this. My understanding from your question is that you wanted to use SQL Authetication which is not an Azure AD authentication type. SQL Authentication is the auth type of using a username and password from the CREATE LOGIN T-SQL.

    In the below image, the green arrow indicates authentication created only within Azure SQL Database. The red box indicates an authenitcation method using Azure Active Directory and Managed Identidies that are added to the Azure SQL Database you are wanting to grant a user access to. 

    You are likely wanting to use Active Directory - Integrated or Active Directory - Universal with MFA Support, and the Token Based Authenitcation would be supported. 

    Please let me know if you have additional questions.

    Regards,

    Mike

    Wednesday, September 18, 2019 1:46 AM
    Moderator
  • Sorry if I was not clear in my original post but it looks like Token Based Authenticationis the only way to connect from an automated powershell script without MFA which is enabled on all accounts. 


    • Edited by net_tech Wednesday, September 18, 2019 1:57 AM
    Wednesday, September 18, 2019 1:55 AM