Getting the Most out of SQL Server 2000's Query Analyzer, Part I

,

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

Rate

5 (2)

Share

Share

Rate

5 (2)