Is there a way to log traffic to and from SQL Serv

Something strange is happening with a database server I had to rebuild and restore from backup.

I'm pointing an old VB6 application using ADODB.Connection and a modern C# EF6 application at it, using what should be the same connection string for both, of the form

servername\INSTANCE

When run on the same machine that's running SQL Server, the VB6 application and EF6 application are both able to connect using this connection string.

When run on a different machine on the network, the VB6 application connects, but the EF6 application doesn't.

(with a standard "server not found" message, error: 26 - Error Locating Server/Instance Specified at System.Data.SqlClient.SqlInternalConnectionTds..ctor)

If I look at the specific instance port and connect with

servername,instance_port_number

then both applications connect, whatever machine I run them on. So it seems like something might be happening with SQL Server Browser to cause the issue.

Is there a way I can get some kind of diagnostic information out of SQL Server Browser, what data it's sent to where, without going as far as to monitor all network traffic?

回答1:

An alternative to a network trace for troubleshooting is to send an instance enumeration query to the SQL Server Browser service and examine the results. This will verify the SQL Server Browser is reachable over UDP port 1434 and that the returned datagram contains the instance name and port information needed for the client to connect to a named instance.

Run the PowerShell script below on the problem machine.

# verify UDP port 1433 connectivity and query SQL Server Browser for all instances
Function Get-SqlServerBrowerDatagramForAllInstances($hostNameOrIpAddress)
{
    Write-Host "Querying SQL Browser for all instances on host $hostNameOrIpAddress ..."

    try
    {
        $udpClient = New-Object Net.Sockets.UdpClient($hostNameOrIpAddress, 1434)
        $bufferLength = 1
        $browserQueryMessage = New-Object byte[] 1
        $browserQueryMessage[0] = 2
        $bytesSent = $udpClient.Send($browserQueryMessage, $browserQueryMessage.Length)
        $udpClient.Client.ReceiveTimeout = 10000
        $remoteEndPoint = New-Object System.Net.IPEndPoint([System.Net.IPAddress]::Broadcast, 0)
        $browserResponse = $udpClient.Receive([ref]$remoteEndPoint)
        $payloadLength = $browserResponse.Length - 3
        $browserResponseString = [System.Text.ASCIIEncoding]::ASCII.GetString($browserResponse, 3, $payloadLength)
        $elements = $browserResponseString.Split(";")

        Write-Host "SQL Server Browser query results:`r`n"

        for($i = 0; $i -lt $elements.Length; $i = $i + 2)
        {
            if ($elements[$i] -ne "")
            {
                Write-Host "`t$($elements[$i])=$($elements[$i+1])"
            }
            else
            {
                Write-Host ""
                # next instance
                $i = $i - 1
            }
        }
    }
    catch [Exception]
    {
        Write-Host "ERROR: $($_.Exception.Message)" -ForegroundColor Red
    }
}

# verify UDP port 1433 connectivity and query SQL Server Browser for single instance
Function Get-SqlServerBrowerDatagramByInstanceName($hostNameOrIpAddress, $instanceName)
{
    Write-Host "Querying SQL Browser for host $hostNameOrIpAddress, instance $instanceName ..."

    try
    {
        $instanceNameBytes = [System.Text.Encoding]::ASCII.GetBytes($instanceName)
        $udpClient = New-Object Net.Sockets.UdpClient($hostNameOrIpAddress, 1434)
        $bufferLength = $InstanceNameBytes.Length + 2
        $browserQueryMessage = New-Object byte[] $bufferLength
        $browserQueryMessage[0] = 4
        $instanceNameBytes.CopyTo($browserQueryMessage, 1)
        $browserQueryMessage[$bufferLength-1] = 0
        $bytesSent = $udpClient.Send($browserQueryMessage, $browserQueryMessage.Length)
        $udpClient.Client.ReceiveTimeout = 10000
        $remoteEndPoint = New-Object System.Net.IPEndPoint([System.Net.IPAddress]::Broadcast, 0)
        $browserResponse = $udpClient.Receive([ref]$remoteEndPoint)
        $payloadLength = $browserResponse.Length - 3
        $browserResponseString = [System.Text.ASCIIEncoding]::ASCII.GetString($browserResponse, 3, $payloadLength)
        $elements = $browserResponseString.Split(";")
        $namedInstancePort = ""
        Write-Host  "SQL Server Browser query results:`r`n"
        for($i = 0; $i -lt $elements.Length; $i = $i + 2)
        {
            if ($elements[$i] -ne "")
            {
                Write-Host  "`t$($elements[$i])=$($elements[$i+1])"
                if($elements[$i] -eq "tcp")
                {
                    $namedInstancePort = $elements[$i+1]
                }
            }
        }

    }
    catch [Exception]
    {
        Write-Host "ERROR: $($_.Exception.Message)" -ForegroundColor Red
    }

}


Get-SqlServerBrowerDatagramForAllInstances -hostNameOrIpAddress "servername"

Get-SqlServerBrowerDatagramByInstanceName -hostNameOrIpAddress "servername" -instanceName "INSTANCE"

回答2:

In entity framework 6 you can take the dbcontext object and do something like. Yourcontext.Database.log = s => mylogger.Debug(s);

The right hand side is a lambda function that takes a string s and logs it.

All of the sql and parameters get logged.