Using the Windows Debugging tool WinDBG you can attach a live process and debug. You can set up break-points at different function calls and debug when the process hits and stops to the break-points. When you do not have the source code (which is true for most of the products that we use as end user or support) this technique can be very useful. Today I will show you guys how to attach a UDL file to WinDBG to see your call flow under the hood. You can apply the same technique to attach your client application that is connecting to a database and having a connectivity issue.
If you do not have WinDBG installed already then you can download the tool from the web WinDbg (32-bit) WinDbg (64-bit). There are three versions of the debugger package: 32-bit, x64 bit and ia64-bit. To select the right version, see Choosing a 32-bit or 64-bit Debugger Package.
Then you need the symbols. You can download the symbols or you can use Microsoft Symbol server. The following KB has the detailed steps to use the Microsoft Symbol server.
Use the Microsoft Symbol Server to obtain debug symbol files
Attaching a UDL file process to WinDBG
We will attach a Universal Data Link (.udl) file with the WinDBG and debug the process as we try to make a connection to a SQL Server. UDL files are most commonly used to trouble shoot connectivity issues. If you are not familiar with ULD file then please review the following link and create a test.udl file on your desktop.
Creating and Configuring Universal Data Link (.udl) Files
Let’s follow the steps below to test the connectivity using the UDL file and then attach the process:
1. Open the test.udl file that you save on your desktop.
2. Go to the provider tab and Select “Microsoft OLEDB provider for SQL Server”
3. Click ‘Next’ then type the SQL server name that you want to connect.
4. Select Windows Integrated Authentication (assuming your windows account has access to the SQL server) and click “Test Connection”. You should see a message “Test Connection Succeeded”
5. From the task manager check the process name, for UDL this is rundll32.exe
6. Open WinDBG and go to File --> Attach a Process --> from the list select rundll32.exe and click OK.
Note: There can be multiple rundll32.exe; by clicking the plus sign next to rundll32.exe make sure that the rundll32.exe you have selected is running the test.udl file that you have saved on your desktop.
The rundll32.exe process running the test.uld file is now attached to the WinDBG. If you try to do anything in the UDL files GUI you will find everything is frozen and even you will not be able to move the window. To debug a process we need to stop the process in predefined places so that we can check the call stack during that state of the process and anything else that we want to check as we would do when analyzing a hang dump. Setting breakpoints allows us to stop the process to predefined locations. Now let’s look at how can we setup breakpoints in the attached process.
Setting up Breakpoints:
To setup a breakpoint you have to know the name of the function and the dll where you want to setup the breakpoint. In our case we will try to make a connection to a SQL server from the UDL file and then debug the process. While using a UDL file you can force a protocol to be used when making the connection to the database server. For example to force TCP protocol you have to specify ‘tcp:’ at the beginning of the server name. When making a connection using TCP we know it will use some sort of windows socket API calls for example the connect() function. This is just an assumption but with a quick web search we can verify if connect() is a valid Windows API call and in what dll this function is included. Usually at the very end of the API document you will see the dll name for the function.
The above document shows the dll name for the connect function is Ws2_32.dll. So let's add a breakpoint for the connect () function.
0:001> bp Ws2_32!connect
Similarly we can find few other Windows API calls that we think will be used when making a connection to the SQL server from test UDL file. For example:
So let’s set breakpoints for all of them. You can then check the list of the breakpoints using ‘bl’ command to make sure that the breakpoints are actually added.
If you add an inaccurate breakpoint or one that you don't need you can remove the breakpoint as below. To remove breakpoint you need to use the number of the breakpoint in the list.
In this case we removed the breakpoint for send() function just to demonstrate the ‘bc’ command. So, let’s add it back.
Now we have attached the process and setup breakpoints in three function calls that we think will be used when making a connection from a UDL file to SQL server. So it’s time now to start the debugging and see if the process stops to the breakpoints we have set.
Debugging the process:
As I mentioned earlier after you attach a process it will be frozen or stopped. In our case we attached the UDL. If you go to the UDL GUI you will not be able to do anything. You won’t be able to press any button or even move the window. The steps below show how to start the attached process again and debug.
1. Type 'g' in the WinDBG and hit enter to let the process run again.
2. Now you should be able to go to different tabs of the UDL file. Set the timeout to 600 in the ‘Advanced’ tab, so that we have enough time to hit different breakpoints before the connection times out. Also make sure you have typed and selected appropriate values for the connection.
3. Hit the “Test Connection” button. You will see the process has hit the breakpoint 0; WS2_32!connect and stopped. Now you can run any WinDBG command to check the process state.
4. Lets type 'kL' in WinDBG and hit enter to view the call stack. The call stack shows very clearly that the process is stopped at WS2_32!Connect() function call and it also shows what other function calls this thread has made so far.
5. Now to let the process run again type 'g' in WinDBG and hit enter. The process will hit the next breakpoint WS2_32!send.
6. Let’s do a 'kL' again to see the call stack.
7. Let’s do another set of ‘g’ ( to let the process go) and ‘kL’ (to view the call stack). You will see the process hit the third breakpoint we have set WS2_32!recv.
8. Keep doing 'g' and then 'kL' alternately until you see a success or failure message from the UDL file. A successful connection makes about 5 pairs of send() and recv() API calls.
So using this technique you can troubleshoot a connectivity issue (however I will not recommend this as the first thing that you should try J) and try to determine how far it went before the failure. You can also attach the SQL server service process at the same time (DO NOT consider attaching a production SQL server or a SQL server that is used by other people)and check what is going on in the SQL side. To keep things simple we only viewed the call stack as we hit the breakpoints. But obviously you can dig into all sorts of things as needed to trouble shoot the issue. You can set break points to the places where you think issue may be happening or the function call that you see in the error message call stack. This technique can be used to troubleshoot any process. Good Luck!
Author : Mohammad(MSFT) SQL Developer Engineer, Microsoft
Reviewed by : Enamul(MSFT), SQL Developer Technical Lead , Microsoft