locked
SMO + Azure Data warehouse - how to connect? RRS feed

  • Question

  • does anyone have an example of how to use SMO to connect to azure data warehouse using AD?

    my understanding is that smo uses AD by default, so $server = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "myserver.database.windows.net" should work but i get a cant connect to server error after a 20sec delay

    The following exception occurred while trying to enumerate the collection: "Failed to connect to server
    myserver.database.windows.net.".

    i know when connecting with SSMS i need to select "azure active directory - integrated" from the authentication dropdown 

    but i couldnt find any examples of how to switch this with smo or create a custom ServerConnection object that works with azure DW


    Jakub @ Adelaide, Australia Blog

    Thursday, July 16, 2020 5:25 AM

Answers

  • figured it out by trawling random github repos

    [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
    [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") #need this for Microsoft.SqlServer.Management.Common.ServerConnection


    # Connect to your database (Azure SQL database).
    $serverName = "<your server>"

    $connStr = "Server = " + $serverName + "; Authentication = Active Directory Integrated"
    $connection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection
    $connection.ConnectionString = $connStr
    $connection.Connect()
    $server = New-Object Microsoft.SqlServer.Management.Smo.Server($connection)
    $server.databases.name

    trying to find doco (none exists) or the right library that let me build a azure connection string for the smo.server connection was terrible


    Jakub @ Adelaide, Australia Blog

    • Marked as answer by jakubk Friday, July 17, 2020 5:06 AM
    Friday, July 17, 2020 5:06 AM

All replies

  • You can lookup the correct connection string in your Azure portal.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, July 16, 2020 7:10 AM
  • i cant see the one for SMO in there, just the regular odbc/jdbc/.net stuff

    tried it and got the failed connection straight away

    The following exception occurred while trying to enumerate the collection: "Failed to connect to server
    Server=tcp:myserver.database.windows.net,1433;Initial Catalog=mydb;Persist Security Info=False;User
    ID=myuser;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Authentication="Active Directory
    Integrated";.".


    Jakub @ Adelaide, Australia Blog

    Friday, July 17, 2020 12:50 AM
  • figured it out by trawling random github repos

    [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
    [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") #need this for Microsoft.SqlServer.Management.Common.ServerConnection


    # Connect to your database (Azure SQL database).
    $serverName = "<your server>"

    $connStr = "Server = " + $serverName + "; Authentication = Active Directory Integrated"
    $connection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection
    $connection.ConnectionString = $connStr
    $connection.Connect()
    $server = New-Object Microsoft.SqlServer.Management.Smo.Server($connection)
    $server.databases.name

    trying to find doco (none exists) or the right library that let me build a azure connection string for the smo.server connection was terrible


    Jakub @ Adelaide, Australia Blog

    • Marked as answer by jakubk Friday, July 17, 2020 5:06 AM
    Friday, July 17, 2020 5:06 AM