locked
PowerShell Read/Write-SqlTableData fails connecting to Azure SQL Database RRS feed

  • Question

  • I'm having some difficulty using parts of the SqlServer module for powershell with my database. In this case I'm connecting to an Azure hosted SQL server. I have ruled out firewall, Invoke-SqlCmd works though I have also attempted this from an Azure VM on a VNET connected directly to the server.

    $user = 'script_user'
    $pass = ConvertTo-SecureString 'secret-here' -AsPlainText -Force
    $cred = New-Object System.Management.Automation.PsCredential($user, $pass)
    
    Invoke-Sqlcmd -ServerInstance "myserver.database.windows.net" -Database "my-db" -Credential $cred -Query 'SELECT * FROM my_table'
    # [THIS WORKS]
    
    Read-SqlTableData -ServerInstance "myserver.database.windows.net" -DatabaseName "my-db" -TableName "my_table" -Credential $cred
    # [ERROR]
    And here is the error:
    Read-SqlTableData : Failed to connect to server myserver.database.windows.net.
    At line:1 char:1
    + Read-SqlTableData -ServerInstance "myserver.database.windo ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : ObjectNotFound: (myserver.database.windows.net:String) [Read-SqlTableData], ConnectionFailureException
        + FullyQualifiedErrorId : ConnectionToServerFailed,Microsoft.SqlServer.Management.PowerShell.ReadSqlTableData

    Trying not to over-complicate things here. I'm struggling to find good examples of working scripts and any I do find result in similar errors. Any recommendations are appreciated.

    Thursday, June 13, 2019 1:26 PM

Answers

  • I'm having some difficulty using parts of the SqlServer module for powershell with my database. In this case I'm connecting to an Azure hosted SQL server. I have ruled out firewall, Invoke-SqlCmd works though I have also attempted this from an Azure VM on a VNET connected directly to the server.

    $user = 'script_user'
    $pass = ConvertTo-SecureString 'secret-here' -AsPlainText -Force
    $cred = New-Object System.Management.Automation.PsCredential($user, $pass)
    
    Invoke-Sqlcmd -ServerInstance "myserver.database.windows.net" -Database "my-db" -Credential $cred -Query 'SELECT * FROM my_table'
    # [THIS WORKS]
    
    Read-SqlTableData -ServerInstance "myserver.database.windows.net" -DatabaseName "my-db" -TableName "my_table" -Credential $cred
    # [ERROR]
    And here is the error:
    Read-SqlTableData : Failed to connect to server myserver.database.windows.net.
    At line:1 char:1
    + Read-SqlTableData -ServerInstance "myserver.database.windo ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : ObjectNotFound: (myserver.database.windows.net:String) [Read-SqlTableData], ConnectionFailureException
        + FullyQualifiedErrorId : ConnectionToServerFailed,Microsoft.SqlServer.Management.PowerShell.ReadSqlTableData

    Trying not to over-complicate things here. I'm struggling to find good examples of working scripts and any I do find result in similar errors. Any recommendations are appreciated.

    Good day Tarchannen,

    Use the following format:

    PS C:\> $sqlcc = new-object ('System.Data.SqlClient.SqlConnection') "Data Source='<use server name>.database.windows.net';Persist Security Info=True;User ID='<user name here>';Password='<password here>'"
    PS C:\> $sc = new-object ('Microsoft.SqlServer.Management.Common.ServerConnection') $sqlcc
    PS C:\> $srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $sc
    PS C:\> $db = $srv.Databases["database_name"]
    PS C:\> $table = $db.Tables["table_name"]
    PS C:\> Read-SqlTableData -TopN 10 -InputObject $table
    Don't forget to replace these to your real values: 
    <use server name>
    <user name here>
    <password here>
    database_name
    table_name


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]



    • Edited by pituachMVP Thursday, June 13, 2019 10:03 PM
    • Marked as answer by Tarchannen03 Friday, June 14, 2019 1:28 PM
    Thursday, June 13, 2019 9:59 PM