Introduction
The first time I started up Query Analyzer after upgrading my
client tools from SQL Server 7 to 2000, I noticed the Object Browser
immediately. Playing around a bit, I tinkered with the Transact SQL
debugger and generally just explored some of the new functionality. Most
of it was quite useful, but not exactly straight-forward. When I began
looking into getting more out of some of these new features, I found the documentation on Query Analyzer
was intermingled with the rest of
Books Online and I thought then how it could easily be overlooked. About a
month ago, I was talking with a developer about some issues he was having
debugging code, looking at execution plans, and trying to determine the best
places to put indexes. I started asking him some questions about how the
development team was using Query Analyzer and quickly realized that they saw it
basically as an environment to type in their stored procedures and write simple
queries. They weren't using any of the main features, and the reason was
because they weren't aware of how to use them.
This series will hopefully be a
helpful "How To" guide to maximizing the use of Query Analyzer.
There are quite a few great database tools out there other than Query Analyzer,
but the main advantage of Query Analyzer is it comes with SQL Server, installs
by default as part of the client set, and is very powerful in its own
right. If we're supporting SQL Server, we'll always have Query Analyzer.
The key is to understand what Query Analyzer can do for us, the
kind of information it can provide us, and its limitations. This
article will cover the basics of starting up Query Analyzer and connecting to
SQL Server. I'll introduce the various ways to execute Query Analyzer and
talk a bit about authentication methods and a new tool provided for us by
Windows 2000. We'll also take a quick look at a problem with linked
servers, Query Analyzer (or any client, for that matter), and Windows
authentication.
For
those who've been using Query Analyzer, most of the information in this article
will be old hat but hopefully there will be some new information for everyone. We'll start from the very beginning, to ensure we leave no
gaps. Query Analyzer has a lot of functionality, and some of it can
get pretty in depth. As with most good tools, it takes a little bit
of work and some time to really learn all of the features and options in
order to put the tool to maximum effect. So without further ado, let's
dive right in!
Starting Query Analyzer
There are several ways to start Query Analyzer. The first is
from the Programs sub-menu:
The second is from the Tools menu of Enterprise Manager:
A third way is to simply execute the program (isqlw) from Start
>> Run:
We'll see more about bringing up Query Analyzer via isqlw in a
bit when we talk about the RunAs command.
Connecting to a Server
Upon entering Query Analyzer, we're prompted to connect to a
server. The connection dialog box is the same we'll see in other SQL
Server tools such as Profiler. With it we can choose the server to connect
to, as well as the connection method:
One area that I've seen issues with is understanding the two
different authentication methods. I have seen developers take their NT
(Windows) authentication and try and use the username and password using SQL
Server authentication. This usually occurs when the developer is logged
into the workstation as one user and is trying to get access to the SQL
Server using another NT login.
Windows authentication deals with domain or local computer user
accounts. SQL Server authentication deals with SQL Server logins created on the
particular SQL Server, which means SQL Server must be running in mixed
mode. Obviously, one cannot authenticate Windows accounts using SQL Server
authentication.
Under Windows NT 4.0, there aren't too many
options. Windows authentication is going to match the user account
logged into the workstation. With Windows 2000, however, there is the
RunAs command. The general syntax is:
RUNAS [/profile] [/env] [/netonly] /user:<UserName> program
An example of its use to bring up Query Analyzer is:
runas /user:MyDomain\User2
isqlw
This will allow us to use Windows authentication with Query
Analyzer under the User2 account. The User2 account is only active for
Query Analyzer. When we exit Query Analyzer, nothing will be running under
the User2 context.
Digressing a little, this is a good practice for privileged
accounts. Best practices state that we shouldn't do day-to-day activities
such as checking email, writing status reports, etc., using a privileged account
because whatever account we use for those tasks is the most vulnerable to
compromise. From a risk perspective, if a non-privileged account is
compromised, the potential damage is far less than if a privileged account is
compromised. By utilizing the RunAs command, we can carry out our daily tasks
with a non-privileged account, and if we have a second account with the
appropriate privileges, we can use it only in the specific context of certain
applications. For other SQL Server utilities, given default paths, here
are the runas commands:
Enterprise Manager (SQL Server 2000):
runas /user:MyDomain\User2
"mmc.exe /s \"C:\Program Files\Microsoft SQL Server\80\Tools\BINN\SQL Server Enterprise Manager.MSC\""
SQL Profiler:
runas /user:MyDomain\User2
profiler
As can be seen, the RunAs command is very helpful from a
security perspective. Running in a non-privileged account with the ability
to go to privileged mode has been around in the Unix world for a long time with
the su command (superuser). It's
nice to see this security measure has come around to the Windows world.
Linked Servers, NT Authentication, and "Double Hop"
One other issue I've seen that can throw a developer or DBA into fits
involves querying against linked servers. The root of the issue involves
Windows authentication and the concept of the "double hop," which is
prohibited by Windows authentication under NT non-Kerberos environments (such as
NT 4.0).
Most of the time when we're using Query Analyzer, we're sitting at a
workstation accessing a physically different computer running SQL Server.
For instance:
In SQL Server we can create a linked server from one SQL Server to
another. Diagramming this:
However, we may be running QA from our workstation, and
we're writing queries on one SQL Server that may need to include data from a
linked server.
If we're using SQL Server authentication to get to the first SQL
Server, and the SQL Server uses SQL Server authentication to get to the linked
server, we can get at our data without issue. SQL Server is able to make
the connection to the second server by using the SQL Server login we specified
when we created the linked server.
However, when we connect to a SQL Server using Windows authentication (specifically
NTLM, the method of authentication for NT 4.0) and that SQL Server then attempts
to use Windows authentication to the linked server, we'll get one of the
following errors:
Server: Msg 18456, Level 14, State 1, Line 1 Login
failed for user '\'
or
Server: Msg 18456, Level 14, State 1, Line 1 Login
failed for user 'NT AUTHORITY\ANONYMOUS LOGON'
The problem is with NTLM. SQL Server is having to take the
Windows authentication passed to it by the client and then authenticate using
the same Windows authentication to the second server. If we think of going
from one computer to another as a hop, we have to traverse two hops to get from
client system to the linked server. NTLM does not support such a "double
hop" and as a result, when the second hop is attempted, SQL Server must try
and make an anonymous connection, since it has no other credentials which to
use.
As a result, we fail and receive the error message above. This problem
is not limited to SQL Server, as it can occur in FrontPage 2000 under similar
conditions when trying to work with security. Kerberos does not have such a
limitation and SQL Server 2000 running on Windows 2000 with Active Directory and
Kerberos will not have the same issue. Windows authentication can then
traverse the double hop. If that's not our setup, however, the only
workaround is to make the second connection via SQL Server authentication.
This gets around the double hop issue, though admittedly it means the second
SQL Server has to run in Mixed Mode. Now, one thing to remember is if
we're running Query Analyzer at the console of the SQL Server and not from a
separate client is that we'll not encounter the double hop. The double hop
occurs when we have to go from one computer to another, and then finally to a
second. Also, if we have jobs on a particular server that need to access a
linked server, those jobs can work with a link via Windows authentication, since
we're still only talking about a single hop.
Wrap Up
In this article we've covered the basics of starting up Query Analyzer and
authenticating on SQL Server. We looked at several ways to execute Query
Analyzer and we also looked at the RunAs command. The RunAs command allows
us to be logged into our Windows 2000 or higher workstation with a
non-privileged account while still being able to carry out our administrative
duties with the level of permissions we need. We also took a quick
loop at authentication and the problems we encounter with the double hop.
In the next article we'll take a closer look at the Object Browser and the
Transact-SQL Debugger. The Object Browser is the most visible new feature
of Query Analyzer, so we'll take the time to look at it in some detail.
We'll also walk through the use of the Transact-SQL Debugger so that we might
leverage it for future development efforts. A good understanding of these
two tools can save us a bit of time here and there and so we'll drill down on
them.
References
- Linked Server Double Hop Issue: http://support.microsoft.com/default.aspx?scid=kb;en-us;Q238477
- RunAs for Administrative Context: http://support.microsoft.com/default.aspx?scid=kb;en-us;Q225035