Tone Poem

Cannot Install Windows SDK :(

2012-06-27T16:11:27Z

You try to install latest Windows SDK and it does not work.

If you look at the setup log file and you see:

8:58:43 AM Wednesday, June 27, 2012: C:\WinSDK\Setup\SFX\vcredist_x64.exe installation failed with return code 5100

Reason for this is you have a newer C++ runtime installed on your machine, for example by Visual Studio 2012. The dumb Windows SDK installer will not install ANYTHING (even the simplest thing that does not depend on C runtime) if newer C++ runtime is installed.

Fix #1: Install Windows SDK on clean machine and copy files over (if you just want the tools like me).

Fix #2: Uninstall Microsoft Visual Studio C++ 2012 runtime (both x86 and x64 versions, then install Windows SDK

Windows Diagnostics with Windows Azure Issue

2012-06-27T15:48:00Z

Windows Azure Diagnostics does not work, you see following in the Compute Emulator =>

[Diagnostics]: Error starting diagnostics:
System.FormatException: Invalid account string.
    at Microsoft.WindowsAzure.CloudStorageAccount.<Parse>b__0(String err)
    at Microsoft.WindowsAzure.CloudStorageAccount.ParseStringIntoSettings(String s, Action`1 error)
    at Microsoft.WindowsAzure.CloudStorageAccount.TryParse(String s, CloudStorageAccount& accountInformation, Action`1 error)
    at Microsoft.WindowsAzure.CloudStorageAccount.Parse(String value)
    at Microsoft.WindowsAzure.Plugins.Diagnostics.DiagnosticsAgentManager.<StartAgent>b__0()

Reason? The reason is a connecting string parsing bug in Microsoft.WindowsAzure.Plugins.Diagnostics.ConnectionString =>

<ConfigurationSettings>
<Setting
name="Microsoft.WindowsAzure.Plugins.Diagnostics.ConnectionString"

value="DefaultEndpointsProtocol=https;AccountName=aaa;AccountKey=bbb;" />
</ConfigurationSettings>

Fix: Remove the trailing semi-colon at the end of the configuration string =>

Enabling Advanced Driver Tracing for the SQL Native Client ODBC Drivers

2012-05-15T19:48:00Z

If you are using the SQL Native Client ODBC driver, there is built-in high performance very detailed driver tracing, here is how you use it.

#1. Create batch file to start tracing (this traces for all three versions of SQL Native Client ODBC Driver) named StartSNACTrace.cmd:

@echo off echo Starting SNAC tracing... echo {BA798F36-2325-EC5B-ECF8-76958A2AF9B5} 0xFFFFFFFF 128 SQLNCLI > providers.txt echo {A9377239-477A-DD22-6E21-75912A95FD08} 0xFFFFFFFF 128 SQLNCLI10 >> providers.txt echo {2DA81B52-908E-7DB6-EF81-76856BB47C4F} 0xFFFFFFFF 128 SQLNCLI11 >> providers.txt reg add HKLM\SOFTWARE\Microsoft\BidInterface\Loader /v :Path /t REG_SZ /d "%systemroot%\system32\msdadiag.dll" /f del /Q *.etl logman create trace -n SnacTrace -ct perf -pf providers.txt -bs 10000 -nb 10000 50000 -o SnacTrace.etl -max 100 -cnf 00 logman start -n SnacTrace echo Run StopSnacTrace.cmd to stop tracing.

Note, replace 0xFFFFFFFF with 0x00000000 to do minimum tracing. The number 128 indicates save trace data as ANSI, which reduces by about 1/2 the space for the ETL files.

Note you MUST start the tracing before the process that uses the SQL Native Client ODBC driver starts (this is VERY IMPORTANT).

Start the tracing then start the process that is using the SQL Native Client ODBC driver. You can stop tracing at any time, even before the process exits.

#2. Create this batch file named StopSnacTrace.cmd to stop the tracing:

@echo off echo Stopping SNAC tracing... logman stop -n SnacTrace logman delete -n SnacTrace reg delete HKLM\SOFTWARE\Microsoft\BidInterface\Loader /v :Path /f echo SNAC tracing stopped

The above settings will create a new trace file of maximum size 100MB numbered SnacTrace_000001.etl, SnacTrace_000002.etl, etc…

#3. To process the trace files, you need to register a special mof file on the trace processing machine (you do not have to do this on the trace gathering machine)

#4. Download and register the all.mof file from "Data Access Tracing in SQL Server 2012" MSDN article (last time I checked the URL is here http://technet.microsoft.com/en-us/library/hh880086)

There is a section in this article that says "Download Sample and MOF Files". This file is named Setup.zip.

Download this file to some temporary folder and extract the files. You will see a folder named MOF_Files, go into this folder and locate the snac*.mof files, then on the client machine where you want to do tracing,

run the following from an elevated command prompt:

C:\>mofcomp all.mof

Microsoft (R) MOF Compiler Version 6.1.7600.16385 Copyright (c) Microsoft Corp. 1997-2006. All rights reserved. Parsing MOF file: all.mof MOF file has been successfully parsed Storing data in the repository... Done!

This will register all the providers so you can now see them with logman, to verify they are registered, run:

C:\>logman query providers | findstr SQL
SQLNCLI.1                                {BA798F36-2325-EC5B-ECF8-76958A2AF9B5}
SQLNCLI10.1                              {A9377239-477A-DD22-6E21-75912A95FD08}
SQLNCLI11.1                              {2DA81B52-908E-7DB6-EF81-76856BB47C4F}

You should see all 3 SNAC driver versions in the output. You are now ready to process the traces (the *.etl files generated)

#5. To process the traces to extract the data to CSV file, use tracerpt tool (comes with Windows) or LogParser (you need to download this tool).

Run following from a command prompt:

tracerpt SnacTrace_000001.etl -of CSV -en ANSI -gmt -o SnacTrace_000001.csv

Note I find the LogParser tool (you can download from Microsoft) provides a nicer, more compact log file, try this command:

LogParser.exe "select eventnumber, eventname, timestamp, userdata into SnacTrace_000001.csv from SnacTrace_000001.etl"
-i:ETW -o:CSV -oTsFormat:"yyyy-MM-dd hh:mm:ss.n" -headers:off

This will give you a really nice looking trace file output, and LogParser is also very fast to process large traces.

PowerShell Command Separator Is ; and Not &

2012-04-28T16:02:41Z

Ahh PowerShell, I still cannot fully embrace your powerful weirdness.

With normal NT command prompt I can restart my SQL Server by running =>

net stop mssqlserver & net start mssqlserver

This does not work in PowerShell, you get the usual cryptic, nonsensical and useless error message:

PS C:\> net stop mssqlserver & net start mssqlserver
Ampersand not allowed. The & operator is reserved for future use; use "&" to pass ampersand as a string.
At line:1 char:23
+ net stop mssqlserver & <<<< net start mssqlserver
+ CategoryInfo : ParserError: (:) [], ParentContainsErrorRecordException
+ FullyQualifiedErrorId : AmpersandNotAllowed

Personally, I would change this error message to:

Ampersand not allowed. You can use the semi-colon instead for a command separator in PowerShell.

Ok, given this logic, then I try:

PS C:\> net stop mssqlserver "&" net start mssqlserver
The syntax of this command is:

NET STOP
service

Ok, I give up. Time to search the internet.

Found some mention of an undocumented semi-colon as command separator, this works =>

PS C:\> net stop mssqlserver ; net start mssqlserver
The SQL Server (MSSQLSERVER) service is stopping.
The SQL Server (MSSQLSERVER) service was stopped successfully.

The SQL Server (MSSQLSERVER) service is starting.
The SQL Server (MSSQLSERVER) service was started successfully.

Disabling => CLRDLL: No CLR image loaded (i.e. mscorwks.dll)

2012-04-26T19:55:40Z

I’m debugging using the Debugging Tools For Windows (WinDbg). Debugging a native C++ process (no CLR loaded).

I find with every debugger command I run, I get annoying amounts of debugger logging spew saying “CLRDLL: No CLR image loaded (i.e. mscorwks.dll)”.

For example, when I dump a call stack I see:

0:000> kC

CLRDLL: No CLR image loaded (i.e. mscorwks.dll)

KERNELBASE!RaiseException

myapp!_CxxThrowException

CLRDLL: No CLR image loaded (i.e. mscorwks.dll)

WARNING: Stack unwind information not available. Following frames may be wrong.

Etc…

The fix to disable this spew is running:

.cordll –vd

The reason I saw this in my debugger is I have a startup script that runs:

.cordll -ve -u –l

This –ve means “enable vebose logging for CLR”. Internally the debugger is trying to load CLR debugging support over and over and this is failing. Running .cordll –vd disables the extended logging.

Note you can disable CLR integration with debugger to speed up the debugger command execution when debugging native code by running:

.cordll -d -u -vd

Read help on .cordll for details.

Calculating read_only_routing_url for AlwaysOn

2012-04-25T21:29:54Z

When setting up read-only routing for SQL Server AlwaysOn, you have to configure each availability replica with a read-only routing URL.

The read-only routing URL is just a “pointer” to the replica and determines what the replica will report back to the client driver when routing to the replica.

Suppose you have an AlwaysOn cluster with 3 cluster nodes, Node1, Node2, and Node3. On each of these nodes you will have a SQL Server instance running. For very simple configurations there will be one default SQL Server instance listening on tcp port 1433 on each node. With a more complex configuration you may have clustered SQL Server instances on these nodes.

Read-only routing uses the following algorithm to locate a readable secondary:

Client connects to an Availability Group listener endpoint.

Note this endpoint always points to the primary replica for the availability group

Client specifies ApplicationIntent=ReadOnly in the connection string, this is transmitted to the server during login
On server side, server checks that incoming connection is using an Availability Group listener endpoint

Otherwise, read-only routing is disabled

Server checks the target database and determines if it is in an availability group
If database is in an availability group, we check if the read_only_routing_list is set on the primary replica

If list is not set, routing is disabled
If list is set, then routing is enforced

Server then enumerates the replicas in the read_only_routing_list and checks each replica in the list
First replica it finds that is synchronizing and accepts readers (allow_connections=read_only or all) is the routing target
Server next reads the read_only_routing_url from this replica and sends this response to the client
Client reads routing URL and re-directs to the readable secondary instance

Given above, you need to ensure that the read_only_routing_url is properly set for each replica when enabling read-only routing, otherwise the client will be redirected to the wrong instance.

I created a T-SQL script that you can run against any instance and it will tell you what the appropriate read_only_routing_url is for the instance. The script will detect if the instance is clustered or not an generate appropriate URL. Script is included below, enjoy! =>

-- Read-only routing url generation script.
-- Connect to each replica in your AlwaysOn cluster and run this script to get the read_only_routing_url for the replica.
-- Then set this to the read_only_routing_url for the availability group replica =>
-- alter availability group MyAvailabilityGroup modify replica on N'ThisReplica' with (secondary_role(read_only_routing_url=N'<url>'))
print 'Read-only-routing url script v.2012.1.24.1'

print 'This SQL Server instance version is [' + cast(serverproperty('ProductVersion') as varchar(256)) + ']'

if (ServerProperty('IsClustered') = 1)
begin
print 'This SQL Server instance is a clustered SQL Server instance.'
end
else
begin
print 'This SQL Server instance is a standard (not clustered) SQL Server instance.'
end

if (ServerProperty('IsHadrEnabled') = 1)
begin
print 'This SQL Server instance is enabled for AlwaysOn.'
end
else
begin
print 'This SQL Server instance is NOT enabled for AlwaysOn.'
end

-- Detect SQL Azure instance.
declare @is_sql_azure bit
set @is_sql_azure = 0

begin try
    set @is_sql_azure = 1
    exec('declare @i int set @i = sql_connection_mode()')
    print 'This SQL Server instance is a Sql Azure instance.'
end try
begin catch
    set @is_sql_azure = 0
    print 'This SQL Server instance is NOT a Sql Azure instance.'
end catch

-- Check that this is SQL 11 or later, otherwise fail fast.
if (@@microsoftversion / 0x01000000 < 11 or @is_sql_azure > 0)
begin
print 'This SQL Server instance does not support read-only routing, exiting script.'
end
else
begin -- if server supports read-only routing

    -- Fetch the dedicated admin connection (dac) port.
    -- Normally it's always port 1434, but to be safe here we fetch it from the instance.
    -- We use this later to exclude the admin port from read_only_routing_url.
    declare @dac_port int
    declare @reg_value varchar(255)
    exec xp_instance_regread
        N'HKEY_LOCAL_MACHINE',
        N'SOFTWARE\Microsoft\Microsoft SQL Server\\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp',
        N'TcpDynamicPorts',
        @reg_value output

set @dac_port = cast(@reg_value as int)

    print 'This SQL Server instance DAC (dedicated admin) port is ' + cast(@dac_port as varchar(255))
    if (@dac_port = 0)
    begin
        print 'Note a DAC port of zero means the dedicated admin port is not enabled.'
    end

    -- Fetch ListenOnAllIPs value.
    -- If set to 1, this means the instance is listening to all IP addresses.
    -- If set to 0, this means the instance is listening to specific IP addresses.
    declare @listen_all int
    exec xp_instance_regread
        N'HKEY_LOCAL_MACHINE',
        N'SOFTWARE\Microsoft\Microsoft SQL Server\\MSSQLServer\SuperSocketNetLib\Tcp',
        N'ListenOnAllIPs',
        @listen_all output

    if (@listen_all = 1)
    begin
        print 'This SQL Server instance is listening to all IP addresses (default mode).'
    end
    else
    begin
        print 'This SQL Server instance is listening to specific IP addresses (ListenOnAllIPs is disabled).'
    end

    -- Check for dynamic port configuration, not recommended with read-only routing.
    declare @tcp_dynamic_ports varchar(255)
    exec xp_instance_regread
        N'HKEY_LOCAL_MACHINE',
        N'SOFTWARE\Microsoft\Microsoft SQL Server\\MSSQLServer\SuperSocketNetLib\Tcp\IPAll',
        N'TcpDynamicPorts',
        @tcp_dynamic_ports output

    if (@tcp_dynamic_ports = '0')
    begin
        print 'This SQL Server instance is listening on a dynamic tcp port, this is NOT A RECOMMENDED CONFIGURATION when using read-only routing, because the instance port can change each time the instance is restarted.'
    end
    else
    begin
        print 'This SQL Server instance is listening on fixed tcp port(s) (it is not configured for dynamic ports), this is a recommended configuration when using read-only routing.'
    end

    -- Calculate the server domain and instance FQDN.
    -- We use @server_domain later to build the FQDN to the clustered instance.
    declare @instance_fqdn varchar(255)
    declare @server_domain varchar(255)

    -- Get the instance FQDN using the xp_getnetname API
    -- Note all cluster nodes must be in same domain, so this works for calculating cluster FQDN.
    set @instance_fqdn = ''
    exec xp_getnetname @instance_fqdn output, 1

    -- Remove embedded null character at end if found.
    declare @terminator int
    set @terminator = charindex(char(0), @instance_fqdn) - 1
    if (@terminator > 0)
    begin
        set @instance_fqdn = substring(@instance_fqdn, 1, @terminator)
    end

-- Build @server_domain using @instance_fqdn.
set @server_domain = @instance_fqdn

    -- Remove trailing portion to extract domain name.
    set @terminator = charindex('.', @server_domain)
    if (@terminator > 0)
    begin
        set @server_domain = substring(@server_domain, @terminator+1, datalength(@server_domain))
    end
    print 'This SQL Server instance resides in domain ''' + @server_domain + ''''

    if (ServerProperty('IsClustered') = 1)
    begin
        -- Fetch machine name, which for a clustered SQL instance returns the network name of the virtual server.
        -- Append @server_domain to build the FQDN.
        set @instance_fqdn = cast(serverproperty('MachineName') as varchar(255)) + '.' + @server_domain
    end

declare @ror_url varchar(255)
declare @instance_port int

set @ror_url = ''

    -- Get first available port for instance.
    select
    top 1    -- Select first matching port
    @instance_port = port
    from sys.dm_tcp_listener_states
    where
    type=0 -- Type 0 = TSQL (to avoid mirroring endpoint)
    and
    state=0    -- State 0 is online
    and
    port <> @dac_port -- Avoid DAC port (admin port)
    and
    -- Avoid availability group listeners
    ip_address not in (select ip_address from sys.availability_group_listener_ip_addresses agls)
    group by port
    order by port asc -- Pick first port in ascending order

    -- Check if there are multiple ports and warn if this is the case.
    declare @list_of_ports varchar(max)
    set @list_of_ports = ''

    select
    @list_of_ports = @list_of_ports +
        case datalength(@list_of_ports)
        when 0 then cast(port as varchar(max))
        else ',' + cast(port as varchar(max))
        end
    from sys.dm_tcp_listener_states
    where
    type=0    --     Type 0 = TSQL (to avoid mirroring endpoint)
    and
    state=0    -- State 0 is online
    and
    port <> @dac_port -- Avoid DAC port (admin port)
    and
    -- Avoid availability group listeners
    ip_address not in (select ip_address from sys.availability_group_listener_ip_addresses agls)
    group by port
    order by port asc

print 'This SQL Server instance FQDN (Fully Qualified Domain Name) is ''' + @instance_fqdn + ''''
print 'This SQL Server instance port is ' + cast(@instance_port as varchar(10))

set @ror_url = 'tcp://' + @instance_fqdn + ':' + cast(@instance_port as varchar(10))

    print '****************************************************************************************************************'
    print 'The read_only_routing_url for this SQL Server instance is ''' + @ror_url + ''''
    print '****************************************************************************************************************'

    -- If there is more than one instance port (unusual) list them out just in case.
    if (charindex(',', @list_of_ports) > 0)
    begin
        print 'Note there is more than one instance port, the list of available instance ports for read_only_routing_url is (' + @list_of_ports + ')'
        print 'The above URL just uses the first port in the list, but you can use any of these available ports.'
    end

end -- if server supports read-only routing
go

Tracking Handle Misuse Using Application Verifier and Windbg

2009-09-08T20:26:44Z

In a recent MSDN forum post Connection Pooling randomly throwing COM exceptions I uncovered an issue where some code inside the process was closing handles owned by our pooling code. This caused our pooling code to get into a bad state triggering a SemaphoreFullException every single time a caller attempted to open a connection from the pool.

The root cause of this problem was code calling the Win32 API CloseHandle on a handle that it did not own. Since Windows can aggresively reuse handle values, it’s possible to hit a situation like below:

Thread1: CreateFile –> Get handle #1.

Thread1: CloseHandle(#1)

Thread2: CreateFile –> Get handle #1, store in member variable.

Thread1: CloseHandle (#1) <- Mistake in code.

Thread1: CreateEvent –> Get handle #1.

Now my code on Thread2 thinks it has a file handle, but it has an event handle! Thread2 did not make any coding mistake, the Thread1 code corrupted Thread2’s handle value.

This is an inherent issue when using handles, they are inherently dangerous you have to be very careful to only call CloseHandle 1 time. Now the operating system could mitigate this by handling out random values for handles, but in general the operating system does not always do this for performance reasons. The tradeoff is fast performance for all applications that behave correctly.

Now if you have a process where you suspect handles are getting misused (leaked or double closed), you can use Application Verifier to help track this down very quickly, this is what I want to talk about.

First download both Microsoft Application Verifier and Debugging Tools for Windows, both are free downloads from Microsoft. I would provide links but these always change, get the latest versions of both. Get the version that matches your operating system bitness as well for best results.

Install Microsoft Application Verifier and Debugging Tools for Windows on the machine where you have the problem. Both are very clean installers and remove easily so don’t worry about installing on production machine, they are easy to remove and don’t require any reboot during removal. If you are really worried you can install them on a separate machine and xcopy the files to the production machine, this works as well.

Next, figure out what process you want to debug. If you are running IIS application with .NET, most likely this is the w3wp.exe process. You don’t need the process ID yet.

First run Application Verifier to setup handle tracking, it looks like below:

Not too fancy. :) You want to create the entry for the process name next, select File | Add Application. Let’s do the demonstration using notepad.exe for now. Just type in notepad.exe and click Open. You do not have to find and select the correct binary, just typing in the process name is sufficient, it does not need the full path:

Now that you’ve added the process, go to the right and select ONLY handles under the Basics options, like below:

The reason I only select handles here is to reduce the performance implications of all the other tracking inside the process. I just want to track handles, this is all.

Once you’ve selected handles only, click Save, then close Application Verifier. You will see this message:

This is fine, we will do this work later on (attach debugger). Click OK and exit App Verifier.

Now, VERY IMPORTANT! Re-open App Verifier. Verify you see your process name correctly and you see only Handles is checked. Do this step as a sanity check to ensure you selected the right thing AND it was saved correctly to registry.

Exit App Verifier again.

If your process is currently running, stop and restart the process. If for example you are using IIS, you want to run iisreset at this point to restart IIS. This step is required as the process reads the Application Verifier flags ONLY during startup.

In our case we are debugging notepad, so lets startup an instance of notepad.exe at this point (Start|Run –> Notepad.exe)

Now open Windbg.exe (from Debugging Tools For Windows install). Select File | Attach To Process…

VERY IMPORTANT! First thing to do is check the Non-invasive check box on the Attach to Process dialog, as below:

You should ALWAYS check this when doing handle tracking work because this allows you to attach to the process in a special mode called non-invasive mode. This allows the debugger to be later detached and the process will continue normally. If you don’t check this checkbox, the debugger will fully attach to the process and you cannot shut down the debugger without terminating the process.

Next, locate your process, you will see here my process is notepad.exe, select this process:

Click OK to attach the debugger non-invasively. Once you are attached, go to the command window (select View|Command) and run the following commands:

0:000> !htrace -enable
Handle tracing enabled.
Handle tracing information snapshot successfully taken.

This command is required to enable the tracing of handles. Once this is set, you can exit the debugger by running the q (quit) command:

0:000:> q

At this point handle tracking is enabled for the process. For this experiment, now go to notepad and open a single text file but do not exit notepad.exe yet.

Now we want to see what handle tracking can do for us. Startup Windbg.exe again and attach to notepad again in non-invasive mode. Run the following commands:

0:000> .dump /ma c:\temp\notepad.dmp
Creating c:\temp\notepad.exe - mini user dump
Dump successfully written

Note the command is .dump /ma <filename> where <filename> can be anything you want here. I called my dump file c:\temp\notepad.dmp.

This creates a dump of the process. Now again run q (quit) command to exit debugger.

Re-open Windbg again, we are next going to examine the dump file.

In Windbg select File | Open Crash Dump… and select c:\temp\notepad.dmp and click Open.

In the command window, run the following 2 commands to load symbols:

0:000> .sympath srv*http://msdl.microsoft.com/download/symbols
Symbol search path is: srv*http://msdl.microsoft.com/download/symbols
Expanded Symbol search path is: srv*http://msdl.microsoft.com/download/symbols
0:000> .reload /f
..........................................*** ERROR: Symbol file could not be found. Defaulted to export symbols for AvCheck.dll -
.....
Loading unloaded module list

Note you may see errors here, this is fine for now.

Now we want to see a trace of our handle activity, run the !htrace command with no arguments:

0:000> !htrace

Next you will see tons of debugger spew like below:

--------------------------------------
Handle = 0x00000000000002f0 - OPEN
Thread ID = 0x0000000000001390, Process ID = 0x0000000000000b6c

0x0000000077835e3a: ntdll!ZwDuplicateObject+0x000000000000000a
0x00000000776148a8: kernel32!DuplicateHandle+0x0000000000000055
0x000007fefe6cb435: rpcrt4!THREAD::THREAD+0x00000000000000b5
0x000007fefe6cb35c: rpcrt4!ThreadSelfHelper+0x0000000000000028
0x000007fefe77444c: rpcrt4!NdrpClientCall2+0x0000000000000698
0x000007fefe774ddd: rpcrt4!NdrClientCall2+0x000000000000001d
0x000007fefe4c0e9a: ole32!BulkUpdateOIDs+0x000000000000008a
0x000007fefe4c0d89: ole32!CRpcResolver::BulkUpdateOIDs+0x0000000000000109
0x000007fefe4c0c36: ole32!CROIDTable::ClientBulkUpdateOIDWithPingServer+0x0000000000000224
0x000007fefe4c94c3: ole32!CROIDTable::WorkerThreadLoop+0x0000000000000057
0x000007fefe4c9406: ole32!CRpcThread::WorkerLoop+0x000000000000001e
0x000007fefe4c9682: ole32!CRpcThreadCache::RpcWorkerThreadEntry+0x000000000000001a
0x000000007761466d: kernel32!BaseThreadInitThunk+0x000000000000000d
0x0000000077818791: ntdll!RtlUserThreadStart+0x000000000000001d
--------------------------------------
Handle = 0x000000000000031c – OPEN

…

This shows you all the handle activity in the process.

You can locate a double close by searching for the CLOSE lines:

Handle = 0x000000000000033c - CLOSE

This will also have the stack trace of the code that closed the handle.

Now in my case what I did was gather a dump when some error case occurred in my process, then I examined the dump to determine what handle my code was using and then used !htrace to find out who closed it. All of the information is kept in the crash dump nicely for you.

You can also pass a parameter to !htrace to dump activity for a single handle, like below:

0:000> !htrace 33c
--------------------------------------
Handle = 0x000000000000033c - CLOSE
Thread ID = 0x0000000000000744, Process ID = 0x0000000000000b6c

0x0000000077835faa: ntdll!ZwTerminateThread+0x000000000000000a
0x000000007780b508: ntdll!RtlExitUserThread+0x0000000000000048
0x000000007780b8a7: ntdll!TppWorkerThread+0x0000000000000a23
0x000000007761466d: kernel32!BaseThreadInitThunk+0x000000000000000d
0x0000000077818791: ntdll!RtlUserThreadStart+0x000000000000001d
--------------------------------------
Handle = 0x000000000000033c - OPEN
Thread ID = 0x0000000000000744, Process ID = 0x0000000000000b6c

0x000000007773d57a: user32!ZwUserCallOneParam+0x000000000000000a
0x000000007773d95f: user32!RealMsgWaitForMultipleObjectsEx+0x00000000000000b3
0x000000007773da2e: user32!MsgWaitForMultipleObjectsEx+0x0000000000000046
0x0000000077732af4: user32!MsgWaitForMultipleObjects+0x0000000000000020
0x000007fef5fbb198: browseui!CShellTaskScheduler::_TT_MsgWaitForMultipleObjects+0x0000000000000038
0x000007fef5fbb11d: browseui!CShellTaskScheduler::TT_TransitionThreadToRunningOrTerminating+0x00000000000000a1
0x000007fef5fbaea1: browseui!CShellTaskThread::ThreadProc+0x0000000000000076
--------------------------------------

You will notice a specific handle is closed and reopened many times over a period of time, this is normal. What you want to look for is cases where CLOSE is called 2 times in a row to find cases of handle misuse. Likewise if a handle is leaked you will not see a final CLOSE called, the last trace will be an OPEN.

Now, when you are finished debugging, BE ABSOLUTELY SURE to start App Verifier again and DELETE the process entry for your process! You want to always do this since the App Verifier settings are persisted in the registry, they will survive even a server reboot. Right click on the Image Name and select “Delete Application”. Then click Save and close and re-open App Verifier to ensure that the entry is gone. As well if you are debugging some service you want to restart the service to stop the tracking, run iisreset for example again to reset IIS to normal state if you are debugging IIS.

Hope this helps you debug the next handle misuse case you encounter, have fun!

Nice day

2009-02-11T08:30:01Z

Yes.

How to tell if binary is x86 or amd64?

2008-11-25T20:17:59Z

First get dumpbin tool, ships with any Windows SDK.

Run dumpbin /headers on the binary, then look at the magic number.

This is an amd64 binary:

c:\>dumpbin /headers c:\amd64\msvcr100.dll | findstr magic
20B magic # (PE32+)

This is a 32-bit binary:

c:\DPMedusa1>dumpbin /headers c:\x86\msvcr100.dll | findstr magic
10B magic # (PE32)

dumpbin also works on executables, for example on my amd64 machine:

c:\>dumpbin /headers c:\Windows\System32\calc.exe | findstr magic
20B magic # (PE32+)

c:\>dumpbin /headers c:\Windows\SysWOW64\calc.exe | findstr magic
10B magic # (PE32)

TrustedInstaller

2008-10-08T00:09:52Z

Just a note to self, when typing in TrustedInstaller into security dialogs in Vista/Windows 2008, be sure to prefix with:

NT SERVICE\TrustedInstaller

C:\Windows>icacls c:\windows\winsxs
c:\windows\winsxs

                  NT SERVICE\TrustedInstaller:(OI)(CI)(F)
                  BUILTIN\Administrators:(OI)(CI)(RX)
                  NT AUTHORITY\SYSTEM:(OI)(CI)(RX)
                  BUILTIN\Users:(OI)(CI)(RX)

The security UI dialogs do not recognize TrustedInstaller just by itself (don't know why).

What is mscorwks_ntdef.dll?

2008-10-07T03:55:01Z

Ok, file this one under debugger trivia.

If you are debugging a 32-bit CLR process from a 64-bit native (Windbg) debugger, you will notice:

0:040> lmvm mscorwks_ntdef
start             end                 module name
00000000`69480000 00000000`69a10000   mscorwks_ntdef   (deferred)
    Image path: mscorwks_ntdef.dll
    Image name: mscorwks_ntdef.dll
    Timestamp:        Fri Jul 25 06:58:48 2008 (4889DC18)
    CheckSum:         00597CC4
    ImageSize:        00590000
    File version:     2.0.50727.3053
    Product version: 2.0.50727.3053
    File flags:       0 (Mask 3F)
    File OS:          4 Unknown Win32
    File type:        2.0 Dll
    File date:        00000000.00000000
    Translations:     0409.04b0
    CompanyName:      Microsoft Corporation
    ProductName:      Microsoft® .NET Framework
    InternalName:     mscorwks.dll
    OriginalFilename: mscorwks.dll
    ProductVersion:   2.0.50727.3053
    FileVersion:      2.0.50727.3053 (netfxsp.050727-3000)
    FileDescription: Microsoft .NET Runtime Common Language Runtime - WorkStation
    LegalCopyright:   © Microsoft Corporation. All rights reserved.
    Comments:         Flavor=Retail

What is this mscorwks_ntdef? It is debugger hocus pocus due to a bug. Run this command to make it go away:

0:040> .reload /s
.....
Loading Wow64 Symbols
................................................................
................................................................
................................................................

Now you will see the correct output for mscorwks module:

0:040> lmvm mscorwks_ntdef
start             end                 module name
0:040> lmvm mscorwks
start             end                 module name
00000000`69480000 00000000`69a10000   mscorwks   (deferred)
    Image path: C:\Windows\Microsoft.NET\Framework\v2.0.50727\mscorwks.dll
    Image name: mscorwks.dll
    Timestamp:        Fri Jul 25 06:58:48 2008 (4889DC18)
    CheckSum:         00597CC4
    ImageSize:        00590000
    File version:     2.0.50727.3053
    Product version: 2.0.50727.3053
    File flags:       0 (Mask 3F)
    File OS:          4 Unknown Win32
    File type:        2.0 Dll
    File date:        00000000.00000000
    Translations:     0409.04b0
    CompanyName:      Microsoft Corporation
    ProductName:      Microsoft® .NET Framework
    InternalName:     mscorwks.dll
    OriginalFilename: mscorwks.dll
    ProductVersion:   2.0.50727.3053
    FileVersion:      2.0.50727.3053 (netfxsp.050727-3000)
    FileDescription: Microsoft .NET Runtime Common Language Runtime - WorkStation
    LegalCopyright:   © Microsoft Corporation. All rights reserved.
    Comments:         Flavor=Retail

However, try to load sos and it will fail:

0:040> .loadby mscorwks sos
Unable to find module 'sos'

0:040> .load C:\Windows\Microsoft.NET\Framework64\v2.0.50727\sos.dll
0:040> !dumpstack -EE
Failed to load data access DLL, 0x80004005
Verify that 1) you have a recent build of the debugger (6.2.14 or newer)
            2) the file mscordacwks.dll that matches your version of mscorwks.dll is
                in the version directory
            3) or, if you are debugging a dump file, verify that the file
                mscordacwks_<arch>_<arch>_<version>.dll is on your symbol path.
            4) you are debugging on the same architecture as the dump file.
                For example, an IA64 dump file must be debugged on an IA64
                machine.

You can also run the debugger command .cordll to control the debugger's
load of mscordacwks.dll. .cordll -ve -u -l will do a verbose reload.
If that succeeds, the SOS command should work on retry.

If you are debugging a minidump, you need to make sure that your executable
path is pointing to mscorwks.dll as well.
0:040> .cordll -ve -u -l
CLRDLL: LoadLibrary(C:\Windows\Microsoft.NET\Framework\v2.0.50727\mscordacwks.dll) failed, Win32 error 193
CLRDLL: Unable to find mscordacwks_AMD64_x86_2.0.50727.3053.dll by mscorwks search
CLRDLL: Unable to find 'mscordacwks_AMD64_x86_2.0.50727.3053.dll' on the path
CLRDLL: Unable to get version info for 'c:\syms\mscorwks.dll\4889DC18590000\mscordacwks_AMD64_x86_2.0.50727.3053.dll', Win32 error 0n87
CLRDLL: ERROR: Unable to load DLL mscordacwks_AMD64_x86_2.0.50727.3053.dll, Win32 error 0n87
CLR DLL status: ERROR: Unable to load DLL mscordacwks_AMD64_x86_2.0.50727.3053.dll, Win32 error 0n87

Summary: Sos debugger extension will not work in "mixed bitness" debugging scenarios.

You must debug 32-bit CLR process using 32-bit native debugger in order to get sos to work properly.

Likewise if you are debugging 64-bit CLR process, use 64-bit debugger and 64-bit sos.dll.

How To Read Data From AD LDS Into Java

2008-09-23T19:49:02Z

Just helping out the next person, this code took me all day to cobble together.

Key tricky part was how to connect to AD using an NT security account from Java.

public static String ReadBindingInfoFromAd() throws NamingException
{  
    Hashtable env = new Hashtable();   
    env.put(Context.INITIAL_CONTEXT_FACTORY, "com.sun.jndi.ldap.LdapCtxFactory");   
    env.put(Context.PROVIDER_URL, "LDAP://MyLdapServer:389");
    env.put(Context.SECURITY_AUTHENTICATION, "DIGEST-MD5"); // Use DIGEST-MD5, it works with Windows.
    env.put(Context.SECURITY_PRINCIPAL, "MyNtDomain\\MyNtUser");  // This is your nt domain and user.
    env.put(Context.SECURITY_CREDENTIALS, "MyPassword");    // Nt user account's password.
    DirContext context = new InitialDirContext(env); 
    
    SearchControls ctrl = new SearchControls();
    ctrl.setCountLimit(1);  // This is a good idea, limits result size. 
    ctrl.setSearchScope(SearchControls.ONELEVEL_SCOPE);
    ctrl.setReturningAttributes(new String [] {"serviceBindingInformation"});
    
    NamingEnumeration enumeration = context.search("CN=MyServer,CN=MyContainer", "(objectclass=serviceConnectionPoint)", ctrl);
    
    // My search should only return one value.
    if (enumeration.hasMore())
    {
        SearchResult result = (SearchResult) enumeration.next();
        Attributes attribs = result.getAttributes();
        NamingEnumeration values = ((BasicAttribute) attribs.get("serviceBindingInformation")).getAll(); 
        if (values.hasMore())
        {
            return values.next().toString();
        }
    }
    return "";  
}

Same code from C# is not as bad because it leverages current user's context:

public static string ReadBindingInfoFromAd()
{
    using (DirectoryEntry root = new DirectoryEntry("LDAP://MyLdapServer:389/CN=MyServer,CN=MyContainer"))
    {
        return (string)root.Properties["serviceBindingInformation"].Value;
    }
}

Chicken + Egg Problem With Hyper-V

2008-09-14T19:11:25Z

When we neglect to port an old and crusty feature, one will always wave a few hands and say it's better in the end for the customer. But sometimes it becomes a sticking point. Today I am trying to setup VMs using Hyper-V on Windows 2008 and by default Hyper-V does not allow mouse to work with VMs over remote desktop unless you install VM additions on guest OS. Virtual PC did not have this requirement, the mouse would work but in a horribly wild and wacky way, skittering left and right across the screen until you installed VM additions. In most cases this was not a problem for me except for today, I'm working from home and I just need to send ONE mouse click in order to log into the guest OS to install VM additions. The old wild and crazy VPC mouse click would suffice here!

Arg. I cannot log in because of one mouse click. I will try rebooting.

Rebooted, managed to type in password behind SCM message box, and logged in.

Next my NIC does not work. Ok, I cannot configure hyper-v over remote desktop and the NIC does not work by default.

Ok, fixed NIC, user error, I had to setup Legacy mode on the NIC, got this working.

Attempted to install hyper-v extensions, this failed with "An error has occurred: The specified program requires a newer version of Windows."

Arg again. I have to install Windows 2003 SP2 I suppose. Trying this now.

What I need to do is create isos for Windows 2003 and Windows 2003 SP2, this would help speed things along.

SqlClient Timeouts Revealed

2008-08-30T03:33:51Z

Just some notes from recent discussions internal and external of how timeouts work with SqlClient. First a classification of where timeouts can occur with SqlClient:

When attempting to get a connection from the SqlClient connection pool
When attempting to create a new fresh connection to server (not getting one from pool)
When sending a command explicitly to the server
When sending commands with "context connection=true"
When sending commands implicitly (under the covers) to the server
When executing asynchronous commands (BeginExecute...)
When waiting for attention acknowledgement from server (special rare case)
When sending TM commands
When fetching rows
When uploading rows using bulk copy

I hope I did not miss a class of timeout here, if so, let me know.

First, the majority of these above listed timeouts leverage SqlConnection.ConnectionString's "Connect Timeout" and SqlCommand.CommandTimeout values in a logical and sensible manner.

Login Timeouts

For example 1 and 2 use "Connect Timeout", this makes sense to me. We could have created a separate "get a live connection timeout" versus a "get connection from the pool timeout" but the utility of this does not over-ride the added complexity of having 2 timeouts here. But say for example you happen to have an old ancient lost to the ages SQL Server that takes 1 minute to open a connection to over a slow WAN. But once connections are in the pool you want to pull these out in a rapid fashion. Ahh, it's just not worth it to add this feature for such an edge case.

With Connect Timeout, a value of 0 means "infinite" wait. It's not truely infinite of course 0 is mapped to UInt32.MaxValue milliseconds underneath which is about 49 days. Hope you don't have to wait that long for SQL to respond.

If the connection pool is full, attempts to get a connection from the pool will block until Connect Timeout expires, this makes sense.

If you have to open a new connection, there are many activities that occur underneath that could take time. We have to open a socket to the server, this could be blocked by networking layer for a long time. Next we have to send prelogin packet to server to negotiate packet size and get server version, and here we wait for a prelogin response from the server. Once we process prelogin, we then have to send a login packet and this can get complicated. First we have to negotiate with SQL Server to get SSL setup for encrypting the login packet. Once we have SSL setup, which can entail multiple trips back and forth to server, we can then send the login packet. If you use integrated authentication, we also have to setup the SSPI channel and this could take a few round trips as well. Ok, once all this is done we get a login acknowledgement from the server and we are "logged in" and hand back control to the caller. All of this occurs under Connect Timeout period of time during SqlConnection.Open, if any leg in the process takes too long, the timeout expires and exception is thrown.

Note if you do get lots of login timeout failures, this can be indicative of poor pooling performance. Logins can timeout due to high CPU on server side as well -- if for example the server just does not have enough CPU to process the login quickly enough. In any case Connect Timeout in SqlClient controls this timeout.

Logins are slightly more complicated when you are connecting to a mirrored SQL Server. If you are doing this you will have "Failover Partner" specified in the ConnectionString. When connecting to a mirrored server we alternately attempt to open connections to primary and secondary back and forth until timeout expires. Overall the same timeout provided by "Connect Timeout" applies in the end.

Explicit Command Execution Timeouts

When executing commands using SqlCommand, the CommandTimeout is used. A CommandTimeout of 0 means "infinite" again (a.k.a 49 days). If you don't set CommandTimeout, a default of 30 seconds is used.

Looks simple on the surface but now the fun begins. What precisely does "the command completed" really mean? Does it mean the server processed the query and sent back the first byte of response? Does it mean the time until the entire response is drained? The help topic for SqlCommand.CommandTimeout indicates:

This property is the cumulative time-out for all network reads during command execution or processing of the results. A time-out can still occur after the first row is returned, and does not include user processing time, only network read time.

This can be important if you are reading millions of rows using a SqlDataReader. As you read more and more rows you are chewing away at this cumulative time, and the time can run out in theory.

Note that when reading data from the server, SqlClient reads data in packets (called TDS packets) and these are 8000 bytes each by default. We don't do any readahead, we just read 8K at a time. Hence reading a series of rows in a resultset in turn reads a series of 8K packets from the server. If you are curious about this you can use SQL Server Management Studio and turn on the "Include Client Statistics" feature (it's on the toolbar):

You can see here I read 1000 rows, 1306 TDS packets (8000 bytes each) and total of 4MB of data. This boils down to 1306 network reads of 8K. In most cases the network reads are very fast, however you could hit one or more locked rows half-way through reading the results which could accumulate more time. A locked row means the client blocks waiting for the row lock to become available, eating up more of your CommandTimeout.

Hence CommandTimeout covers all the time spend making all these 8k reads, including the first 8K read which is most likely the longest wait as it's waiting for the server to process the query and send back the first bytes of response.

Some customers have complained about this behavior, they think that once the initial read completes then subsequent row fetches should not time out. The command has completed in other words, why time out reading rows? In reality, the command may or may not be completed when the first 8K read is returned to the client. For example you could send a batch of commands and the first command may be completed but subsequent commands in the batch have not even started executing yet. I suppose we could have a row read timeout and a command timeout to allow more granular control here, but it is really such an edge case that it's not worth cluttering the API with multiple and thus more confusing timeout settings (most customers would never need to set RowReadTimeout).

SqlConnection and "context connection=true"

If you never write CLR code that runs inside SQL Server, you can skip this section. If you do write code that runs inside SQL Server (CLR stored procedures or functions) then there is a feature called "context connection=true" that allows you to open a loopback SqlConnection to current SQL Server database. This loopback connection ignores all command timeouts since it is running in the context of a sql batch already and the client controls the timeout.

Implicit Command Timeouts

In some rare cases SqlClient code needs to execute internal commands behind the scenes to fetch metadata etc..., these are "implicit commands". Some cases of SqlConnection implicit commands:

SqlConnection.GetSchema - Internally creates a SqlCommand and executes a metadata query.
SqlConnection.ChangeDatabase - Sends a "use [<databasename>]" command under the covers.
SqlConnection.BeginTransaction - Sends a "begin transaction" command or other transaction manager command under the covers.

These SqlConnection methods don't have access to any pre-existing SqlCommand object to obtain a CommandTimeout, hence they were written to use Connect Timeout. Ok, to be honest I am not sure if this makes total sense, but this is how we did it. Think of "Connect Timeout" as the timeout that applies to all things SqlConnection (login, implicit commands) then it makes sense. Big note: SqlConnection.GetSchema currently has a bug where it uses a hard-coded 180 second timeout,the plan is to fix this to be consistent and use Connect Timeout some time in the future.

Now SqlCommand does not have any implicit commands it runs, but if it did, it would use CommandTimeout.

SqlDataReader does have an implicit command --> SqlDataReader.GetSchemaTable. GetSchemaTable uses the CommandTimeout from it's parent SqlCommand object since SqlDataReader does not have a settable timeout.

I classify TM commands as another form of implicit command executed by the driver under the covers. What is a TM command? TM stands for transaction manager. A TM command is a special command sent to SQL Server to request changes in transaction state from the client. Commands like SqlConnection.BeginTransaction and SqlConnection.EnlistTransaction and SqlConnection.EnlistDistributedTransaction send these TM commands. All of these use the parent SqlConnection's "Connect Timeout".

Asynchronous Command Execution Timeout

SqlClient does not enforce any timeouts on asynchronously executed commands. They can run forever in theory. Note this is not the 49 day forever but the real forever. Asynchronous commands include SqlCommand.BeginExecuteReader, BeginExecuteNonQuery, and BeginExecuteReader.

Ok, you might be a little shocked by this behavior, but this is how it works. The idea of asynchronous command execution is you send off the command and then are notified via an event when it completes. You can implement your own timeout if needed, for example you could start a timer that checks if the command is completed after a period of time and if not then cancels it.

The bottom line is IF we wanted to implement CommandTimeout in this case we would just do the same thing, spin up a timer with a handle to the executing command and cancel it if the timer expires and the command has not completed.

Cancel Timeout (attention acknowledgement timeout)

Ok, this is a very rare edge case, you can probably forget about this one, but if you are really bored, read on. Suppose you execute a command, then the command times out. When this happens the SqlClient driver sends a special 8 byte packet to the server called an attention packet. This tells the server to stop executing the current command. When we send the attention packet, we have to wait for the attention acknowledgement from the server and this can in theory take a long time and time out. You can also send this packet by calling SqlCommand.Cancel on an asynchronous SqlCommand object. This one is a special case where we use a 5 second timeout. In most cases you will never hit this one, the server is usually very responsive to attention packets because these are handled very low in the network layer.

Now there is a really rare edge case where you send an attention but the server has already completed the operation. What happens in this case? In this case SqlClient just drains the response for you silently and still throws the timeout exception. This brings up a good point -- there is a slim sliver of a possibility if you cancel a command or it times out it may actually complete on the server side prior to the attention signal, please keep this in mind. This is a small gotcha that exists when using automatic transactions and a timeout or cancel occurs. If you use a manual transaction then you can always roll back after handing the timeout exception to ensure you get into a consistent state.

Summary

In general the parent objects timeout is used in most cases, this is the easiest thing to remember. If parent is SqlConnection then Connect Timeout is used, if the parent object is SqlCommand or SqlDataReader, then the SqlCommand.CommandTimeout is used. There are a few minor exceptions that don't really add up to much for most scenarios.

If I missed anything here, let me know. Matt

Why Can't I Log Into SQL Server?

2008-08-28T03:17:32Z

Even though I've been working on the SQL Server protocol layer for the past 3 years I still have those days where I scratch my head because I cannot connect to my SQL Server. I have to agree it can be frustrating sometimes. Usually, once you get it working, it stays working, which is a good thing. If any of you out there hit any other "head smacking" gotchas, let me know.

1. SSL Self-Signed Cert Does Not Work When Forcing Encryption From Client

Today I forgot that I checked the "Encrypt Connection" checkbox in SQL Server Management Studio while trying to verify that an SSL certificate I installed on a SQL 2000 box a day before was working. Then I attempted to connect to my SQL 2008 server that is using self-signed certificate (which is default behavior in SQL 2005 and later). When you do this, you get the following error:

"A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) (Microsoft SQL Server, Error: -2146893019)

This error makes sense if you realize that a self-signed certificate does not have a trusted root (everyone knows this, right?). Anyway, if you see this one, now you will remember. This can occur with SQL 2005 and SQL 2008, both of these will load a self-signed certificate during startup time if an appropriate SSL certificate is not setup on the machine.

Thing to remember: If you want full client side forced SSL encryption to work properly with SQL Server, you need to configure a real SSL certificate that the client trusts. The self-signed cert is only useful for encrypting login packets and is not fully trusted by the client since it does not have a trusted root authority.

2. I'm A Vista Admin But Cannot Login To My Local SQL Server

Another good one: Attempt to login to your local SQL Server on Vista from non-elevated command prompt or non-elevated SSMS. Login failed for user domain\username! But I'm logged in as an admin! You are not admin if you don't elevate. This one get's me about once every 3 months or so.

3. Cannot Connect To A Clustered SQL Server Named Instance From A Vista Client (submitted by Andy Babiec, thanks!)

This is technically speaking not a Vista specific issue, but it is seen more often on Vista clients because of it's advanced (a.k.a. more secure) firewall configuration.

The basic problem is when the client needs to determine the port of any SQL Server named instance, it will send a UDP packet on port 1434 to the target server and then wait for a response on UDP 1434. The response packet will have instance name and port number. Now the issue arises with Windows Cluster where our SQL Browser service gets the incoming UDP packet then posts a response. The incoming UDP packet comes in on the virtual cluster IP, but when we send a response, the packet is tagged with the local machine's IP. Hence a more aggressive client firewall may block the response UDP packet since it has a different IP from the outbound request packet.

This can cause clients on Vista such as SQL Server Management Studio and also Sql Profiler to fail to connect to the clustered named instance.

There are various workarounds, one is to add SSMS.exe and SqlProfiler.exe to firewall exclusion list. Another is to specify the tcp port in the connection string when connecting. I tend towards the latter, but then again I am a protocols geek and I setup my servers with known port numbers. We're working with the Windows Cluster team for a solution to this one. Here is a more detailed blog on the subject from Xinwei, one of our protocols guru:

http://blogs.msdn.com/sql_protocols/archive/2006/02/27/unable-to-connect-to-a-sql-server-named-instance-on-a-cluster.aspx

4. User Does Not Have Right To Log Into Their Default Database

I have seen this a few times on the newsgroups, so adding this one too. When you create new users in SQL Server, be sure to allow them access to their designated default database, otherwise they cannot log in!

For example, create a junk1 user like so:

use master
go
create database junk1
go
use junk1
go
create login junk1 with password=N'junk1',
default_database=junk1, check_expiration=off, check_policy=off
go

Now attempt to log in using osql:

C:\>osql -Ujunk1 -Pjunk1 -S.
Login failed for user 'junk1'.
Cannot open user default database. Login failed.

If you examine the errorlog in SQL 2008, you see:

Error: 18456, Severity: 14, State: 40.
Login failed for user 'junk1'. Reason: Failed to open the database specified in the login properties. [CLIENT: 123.23.123.12]

We actually did a ton of work in SQL 2008 to make these 18456 login errorlog messages more human understandable in SQL 2008, so this is nice feature for dba who is wondering "who is trying to log into my server?". In SQL 2005 you need the special 18456 decoder ring:

Sharing IE Links With Live Mesh

2008-06-04T19:35:12Z

In case you have not heard the buzz, we have this new thing called Live Mesh that is pretty awesome. I found out about it whist trying to figure out how to share my Links (c:\users\...\Favorites\Links) folders across multiple machines. I talked to one of my IE buds and he indicated Live Mesh might be the ticket.

With Live Mesh you can add each computer as a node in your mesh. Then you can share folders from your computer into the mesh (and it shares these across all your computers).

To share my Links, I had to play some tricks with Live Mesh, but nothing too serious.

I setup Live Mesh on multiple computers, then on primary computer right clicked on C:\Users\MattN\Favorites\Links and shared it to the Mesh (note these computers are Vista, Live Mesh works on Vista and XP for now). On XP this would be C:\Documents And Settings\MattN\Favorites\Links. I shared my Links folder on one and only one machine (and also at this point consolidated all my actual links on the one primary machine).

Next on secondary machines what will happen is a new shortcut named Links will appear on your desktop once Live Mesh syncs up.

I then closed all IE instances on secondary machines, renamed existing Links to Links1.

Then go to link on desktop, right click and select Live Mesh Options... Change Sync Settings...

This will give you an option on where you want the link to reside, change it to C:\Users\Mattn\Favorites\Links, the folder will move automatically and now you have a shared Links folder, pretty cool!

Going to let this run for a few weeks and see how it works out.

Finding Locals In CLR

2008-05-30T07:25:36Z

Today I'm looking at a few Watson dumps trying to determine why SqlConnection is failing to connect to SQL Server and hanging.

I see stack like below:

0:000> k
0021dc5c 79f40778 kernel32!SleepEx+0x62
0021dc90 7a0561d7 mscorwks!EESleepEx+0xa3
0021dc9c 7a12fc71 mscorwks!CExecutionEngine::ClrSleepEx+0xd
0021dcac 7a0e4d7c mscorwks!ClrSleepEx+0x13
0021dce8 7a082a5f mscorwks!Thread::UserSleep+0x63
0021dd88 652e94cd mscorwks!ThreadNative::Sleep+0xce
0021ddc8 652e9283 System_Data_ni!System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover
0021de20 652e83ff System_Data_ni!System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist

I need to look at CLR locals in SqlClient.SqlInternalConnectionTds.LoginNoFailover, and these locals are intrinsic CLR type long (Int64).

Where does CLR hide these frame local long values?

Using the sos extension I see !dso does not dump intrinsic locals.

!clrstack, wow the output is completely wrong and bizarre.

!dumpstack, !dumpstack -EE, broken.

kP works, somewhat reasonably.

Time to get ugly.

Ok, I locate start of assembly for SqlInternalConnectionTds.LoginNoFailover in debugger and just dump the raw assembly.

I know my variable is passed into function ADP.TimerRemainingMilliseconds, so my plan is to locate this call in unassembly and see how the assembly code packs the value passed to this function, this will give me a clue on where the value is held.

I find it here:

652e942f ff75d8          push    dword ptr [ebp-28h]
652e9432 ff75d4          push    dword ptr [ebp-2Ch]
652e9435 e8d2c3f3ff      call    System_Data_ni!System.Data.Common.ADP.TimerRemainingMilliseconds(Int64) (6522580c)

Good, I see ebp-offset, this looks like stack local. I use kvn to get frame number.

0:000> kvn 10
# ChildEBP RetAddr Args to Child
...
07 0021dd88 652e94cd 06bf6a58 06bf3f90 06bf67cc mscorwks!ThreadNative::Sleep+0xce (FPO: [Non-Fpo]) (CONV: fastcall) [f:\whidbeyrtmlhs\ndp\clr\src\vm\comsynchronizable.cpp @ 964]
08 0021ddc8 652e9283 00000000 00000000 00000000 System_Data_ni!System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(System.String, System.String, Boolean, System.Data.SqlClient.SqlConnection, System.Data.SqlClient.SqlConnectionString, Int64)+0x215 (Managed) [f:\WhidbeyRTMlhs\ndp\fx\src\Data\System\Data\SqlClient\SqlInternalConnectionTds.cs @ 862]

Set the frame:

0:000> .frame 8
08 0021ddc8 652e9283 System_Data_ni!System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(System.String, System.String, Boolean, System.Data.SqlClient.SqlConnection, System.Data.SqlClient.SqlConnectionString, Int64)+0x215 [f:\WhidbeyRTMlhs\ndp\fx\src\Data\System\Data\SqlClient\SqlInternalConnectionTds.cs @ 862]

I then look at registers for frame 8:

0:000> r
Last set context:
eax=00000000 ebx=00000000 ecx=00000000 edx=00000000 esi=0021dc38 edi=00000000
eip=77520f34 esp=0021dbf4 ebp=0021dc5c iopl=0         nv up ei pl nz na po nc
cs=001b ss=0023 ds=0023 es=0023 fs=003b gs=0000             efl=00200202
ntdll!KiFastSystemCallRet:
77520f34 c3              ret

Ok, then 0021dc5c -28 and 0021dc5c -2c should be the 2 DWORDs that compose my Int64 value:

0:000> dd 0021dc5c-2c
0021dc30 76f16110 00000000

For real? I am really not sure. I'll try it on a live debug to be sure.

Connection Pooling, ADO.NET, SPIDs

2008-05-09T01:53:54Z

If you have pooling issues with ADO.NET (leaking connections, pool size exceeded, etc...) one way to slice and dice these with SQL 2005 is to use the SQL 2005 system DMVs.

You can locate leaked connections like so:

select 
session_id, 
datediff(s,last_read,getdate()) as SecondsSinceLastRead,
datediff(s,last_write,getdate()) as SecondsSinceLastWrite,
(select text from sys.dm_exec_sql_text(dec.most_recent_sql_handle)) as LastSqlStatment
from sys.dm_exec_connections dec

The idea being if the connection is properly pooled then last_read and last_write should always be active. Leaked connections will go into garbage collector and sit there for a while still open but not reading and writing.

Next suppose you find leaked ones, how can you correlate these with client code? Here is one way:

select 
session_id as Spid, 
(select login_name from sys.dm_exec_sessions where session_id=dec.session_id) as UserName,
datediff(s,last_read,getdate()) as SecondsSinceLastRead,
datediff(s,last_write,getdate()) as SecondsSinceLastWrite,
text as LastSqlStatment
from sys.dm_exec_connections dec
cross apply sys.dm_exec_sql_text(dec.most_recent_sql_handle)

This will dump out each SPID and also the last TSQL statement to run on the SPID. Then search your source code for the TSQL to locate which part of your code leaked the connection. Not a perfect solution but should get you pretty close.

Suppose you are really stumped and cannot correlate the TSQL. You can get a full memory dump of the client process (I'm assuming like an ASP.NET application here) and then dig through this using the sos debugger extension. I'll post on this later.

Debugging .NET Code

2008-04-23T06:32:00Z

Some days I wonder how customers debug .NET code.

I've been debugging CLR for many years using a mix of techniques (VS.NET debugger and Windbg and logging of course). To be honest I hate debugging in VS.NET, the debugger just annoys me to no end except for very simple debugging scenarios. Once you go Windbg it is hard to go back.

But Windbg and CLR debugging are not a match made in heaven. They work together like Frankenstein's monster and the angry mob work together.

Today I am debugging System.Data.SqlClient and transaction behavior inside CLR stored procedures and TDS protocol.

I know that transaction state flows over TDS protocol using a combination of TDS ENVCHANGE tokens (server->client) plus TDS mars header (client -> server).

What I see is proper setup of transaction state here:

- Tds: Response from server, Version=7.2
+ TDSHeaderFirst: 0x1
- TDSServerResponseData:
     TokenType: ENVCHANGE
   - TokenData:
      Length: 11 (0xB)
      EnvChangeType: Begin Transaction
      NewValueLength: 8 (0x8)
      NewValue: 223338299393 (0x3400000001)
      OldValueLength: 0 (0x0)
     TokenType: DONE
   + TokenData:

0x3400000001 is my transaction id (descriptor). I see it going out on the next request:

- Tds: Query, Version=7.2
+ TDSHeaderFirst: 0x1
- SqlBatchData:
   - AllHeadersData:
      TotalLength: 22 (0x16)
      HeaderLength: 18 (0x12)
      HeaderType: MARS Header
      TransactionDescriptor: 223338299393 (0x3400000001)
      OutstandingRequestCount: 1 (0x1)
     SQLText: exec ClrProc2 N'123'

Next my CLR sproc inserts data into a table which in turn fires a trigger which in turn rolls back the transaction ->

create trigger trig1 on t1 for insert, update as
begin
    insert t2 (f1) values (1)
    select * from t2
    rollback tran
end

It is not recommended to do this in a trigger of course! This is only my little test to see TDS protocol behavior. The CLR sproc is supposed to surpress TDS tokens for internally run commands, which is a nice feature. But how does it do this and keep transaction state consistent with client? I expected it to send back a rollback transaction state ENVCHANGE. I don't see it over the wire.

I do see an ERROR token of state 16 coming back and I suspect this is telling SqlClient to reset transaction state.

Ok, back to debugging CLR. I need to know when this TDS response comes back, how it resets the transaction internal state of SqlClient. I know little to nothing about the internals of how it works, but need to figure it out and debug it quickly without wasting time. Via some digging I found this _currentTransaction was getting set and reset when the ENVCHANGEs come in.

Now, how do I set a breakpoint on the change of _currentTransaction in VS.NET debugger?

Let the fun begin.

First, note that I have a debug version of SqlClient that I just freshly built out of my enlistment and shoved into the GAC. This is one important way to please the temperamental gremlin living inside VS.NET debugger. Ok, crank up debugger.

Ok, get to my code, try to step into SqlCommand.ExecuteNonQuery. Debugger does not step into it.

Tools | Options... Debugging | General-> "Enable Just My Code" checkbox is clear. Why does it not step in?

Ok, the gremlin does not like my System.Data.dll. VS.NET cannot find the symbols for a dll I just build on my local machine. The trick to fix this is to place the symbols right next to the dll so that the debugger does not have to look too hard for symbols. Or you can modify symbol path in VS.NET. I choose to place symbols next to dll, the reload symbols from module window.

Cool, got it working. Now how do I set a bp on change of _currentTransaction? This seems to be the tricky part in CLR. In Windbg I would locate address of _currentTransaction and set a ba w4 <address> on it and call it a day. In CLR there is no equivalent as far as I can tell. Yes, I suppose I can manually set breakpoints on every method of the class that _currentTransaction uses in hopes that the methods are used but this is difficult to do in CLR as well (in Windbg I can set breakpoint on all class methods using a wildcard in one command). It is time to go beg the VS.NET debugger gurus to see if they can help me.

Not yet. Let me try this VS.NET 2008 immediate window, I have been meaning to give it a try.

!help
'!help': not available while Managed only debugging.

Arg. I found a few basic commands that work, but most Windbg commands don't work:

?_currentTransaction
null
?poi(_currentTransaction)
The name 'poi' does not exist in the current context

Ok, I give up for now. I talked to CLR folks and they indicated VS.NET does not have data breakpoints for managed code yet. Bummer.

Writing CLR Stored Procedures

2008-04-22T23:08:12Z

I'm trying to write some CLR sprocs to test out the TDS behavior of CLR sprocs versus T-SQL sprocs for TDS protocol documentation purposes. If you are not aware, we now have the TDS protocol spec on MSDN for your bedtime reading:

http://msdn2.microsoft.com/en-us/library/cc448435.aspx

Note I'm also finishing up a full blown netmon parser for TDS as part of the protocol documentation effort. To ensure this works well I'm investigating the dusty corners of TDS protocol stream behavior to ensure we have it properly spec' d and my parser works too.

On to CLR sprocs topic. First thing I did was crank up VS.NET 2008 and try to create a database project so I can upload some test CLR sprocs. It doesn't work because VS.NET 2008 is smart and detects the target SQL Server is 2008 and not 2005 so it refuses to co-operate and create a nice little CLR project for me. I suppose I could use a 2005 server but I'm lazy.

No worries, I know how to manually upload CLR sprocs with all the magic T-SQL incantations.

Next I do this:

create assembly ClrTestJunkAssembly from N'C:\Project\ADONet\TranStress\bin\Debug\TranStress.exe' WITH PERMISSION_SET=SAFE
go

Msg 10301, Level 16, State 1, Line 1
Assembly 'TranStress' references assembly 'system.enterpriseservices, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(The system cannot find the file specified.)). Please load the referenced assembly into the current database and retry your request.

Note yes I am really really lazy and don't want to create a separate clean library project with my CLR stored proc in it. I just want to upload my test client exe that also just happens to have the sproc class in it and get on with my life. My other non-sproc test code happens to ref System.EnterpriseServices. Arg.

Can I just shovel EnterpriseServices in?

create assembly EnterpriseServicesAssemblyForSQLServerWhoIsTooLazyToLookInGac from N'e:\WINNT\Microsoft.NET\Framework\v2.0.50727\System.EnterpriseServices.dll' WITH PERMISSION_SET=EXTERNAL_ACCESS
go

Warning: The Microsoft .Net frameworks assembly 'system.enterpriseservices, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=x86.' you are registering is not fully tested in SQL Server hosted environment.
Warning: The Microsoft .Net frameworks assembly 'system.directoryservices, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.
Msg 6218, Level 16, State 2, Line 1
CREATE ASSEMBLY for assembly 'System.EnterpriseServices' failed because assembly 'System.DirectoryServices' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message
[ : System.DirectoryServices.DirectorySearcher::SetSearchPreferences][mdToken=0x60000f8][offset 0x0000050E][found address of value 'System.DirectoryServices.Interop.AdsSortKey'] Expected numeric type on the stack.
[ : System.DirectoryServices.SearchResultCollection::RetrieveDirectorySynchronizationCookie][mdToken=0x60001c6][offset 0x00000023][found address of value 'System.DirectoryServices.Interop.AdsSearchColumn'] Expected numeric type on the stack.
[ : System.DirectoryServices.SearchResultCollection::RetrieveVLVResponse][mdToken=0x60001c7][offset 0x00000023][found address of value 'System.DirectoryServices.Interop.AdsSearchColumn'] Expected numeric type on the stack.
[ : System.DirectoryServices.SearchResultCollection+ResultsEnumerator::GetCurrentResult][mdToken=0x60001d4][offset 0x00000033][found address of Native Int] Expected numeric type on the stack.

Arg, you don't have to tell me everything twice, work with me brother. Ok, I didn't like System.EnterpriseServices anyway, it was highly over-rated, so I removed it from my other code (I wasn't using it anymore anyway).

create assembly ClrTestJunkAssembly from N'C:\Project\ADONet\TranStress\bin\Debug\TranStress.exe' WITH PERMISSION_SET=SAFE
go

Msg 6212, Level 16, State 1, Line 1
CREATE ASSEMBLY failed because method 'Main' on type 'TranStress.Program' in safe assembly 'TranStress' is storing to a static field. Storing to a static field is not allowed in safe assemblies.

Ok, ok, SQL, I know this game. I'm starting to get offended.

create assembly ClrTestJunkAssembly from N'C:\Project\ADONet\TranStress\bin\Debug\TranStress.exe' WITH PERMISSION_SET=UNSAFE
go

Msg 10327, Level 14, State 1, Line 1
CREATE ASSEMBLY for assembly 'TranStress' failed because assembly 'TranStress' is not authorized for PERMISSION_SET = UNSAFE. The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission.

Ok, let me try this but it makes me feel strange:

alter database transtress set trustworthy on
go
create assembly ClrTestJunkAssembly from N'C:\Project\ADONet\TranStress\bin\Debug\TranStress.exe' WITH PERMISSION_SET=UNSAFE
go

Ahh! Now that was easy. Next, I have to expose my function as a stored proc. I created my CLR proc like so:

class CLRTestJunk
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void ClrProc1(string param1)
    {
        using(SqlConnection conn = new SqlConnection("Context Connection=true;"))
        {
            conn.Open();
            using (SqlCommand cmd = conn.CreateCommand())
            {
                int v = -1;
                int.TryParse(param1, out v);
                cmd.Parameters.Add("@p1", SqlDbType.Int);
                cmd.Parameters[0].Value = v;
                cmd.CommandText = "insert t1 (f1) values (@p1)";
                cmd.ExecuteNonQuery();
            }
        }
    }
}

Next I run this:

create procedure ClrProc1 as external name ClrTestJunkAssembly.CLRTestJunk.ClrProc1
go

Msg 6505, Level 16, State 2, Procedure ClrProc1, Line 1
Could not find Type 'CLRTestJunk' in assembly 'TranStress'.

Ok, now what? I crank up reflector to see if it's there, yes it's there.

Maybe because I forgot to add the param:

create procedure ClrProc1(@param1 varchar(max)) as external name ClrTestJunkAssembly.CLRTestJunk.ClrProc1
go

Msg 6505, Level 16, State 2, Procedure ClrProc1, Line 1
Could not find Type 'CLRTestJunk' in assembly 'TranStress'.

No. no. no. I'm not beaten yet. Go search around web for 6505, try this:

create procedure ClrProc1(@param1 varchar(max)) as external name [TranStress.ClrTestJunkAssembly].CLRTestJunk.ClrProc1
go

No dice. Ok SQL CLR, you win, I strip out a separate library class with my code. Same problem:

create assembly ClrClassLibraryAssembly from N'C:\Project\ADONet\ClrClassLibrary\bin\Debug\ClrClassLibrary.dll' WITH PERMISSION_SET=SAFE
go
create procedure ClrProc1 as EXTERNAL NAME ClrClassLibraryAssembly.ClrClassLibrary.ClrProc1
go

Msg 6505, Level 16, State 2, Procedure ClrProc1, Line 1
Could not find Type 'ClrClassLibrary' in assembly 'ClrClassLibrary'.

Ok, SQL CLR you are making me feel stupid, I thought I was a kick butt dba until today, now you are just mocking me, thanks a ton. I found another newsgroup entry that gave me an idea:

create procedure ClrProc1 as EXTERNAL NAME ClrClassLibraryAssembly.[ClrClassLibrary.ClrClassLibrary].ClrProc1
go

Msg 6576, Level 16, State 4, Procedure ClrProc1, Line 1
Type 'ClrClassLibrary.ClrClassLibrary' in assembly 'ClrClassLibrary' is not public.

Ohhh! I am getting closer. Here is my class now for reference:

namespace ClrClassLibrary
{
    class ClrClassLibrary
    {
        [Microsoft.SqlServer.Server.SqlProcedure]
        public static void ClrProc1(string param1)
        {

To reference this in SQL Server I have to say ClrClassLibraryAssembly.[ClrClassLibrary.ClrClassLibrary].ClrProc1, not bloody intuitive but ok, I can guess why we did it this way (SQL Server's 4 part naming convention probably won out over CLR).

Ok, make my class public and away I go:

create procedure ClrProc1 as EXTERNAL NAME ClrClassLibraryAssembly.[ClrClassLibrary.ClrClassLibrary].ClrProc1
go

Msg 6550, Level 16, State 2, Procedure ClrProc1, Line 1
CREATE PROCEDURE failed because parameter counts do not match.

Ok, I forgot to add back my param:

create procedure ClrProc1(@param1 varchar(max)) as EXTERNAL NAME ClrClassLibraryAssembly.[ClrClassLibrary.ClrClassLibrary].ClrProc1
go

Msg 6552, Level 16, State 3, Procedure ClrProc1, Line 1
CREATE PROCEDURE for "ClrProc1" failed because T-SQL and CLR types for parameter "@param1" do not match.

Ok, I think I have performed full code coverage for all CLR error messages by now, one more try, the winner is:

create procedure ClrProc1(@param1 nvarchar(max)) as EXTERNAL NAME ClrClassLibraryAssembly.[ClrClassLibrary.ClrClassLibrary].ClrProc1
go

Yes! Now to call my CLR stored procedure:

exec ClrProc1 N'123'

Msg 6263, Level 16, State 1, Line 1
Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.

Whoops. Ok, need to enable CLR:

sp_configure 'clr enabled',1
go
reconfigure with override
go

Ok, one more time:

exec ClrProc1 N'123'

Works! Looks like VS.NET does quite a bit of nasty CLR grunt work for me. But overall what have we learned about CLR stored procedures?

Make sure your class is public.
Avoid references to CLR modules like System.EnterpriseServices, try to keep your code as reference free as possible.
Avoid namespaces.
Ensure you enable CLR on the server.
Probably best to have it in it's own class library to keep things as simple as possible.
Don't change versions of SQL Server or VS.NET will no longer work.
Always learn how to do it using T-SQL versus rely on VS.NET if you want it to work anyway.

You can always sniff the TSQL that VS.NET uses to configure your CLR sproc using SQL Profiler and save this.

Why does Windows Always Want To Hide Things From Me?

2008-04-16T06:10:53Z

Over the years, I find more and more of the Windows UI is designed to hide things like where the files are coming from, file extensions, etc... from me. Why is this? I want to see this stuff.

For example, why can't I get the full path to a file easily? I have to open Run box, then drag and drop file into the Run box, then select file name, then copy it, then go paste it where I need it. I've probably done this a million times in my life.

I found in Vista recently that drag+drop to a console window no longer works, I was heart broken, this saves me tons of time. But I found a cool feature in Vista that makes up for it.

Hold down shift key and right-click on file and you will see a few new options on the menu bar. See the Copy as Path below, this is the one you want. Saves you having to pop the Run menu, very nice!

Hey, I was excited. :)

I found on Vista that whenever I had a networking problem, the Vista UI was trying very hard to hide all networking information from me. The best it can muster is this insult to my networking abilities:

Why? This is all you have for me Vista, I ask? I want to see random Matrix like diagnostic spew and perl scripts scrolling across the screen like in the Hollywood movies. This will tell me Vista is doing something purposeful. Maybe next version.

High Performance Help In VS.NET 2008

2008-03-28T10:05:46Z

Don't know about you, but some days I'm so busy I don't have time to wait while VS.NET spawns dexplore.exe over DCOM and makes bunch of SOAP calls to MSDN to then find the MSDN url to pop context sensitive help for a keyword (yes I debugged it). Don't get me started about updating help topics please wait 5 minutes.

I don't need more proof VS.NET help thinks Moore's Law is a challenge one should take very seriously.

Hence I wrote this VS.NET macro to speed things up for me. Assigned keyboard Alt-F1 to fire off this ReallyFastHelp macro below.

Ok, agreed it's a bit primitive, I haven't written any VB.NET code in years, but it get's the job done. Feel free to spruce it up. The cool thing is it automagically opens a new tab in IE7 for me each time I shell it, leaving a nice tab for each search. Now if I can just figure out how to make VS.NET 2008 remember my keyboard shortcuts after I close and re-open it. Grrrr.

Imports System
Imports EnvDTE
Imports EnvDTE80
Imports EnvDTE90
Imports System.Diagnostics

Public Module Module1
    Sub ReallyFastHelp()
        Dim selection As String
        Dim shellObject As Object
        Dim shellUrl As String

        selection = DTE.ActiveDocument.Selection.Text
        If String.IsNullOrEmpty(selection) Then
            shellUrl = "http://msdn.microsoft.com"
        Else
            selection = Uri.EscapeUriString(selection)
            shellUrl = "http://search.live.com/results.aspx?q=" & selection
        End If

        Dim psi As ProcessStartInfo
        Dim startInfo As New System.Diagnostics.ProcessStartInfo(shellUrl)
        startInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Maximized

System.Diagnostics.Process.Start(startInfo)

End Sub
End Module

MacGruber Transactions

2008-03-26T11:03:23Z

Found an interesting bit of code on the newsgroups the other day .... what I like to call MacGruber transactions. If you are a fan of SNL you know the story.

With System.Data.SqlClient.SqlConnection in .NET 2.0 we added a new connection string attribute named "Transaction Binding" to alter the behavior of how SqlConnection interacts with System.Transaction.

One might believe a transaction always spans a TransactionScope block, this is technically true but the transaction can rollback at any point inside the block. The transaction is still there but it's state has changed.

With SqlClient our default behavior is to use Transaction Binding=Implicit Unbind which means we auto unbind from the transaction scope if the transaction rolls back and switch back to standard SQL auto-commit mode.

Hence work inside the TransactionScope could be half rolled back and half committed if for example the transaction times out half way through the code block. I recommend using Transaction Binding=Explicit Unbind since this gives you deterministic behavior. We are thinking about changing the default to this in future releases of System.Data.SqlClient driver let me know what you think.

Easy Way To Debug SSL Issues With Web Services (Including SQL Server 2005 SOAP Endpoints)

2006-03-13T20:08:00Z

Here is a nice easy way to debug SSL issue with Windows 2003 that I discovered while working on SOAP projects. I think this works on XP SP2 as well have not tested this.

1. Create batch file c:\ssltrace.cmd with following contents:

logman start http_ssl_trace -pf c:\guids.txt -o out.etl -ets
pause
logman stop http_ssl_trace -ets
tracerpt /y out.etl
notepad dumpfile.csv

2. Create file c:\guids.txt with following contents:

{1fbecc45-c060-4e7c-8a0e-0dbd6116181b} 0x000000FF 5 IIS: SSL Filter
{dd5ef90a-6398-47a4-ad34-4dcecdef795f} 0x000000FF 5 HTTP Service Trace

3. On the web service machine run c:\ssltrace.cmd to start tracing, then hit your web service with your POST. Once you are finished testing press the spacebar to in the ssltrace.cmd command window to stop tracing and display the trace file. You should see everything coming in and going out along with error codes, etc...

If you are even more ambitious and want to see the innards of NTLM and Kerberos and LSA chatter, add this to guids.txt ->

{C92CF544-91B3-4dc0-8E11-C580339A0BF8} 0x000000FF 5   NTLM Security Protocol
{cc85922f-db41-11d2-9244-006008269001} 0x000000FF 5   Local Security Authority (LSA)
{bba3add2-c229-4cdb-ae2b-57eb6966b0c4} 0x000000FF 5   Active Directory: Kerberos

Debugging SQL Connectivity 101

2006-02-25T10:40:00Z

Note to self: Things to remember to check next time I can't connect to SQL...

1. Is SQL started? Yes. Is the instance I'm trying to connect to started? Yes.

2. Run netstat -aon on sql box, do I see something bound to 1433 or say my instance port 5555? For example do I see 0.0.0.0:1433 bound? Does the pid match with pid for SQL Server?

3. If I run > telnet servername 1433 <, does this connect? I usually try this one first it is easy and tells you if you have a firewall problem right away. Oh Windows Firewall, you have made my life in SQL Protocols such fun.

4. Try the osql connection test using hard coded protocol and port:

osql -E -Stcp:mysql,5555

5. Hey, try the admin port, does this work? Why does admin port not work with osql? We call this progress my friend.

sqlcmd -E -Sadmin:127.0.0.1

6. Maybe I have a machine with IPv6?

sqlcmd -E -Sadmin:::1

Not even sure if this works, but ::1 is loopback for ipv6, good to know.

7. Did I check SQL SAC? The SQL Server Surface Area Configuration tool can totally block remote connectivity, go check it.

8. Is the SQL Browser service started? Run -> net start sqlbrowser. This service helps the client locate the proper port for named instances.

9. Did I change the port for my default instance from 1433 to something else? Client is not smart enough to figure this out sometimes. Check the ERRORLOG and see what port SQL is binding to.

10. Is IPSec services enabled? This can break connectivity within a domain. Run -> net start policyagent.

11. Check SQL Server Configuration Manager and determine what ports you are really binding to. Please forgive us for the funky GUI here, I agree it is confusing. Read it carefully is my best advice. I find it easier to look at the ERRORLOG to be honest that this GUI.

12. Am I using a clustered instance of SQL? With clustered SQL, the only thing that might throw you off is that you can connect to the virtual ip as well as locally bound ips. It can get confusing. Avoid the local ips and use the clustered ips you see in Cluster Admistrator.

13. Did someone install a virus scanner on your SQL box recently? Maybe it decided to block SQL from talking to the internet by blocking all sockets. Oh yes I've seen this many times.

14. Running any unusual xprocs in SQL lately? I have seen xprocs close our socket handles in the past, CloseHandle is a dangerous API, it does not preclude you from putting in some random value that is somebody else's handle.

15. Rootkits. Did you know that a rootkit can piggyback the same ports you use for normal applications such as IIS and SQL and use these ports while you are also using these ports for normal activities? And everything works fine for both your app and the rootkit? This is exceedingly rare however, but does make a good case for regular network sniffing and intrusion checking.

I'm sure I can think of more of these, later!