using PowerShell, Azure AD authenticaion, SMO, and Azure SQL DB?


  • How does one correctly connect to a Azure SQL DB using the latest SqlServer module, Azure AD authentication, and the SMO object. I'd like to be able to script login management items without having to open a SSMS session. ie: add / remove users to a role and enumerate existing user(s) in role(s).

    Our "database admin" accounts use a Azure AD group for authentication. The "New-PSDrive" method doesn't work and neither does "Invoke-SQLCmd" from what I can piece together. All the examples seem to use SQL authentication or the really old method of manually building a connection object.

    If I run:  cd SQLSERVER:\sql\\DEFAULT\Databases\DATABASE\

    I just get RPC errors and Windows logins aren't supported.

    miercuri, 13 iunie 2018 19:16

Toate mesajele

  • Is there a specific task (or set of tasks) you are looking to automate? I am going to include some REST API and PowerShell cmdlets that can be leveraged against Azure SQL Database.

    Azure SQL Database REST API

    Azure PowerShell samples for Azure SQL Database


    The below links cover the configuration for adding AAD authentication to Azure SQL Database with the 'PowerShell' link specific to PowerShell.

    Configure and manage Azure Active Directory authentication with SQL Database, Managed Instance, or SQL Data Warehouse


    Please let me know if you have any additional questions, or experience issues. Regards, Mike

    vineri, 15 iunie 2018 18:34
  • Thanks. I'm trying to get to the point where I can use a PowerShell script to run:


    or the equivalent for groups and/or delete users or groups, but I need my
    PowerShell script to authenticate using a Azure AD user. All the code samples I
    find are authenticating via a SQL user.

    Thanks for the links, but those are all about creating the database and then
    using SSMS to run the CREATE USER portions.

    The SMO objects work fine for connecting to local Windows auth SQL servers or
    for SQL authentication, but not for Windows auth against Azure SQL databases.

    Also, I realize that using a a SQLConnection object would probably work with a connection string, but I'd like to keep this purely in PowerShell and the "SQLServer" PS module and not have to make sure anyone running my script has to have SSMS or other dependencies installed.

    marți, 19 iunie 2018 17:46