none
Can't connect to SQL server with Windows Authentication mode RRS feed

  • Question

  • Hi all, I am working on writing an Azure automation runbook to connect to a SQL server. Here is my sample code

            

    $Conn = New-Object System.Data.SqlClient.SqlConnection("Server = XXXXXX; Database = XXXX; Integrated Security = True") 

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

    $Ds=New-Object system.Data.DataSet
    $Da=New-Object system.Data.SqlClient.SqlDataAdapter($Cmd)
    [void]$Da.fill($Ds)

    # Output the count
    $Ds.Tables.Column1
    $Conn.Close()

    It returned an error about can't connect to the SQL server and the server authentication is Windows Authentication mode only. So I can't use username and password to connect to it. Please let me know if there is anyway to connect to it. Any help will be appreciated. Thanks.


    Tuesday, February 27, 2018 11:38 PM

Answers

  • Problem solved. I am using Windows Hybrid Runbook Worker then I can use Azure automation runbook to connect to SQL server which set as Windows Authentication mode only. Here is the link:

    https://docs.microsoft.com/en-us/azure/automation/automation-windows-hrw-install

    • Marked as answer by ShifengWu Wednesday, February 28, 2018 9:58 PM
    Wednesday, February 28, 2018 9:58 PM

All replies

  • Try{
        $conn = New-Object System.Data.SqlClient.SqlConnection("Server = XXXXXX; Database = XXXX; Integrated Security = True") 
        $conn.Open()
        $cmd = $conn.CreateCommand()
        $cmd.CommandTimeout=120
    
        $cmd.CommandText = 'SELECT COUNT(*) from XXXX'
        $rdr = $cmd.ExecuteReader()
        $dt=New-Object system.Data.DataTable
        $dt.Load($rdr)
        $Conn.Close()
        $dt
    }
    Catch{
        Throw $_
    }
    


    \_(ツ)_/

    Tuesday, February 27, 2018 11:57 PM
  • Thanks for the help, but it doesn't work. By the way I run this script in local PowerShell is fine but not work in Azure Automation runbook. Please let me know what I can do. Thank you so much.
    Wednesday, February 28, 2018 12:52 AM
  • You have to tell us the complete error.

    Azure runbook will not necessarily connect with SSPI.  The runbook has to be under an account that has permissions and the runbook source has to have connect permissijns to SQLServer.


    \_(ツ)_/

    Wednesday, February 28, 2018 12:55 AM
  • Post here for help with Azure SQLServer.

    https://social.technet.microsoft.com/Forums/en-US/home?forum=ssdsgetstarted


    \_(ツ)_/

    Wednesday, February 28, 2018 1:05 AM
  • Thank for your reply. I am new in Azure runbook and can you please tell me what permission I should have? The runbook under an Azure subscription which same as the  subscription of azure SQL server and I am the owner of this subscription. Is it good enough to connect them together? If it is good enough, how I should write the PowerShell script to connect the SQL server? Thanks. 
    Wednesday, February 28, 2018 1:41 AM
  • Please ask your questions in the forum linked above.  What you are trying to do cannot be done the way you are trying to do it if it can be done at all.


    \_(ツ)_/

    Wednesday, February 28, 2018 2:54 AM
  • Problem solved. I am using Windows Hybrid Runbook Worker then I can use Azure automation runbook to connect to SQL server which set as Windows Authentication mode only. Here is the link:

    https://docs.microsoft.com/en-us/azure/automation/automation-windows-hrw-install

    • Marked as answer by ShifengWu Wednesday, February 28, 2018 9:58 PM
    Wednesday, February 28, 2018 9:58 PM
  • Very good.  You can see that this is not a PowerShell issue.  It is a custom deployment issue for a runbook in a hybrid environment.

    You should always post these kinds of questions bout Azure in the Azure foums first.  You are more likely to find others who have knowledge of this inan Azure environment.

    The link you posted is excellent.  It is better than any I could find in 5 minutes mostly because I didn't think of adding the word "hybrid".


    \_(ツ)_/

    Wednesday, February 28, 2018 10:04 PM