Mark Minasi's Windows Networking Tech Page
Issue #50 September 2005
To subscribe, visit http://www.minasi.com/nwsreg.htm.
To unsubscribe, link to http://www.minasi.com/unsubs.htm.
To change e-mail address, switch between HTML or text format, etc., link to
Visit the Archives at http://www.minasi.com/archive.htm.
Please do NOT reply to this mail; for comments, please link to
copyright 2005 Mark Minasi.
- Tech Section
- The Accidental DBA's Guide Part 2: What You've Got To Know About
SQL Server 2005 Express Edition
- Bring a Seminar to
This month brings a sequel (or perhaps it's a "SQL") to what turned
out to be a very popular newsletter, the piece on Microsoft's free SQL
Server engine, MSDE. As I
mentioned in the MSDE newsletter, Microsoft's increasing tendency to
release useful administrative tools that cannot function without a SQL
database engine forces many of us who'd just as soon spend our lives
ignorant of databases to learn just enough about SQL Server
to get it running, keep it secure, and maintain it. For those who
don't want to shell out a few kilobucks for SQL Server itself,
Microsoft's been giving away a free reduced-power version of SQL Server
called MSDE, but that's about to change. On 7 November 2005,
Microsoft will unveil SQL Server 2005 and it's little brother, SQL
Server 2005 Express Edition -- MSDE's replacement. In this
newsletter, we'll revisit the same questions and answers that we covered
about MSDE, but this time I'll tell you what's changed from MSDE to SQL
Server Express. There's lots to know, but I'll try to keep it easy
to follow and practical. But first, a word from our sponsor...
Don't Miss ITEdge Intersection!
Windows Server 2016 is coming, are you ready? Server 2016 and many other important topics are the focus of ITEdge Intersection at the MGM Grand in Las Vegas, Oct 25-29. Your favorite speakers are there, including Scott Guthrie, Brad Anderson, Jeffrey Snover and of course, Mark Minasi. Register today for the conference and a workshop and you can go home with an XBOX One S, Surface 3 table or MS Band 2. Register at www.itedgeintersection.com.
Bring Mark's Windows 10 Support Class and Our PowerShell Classes to Your
Mark has delivered his new "Deploying, Managing and Securing "the Last
Windows: Working with Windows 10" class to nearly a dozen clients, and the
reviews are uniformly great. Designed for the Windows 7 support pro,
this course tells you everything you need to know to support, deploy, or
manage Windows 10 systems. Fast-paced, lecture-based and
entertaining, this course gives you the shortest path to Windows 10
expertise. Learn about Windows 10's completely new licensing
approach. See how to enable the new "parallel universe" security
tools. Discover the cloud-y new tools in Windows 10 like joining a
system not to an Active Directory but instead to a cloud. Find out
what you can learn at our course outline at
To bring this class to your site, just drop us a line at
And while you're at it, are your folks PowerShell adepts? There
really isn't a productivity-enhancer available for Windows support people
like PowerShell. Bring Mark's "Learning PowerShell: Hands-On with
AD, Networking, and More" class to your site and he'll make your
command-line-hating techies into PowerShell fans. Outline at
The Accidental DBA's Guide To SQL Server Part 2:
What You Need To Know about SQL Server 2005 Express Edition
In newsletter #48, I discussed the Microsoft SQL Server Desktop Engine
based on SQL Server 2000 with Service Pack 3, or MSDE 2000 Release A. I
explained that it is a reduced-function version of SQL Server 2000 and that
folks like me (and maybe you) -- people in charge of a network that might not
even use much in the way database servers -- need to know MSDE because so
many modern essential network utilities need a database server, will not
function without a database server. Things like Windows Server Update
Services (WSUS), the neat new free patching tool, or Audit Collection Services (ACS),
a soon-to-arrive tool that lets you centralize your computers' Security logs.
Those two are cool and they're free, but without a database server, you ain't
runnin' WSUS or ACS, and so we become what I've called "accidental database
But MSDE's based on SQL Server 2000, and SQL Server 2000 is being replaced on
7 November 2005 with SQL Server 2005. When that happens, MSDE 2000 Release
A will be replaced by something called SQL Server 2005 Express, "SSX" to those
in-the-know. It is very backward compatible with MSDE 2000, but there are
some significant differences. This month, I want to explain those
differences: MSDE versus SQL Server 2005 Express for the accidental DBA.
I'm not going to talk about the new programming interfaces to the .NET
programming framework, we're not programmers here, and I will studiously avoid
the new PIVOT T-SQL commands that have lots of programmers dancing in the aisles. Nope, I'm
just going to do
the same thing I did in the earlier newsletter, except with an SSX focus. But
it won't take as
long, as a lot of stuff hasn't changed. But before I go any further...
First, let me get the suspense over: if you understood the MSDE writeup,
then SSX is not going to be a hassle. Yes, it's a little more
memory-hungry and a bit slower, but better in almost every way -- in every
significant way -- than MSDE. (So relax, this will be easy!)
- First, I'll list the big changes between MSDE and SSX.
- In the MSDE writeup, I explained the six things that you can do -- and
must do -- to secure an MSDE server, as an insecure MSDE server is the
lowest of low-hanging fruit on a network. We'll revisit those
issues in an SSX context.
- Then we'll see what you'll need to download to run SSX yourself and
install it. (This of course will change in November, as it's still
beta stuff now.)
- MSDE's main control tool was a command line tool called OSQL. The
story is very much the same with SSX -- osql's still around but only for
backward compatibility. There's also a new command line tool called sqlcmd. Its functions are
pretty much of a superset of osql's, though, so it'll be painless to cover.
And unfortunately yes, you'll still do most of your work from the command line. The only
GUI admin tools are a simple but sometimes useful "Surface Area
Configuration" tool (it simplifies turning on or off some of the
security-sensitive points in SQL Server 2005 Express) and an occasionally
useful tool called "SQL Server Express Manager" that "manages" about as
usefully as Dilbert's pointy-haired boss.
- From that point on in the MSDE write-up, I gave you a bunch of cookbooks
-- how to create a database, put a table in it, install a few records,
create user accounts and manage their passwords, view the system's status,
back up, restore and move databases -- and I'll quickly show how they change
SQL Server Express Versus MSDE 2000: It's Almost All Good
In the MSDE write-up, I listed the one thing that MSDE offered that SQL Server
2000 didn't -- it was free -- and the 11 things that SQL Server 2000 had that
MSDE didn't, and decided that the seven limitations that we'd probably notice
would be the lack of SQL Profiler (a great SQL troubleshooting tool), the SQL Books
Online documentation, the Enterprise Manager (the extensive GUI that lets you
tell SQL to do just about anything just by clicking a mouse), MSDE's limits on memory and CPUs, MSDE's
limit of 2 GB maximum size on a database, and finally the MSDE "throttle," a
built-in feature whereby MSDE slowed itself down when too many people used it.
What's the score for SQL Server 2005 Express? Take a look:
- Still no Enterprise Manager. It'd be nice to have it, as it's a
powerful GUI for controlling SQL Server, but let's
face it -- this is free and quite honestly this database engine could, just
as a database engine, serve many small database needs. If Microsoft
gave away the management tool that makes SQL Server 2000/2005 reasonably
possible to run, then they'd probably significantly hurt sales of SQL Server
besides, as I demonstrated in the MSDE write-up and will demonstrate here, we
don't really lose anything, as we can duplicate just about any EM function
from the command line.
- Still no SQL Profiler. It'd be great to have, but again you can
understand why they're not giving it away; SQL Profiler is to SQL
programming what Network Monitor or Ethereal is network troubleshooting.
It's a nice tool, and it's reasonable for Microsoft to charge for it.
- At present, you can find the SQL Server 2005 Books Online on Microsoft's
site for download. But SS2005 is still in beta as I write this, so I can't say for
what you'll be able to get as of 7 November 2005, when SQL Server 2005
launches. As SQL Server 2000's
documentation is online, I hope Microsoft continues with this trend and puts
the final SQL Server 2005 documentation online as well.
- The throttle is gone from SSX. Yay! Previously, the throttle
would cause MSDE to slow itself down even on a light-duty job if too many
people were accessing the database at the same time. Not having to
worry about that now will, I think, greatly expand the pool of people who
are -- or should be -- thinking about using SSX as their database engine
instead of something like Access or MySQL. (Or, in the case of a few
of my projects,
comma-delimited ASCII data files!)
- Any given SSX database can now be up to 4 GB in size. That just
means that each database can't exceed 4 GB in size, and log file sizes don't
count. So, for example, if your SSX system had eight 3 GB-sized
databases, SSX wouldn't squawk. MSDE's limit was 2 GB, so this is a
- MSDE could use up to two CPUs; SSX will only use one CPU. I don't
know what motivated this, but again it doesn't seem unreasonable. I
have a one-CPU system on my network that runs an AMD64 processor -- just
one! -- and it's lightning-fast. Certainly fast enough to see to my
WSUS, ACS, Ultrasound and other needs.
- MSDE could use up to two GB of RAM; SSX will only use one GB of RAM.
Perhaps this is Microsoft's reason why they chose to drop the throttle --
limiting SSX to one gigabyte of memory address space will have the effect of
making it painfully slow for really big applications. But, again,
we're not doing really big applications; we're maintaining databases of
system events or the like. I kinda wish they hadn't done this, but I
can live with it.
- SSX has a very limited-functionality management and query tool called
"SQL Server Express Manager;" MSDE had nothing like that. Its main
value is in doing SELECT statements on system databases, which tend to have
very wide columns and so their output tends to wrap and be almost unreadable
on the command line. SQL Server Express Manager lets you do SELECT
statements into a data grid, which is more visually manageable. Again,
no big deal, but it doesn't make the world any worse, so thanks are in
In sum, then, we lost some RAM and CPU utilization abilities, but gained
bigger databases and, best of all, got de-throttled. Not bad, and it's
Server 2005 Express Versus MSDE Security-Wise
You may recall that securing MSDE involved six issues. First, the sysadmin account named "sa," which every SQL Server has, needed a hard-to-guess
password. Second, many people will choose to forbid their SQL Servers
(whether 2000 or 2005) to allow logins by local SQL accounts like sa, and
instead tell those servers to only accept logins from local- or domain-based
Windows accounts. Third, MSDE patches appear now and then, and today's security
environment requires that we acquire and install those patches quickly.
Fourth, you can mitigate the number of people who can attack your MSDE system by
telling the system not to accept commands over the network, and only accept
database commands from other programs running on the same system. Fifth, by default MSDE only uses TCP port 1433 and UDP
port 1434, so controlling access to those ports can further shore up your MSDE
defenses; in fact, you can usually disable the "SQL Server Resolution Service,"
which is the thing that uses UDP port 1434. And sixth, SQL Server has a
really scary extended procedure called xp_cmdshell that lets anyone who's seized
control of your MSDE/SQL Server box extend that power to your entire system.
That intruder then has LocalSystem powers, so the attacker can grab password
files, delete things, you name it. If the admin passwords are named
unwisely then the attacker might get control of your whole domain, so a weakly
secured SQL Server can potentially lead to the compromise of the entire network.
I explained that over the years, MSDE's setup program's defaults had evolved
so that a minimum-effort MSDE install would require you to create a non-blank sa
password, would disable local SQL logins and only allow Windows integrated
logins, and would configure the MSDE system to refuse to accept commands from
across the network. That was and is a good default stance, as most MSDE
implementations have MSDE acting as a database engine for a local application,
like Internet Information Server. For example, the SQL-dependent app that I've been
using as the classic example, WSUS, is after all just a glorified
SSX's setup program defaults to as secure a stance as MSDE, and adds to it.
- sa password: If you do not specify an sa password, SSX creates a complex
random password for sa, even if mixed authentication is disabled.
This way, even if a bad guy manages to get enough control of your system to
shift the authentication mode to "mixed" they he'll still have to guess a
complex password to do an sa login.
- Authentication: by default SSX only does Windows authentications,
like MSDE 2000A
- Patches: same story as MSDE 2000A although perhaps WSUS and Microsoft Update
will do SQL Server 2005 /SSX patches -- as far as I can see they do not
patch SQL Server 2000 or MSDE 2000
- Network protocols: disabled by default, as in MSDE 2000A
- Ports: SSX uses the same ports as did MSDE 2000A
- xp_cmdshell: now disabled by default, unlike MSDE 2000A
In other words, a default install of SSX is fairly secure. As with MSDE, you may decide that you'll need to loosen up your server's
security a bit, and I'll show you how to do that here. Before I do,
however, I should briefly explain two basic concepts that I didn't cover in the
first write-up: the SQL Server network protocols and server roles.
SQL Server Network Protocols: "TCP/IP" Versus "Named Pipes"
In the MSDE write-up, I discussed the fact that many systems running some
kind of SQL server software need to be able to serve clients that are on different computers
from the server. For example, you could imagine an HR application that employees could access from
their workstations to examine benefits or the
like. In that case, there'd be a piece of client software on each
employee's PC, and a piece of server software -- SQL Server, MSDE, SSX, Oracle,
MySQL or the
like -- on some central computer. The clients would send commands --
they'd "query" in SQLese -- to the server over the network, and therefore the
server must understand network protocols.
By Default, SSX is Network-Deaf
Simple, right? Sure -- probably childishly obvious to many of you.
But, I noted, in many cases MSDE or SSX end up on the same computer as the
client. For example, suppose you're setting up a WSUS server. The
WSUS server is mainly a gussied-up Web application, but it needs a database
engine to keep track of things like details on patches. So WSUS requires a
database engine like MSDE or SSX. Now, as the client (WSUS) is on the same
machine as the server (MSDE, SSX or whatever), then the client/server communication needn't
involve network protocols. Such a server certainly can support
network protocols, and two programs working on the same computer could even talk
between one another via network protocols. But two apps on the same system
needn't do that, as SQL Server supports a special way for programs on the same system to talk
to one another without needing any network protocols, something called "shared
But wait; you can sit at your computer and use Web client software like
Internet Explorer to query WSUS about something. Isn't that a case of
talking to the SQL Server software over a network? Nope. The client
-- IE -- doesn't
talk to the MSDE/SSX program, as it's a Web client, remember? So IE
queries the WSUS server -- that is, a Web server -- and the Web server then
queries the SQL Server sitting on the same system as it. Thus, we said in
the MSDE piece, it was quite feasible to configure an MSDE system that had no
idea how to communicate over a network. As to why we'd do it, one
word: security. A SQL Server that can't talk over a network is a SQL
Server that's hard for a network-borne bit of malware to attack. So MSDE 2000 Release A and, as you'll learn, SSX set
up by default deaf to networking.
But that doesn't mean that you mightn't find yourself at some point having to configure a SQL
Server 2005 Express system to be able to network. So let's talk about how
it does it.
Understanding Inter-Process Communications (IPCs)
The real key here is in the fact that a SQL Server -- a "database engine,"
some would phrase it -- is, like all server software, completely useless unless it
has a means to receive requests from clients. This is not unlike the way
that you (a "buying client") would place an order with a catalog store (a
Suppose you wanted to buy a shirt from L.L. Bean -- how could you do it?
Well, in computer terms, you, the customer, would be a "process," and LLB, the
vendor, would also be a "process," so you'd need what operating system architect
types call an "inter-process communication" or IPC method. L.L. Bean
supports three that I know of -- "walk into the store," "call on the phone," and
"connect to the Web site." Here's how they work:
- Walk-in: you can go to one of their stores, use your hands to
remove the desired item from their shelves, walk to a cashier and proffer
cash, a check, or credit/debit card, and then transport yourself and the
goods back home. You can only do this when their store is open, and
some of their stores are only open certain hours of the day.
- Telephone: you can call their toll-free number and use an audio
transport protocol (your voice, the operator's ears, and the phone system) to identify your
desired goods. I believe they only allow credit/debit cards in this
scenario. L.L. Bean staff then takes the goods off the shelf, wraps them
and ships them to you. You can do this 24 hours a day, seven days a
week, but the transaction must happen in real-time and all at once.
When I say that, I mean that you
can't get halfway through the order, tell the operator, "oh, gosh, that's my
mother at the door, can you hold?" walk away for two hours, come back
and complete the transaction. Transactions are confirmed by the
operator reading your order back to you, because he or she can't see you to
show you what you'rge going to buy. This is different from the walk-in
model because cashiers
typically don't hold up every item and say, "did you really want to buy
- Web: using an HTTP client and an IP-based network called the Web,
you use your fingers, a keyboard and a mouse to choose your desired items.
Like the phone, you use a credit card and the services of the L.L. Bean staff, who packs and ships
the goods to you. Unlike the phone, you can stop in the middle
of a shopping session, come back to it days later and pick up where you left
off, completing the transaction whenever you feel like it. (At least,
that's the way it used to be at L.L. Bean; for all I know, they've got 20
minute time limits on the cookies that store the shopping cart contents.
Let's just assume that it works the way that I've described.)
In other words, the L.L. Bean "buying server process" can work with any of three IPCs --
walk-in, telephone, or Web. Each kind of IPC is different in that it
formats its data differently (physical handling of goods at the cash register,
voice ordering or HTTP packets), confirms the sale differently (one assumes you're
handling the stuff you want, another reads the order back, the third shows it on a Web page with
"click here to continue" buttons), and works either synchronously (once you
start the transaction you've got to finish it in a short time, as in handing
clothes to a cashier or talking to an operator) or asynchronously (the L.L. Bean
Web server remembers where you left off so the session can be stopped and
re-started as often as you like).
Computers have IPCs also. I've already mentioned one -- shared memory.
It's fast and reliable, but it assumes that the client and server are on the
same computer. In the MSDE write-up I talked about a tool called the SQL
Server Network Utility that let you enable or disable any of a number of what it
called "network protocols" but that were really IPCs. MSDE supported a
wide variety including Banyan VINES, AppleTalk and NWLink IPX/SPX, but for
whatever reason SQL Server 2005 and its Express edition only support two IPCs:
TCP/IP sockets and named pipes. (Well, it also supports shared memory, but
that's not a network-based IPC.)
"TCP/IP" = TCP/IP Sockets
When researchers put together the preeminent protocol for what would
eventually become the Internet, they needed a way for one application to talk to
another application over TCP/IP. Recall that two of the guiding principles
of TCP/IP design were first that the Internet might run over slow dial-up (or
worse) links, and that Internet communications might be interrupted for
unforeseeable amounts of time. (That is, when someone nuked New York then
that big FTP download might get delayed a few hours while Internet traffic got
re-routed.) In other words, the advice to folks programming something for
the Internet was "assume that the Net will be slow and unreliable." (Heck,
that still turns out to be good advice. Farsighted guys, those Arpanet
Along those lines, Internet protocol designers put together an IPC called
"sockets." The notion was that a well-written app could just "plug into"
network services and talk client-to-server as easily as an "electrical client" --
like a toaster or a PC -- could plug into a network and get to an
"electrical server" --like the generator down the street. In the
Windows world, you may know that we call the implementation of TCP/IP sockets "winsock."
You identify a socket with just two pieces of information: the IP address
and port number of the service that we're trying to get to. For example, a
Web server at address 100.100.100.100's socket would be 100.100.100.100:80, as
the Web runs on port 80.
Okay, why do we care about this? Because one way we secure our networks
is by blocking particular ports with a firewall or, for many people, blocking
all ports except particular port numbers. So if you need to
turn on network protocols for your SSX server then you'll want to know which
ports it's using. As it turns out, that varies depending on whether your
SSX system does sockets ("TCP/IP") or named pipes.
So I'll get to the port numbers in a moment.
SQL Server in its various incarnations will communicate using sockets, if the
developer of the app wants it to. But when would a developer do this?
Sockets are set up to require a relatively small amount of setup because of the
anticipated slow nature of the Net, and to be resilient when connections drop.
But socket programming is a bit tougher than named pipes, which I'll get to in a
minute. Therefore anyone using sockets probably expects clients to connect
from across the Internet, over dial-up lines or just plain unreliable links in
Confusing as it sounds, both named pipes and sockets run atop TCP/IP.
But the SQL network configuration tools refer to "named pipes" and "TCP/IP."
Where's sockets? The answer is that "TCP/IP" really means "sockets;" it's
a bad label.
What Port Numbers Do Sockets Use?
Presuming that you've got to enable network protocols, and presuming that
you've got to enable sockets -- oops, I mean "TCP/IP" -- then what ports must
clients be able to connect to in order to get SQL connectivity? It
- The default instance of SQL Server runs on TCP port 1433.
- Any named instances get whatever port's available. In my
experience the first named instance gets TCP port 1033, the second 1034,
etc. But you don't know for sure.
How, then, does a client contact a named instance? In SQL Server 2000,
a service called the "SQL Server Resolution Service" allowed clients to ask it,
via UDP port 1434, "what port does such-and-such named instance run on this
server?" For some reason, the SQL Server Resolution Service has a new
name, the "SQL Browser." No matter what the name, it does the same job.
Assuming that (1) a piece of SQL client software wants to access a named
instance, and (2) it wants to use TCP/IP sockets to do it, then that client
contacts the SQL Browser service via UDP port 1434 and asks for the named
Let me stress that: the SQL Browser is, as far as I can see, only
required if (1) you've enabled network protocols and (2) enabled sockets.
Otherwise, it's unnecessary, at least according to my tests. That's probably why it's installed but
disabled by default on SSX systems.
To review, then: if you enable SQL's support of sockets by enabling the
"TCP/IP" network protocol, then
- If you're using the default instance, then clients only need access to
TCP port 1433 and the SQL Browser is unnecessary.
- If you're using a named instance (and as you'll see, SSX uses one named
"SQLEXPRESS" by default), then clients must be able to access UDP port 1434
to talk to the SQL Browser service and then whatever port the instance got
-- usually in the neighborhood of 1033.
Years ago, no one knew that the Internet would become the network platform of
choice, and so old versions of Windows, Unix, OS/2 and other operating systems
had little or no socket support. But they needed some way to let different
processes communicate, so they needed an IPC. Virtually
every OS has supported some form of shared memory IPC, as it makes perfect sense
when two programs on the same system want to talk to one another. But all of these
operating systems were very network-aware -- local area
network-aware, that is. So the creators of those OSes knew that they'd want
to have some systems act as servers and some as clients, and that the clients and
the servers would talk together on some kind of local area network. That
meant that they
needed an IPC that didn't just let two programs in the same computer talk; no,
these IPCs would have to allow two different programs running on two different computers
talk, assuming that the two computers were on the same LAN. In contrast to
TCP/IP's design goals of supporting networks that were wide-area, slow, dial-in
and unreliable, the IPCs of these pre-Internet OSes assumed that they'd run
atop LANs, which were fast and reliable. The IPCs, therefore, could be
In over twenty years of working with various kinds of LANs, I've seen
simple networking software and complex networking software. But
whether simple or complex, big or small, there's one thing that they
all do: file sharing. So when the folks who designed
networking operating systems needed a model for designing a useful,
easy-to-understand IPC, they looked at file sharing and said "hmmm... we
need to design an IPC that programmers will understand, and by now they
know how to connect up to a file server; can we make an IPC that looks a
lot like connecting to a file server?" The result was named pipes.
Ever done a NET USE to IPC$ on a computer? You've talked to a
named pipe. Named pipes let Program 1 in Computer A to talk to Program 2 in Computer B by
essentially creating something very much like a file share... but whose data
lives not on a hard disk but instead in a block of memory.
Like file sharing within a LAN, named pipes are easy to work with, making
life easier for programmers, but a mite chatty protocol-wise and, well, not a
really great idea to do across the Internet, at least not without a very close
attention to detail. Also, named pipes really do piggyback on the
file server service; turn off the Server service on a server offering some
service via named pipes and blammo, the named pipes don't exist any more.
Which brings us to the question that you're waiting for me to answer: what
ports do named pipes use? The same ones as the SMB file sharing service:
- UDP ports 137 and 138
- TCP port 139
Ugh, yuck. Don't want to expose those guys over the Net.
So if your SQL Server were intended to talk over the Internet, then sockets
("TCP/IP") is the way to go -- named pipes would probably be a bad idea.
If you intend the server to talk to systems on the same local area network, then
named pipes would work fine -- but so would sockets. Personally, I'd
always use sockets over a network of any kind, unless for some reason the SQL
server's client couldn't speak sockets.
The bottom line, then, is this. If you can keep network
protocols disabled, then do it. But if you must open them, then
try only enabling "TCP/IP." Enable just that and try your client;
if it works, great. If not, enable named pipes.
As administrators in the Windows world, we sometimes need to take a user
account and give it a set of powers. For example, when first setting up a
network, we'll create an account for ourselves and give it the same power as the
Administrator account; that way, we needn't log in with the anonymous and
un-auditable Administrator account, and can instead log in with an account of
our own but still have the powers and abilities that we need to get the job of domain administration done. How do we do that? By putting our
account into some group, of course -- Domain Admins, in this case.
In the SQL Server world, things work differently. For example, you've
already met the "sa" account, a built-in account that can do anything on a given
SQL Server. But suppose you wanted to create a second all-powerful account
called "sa2?" You learned in the MSDE piece how to create local SQL
accounts, so creating the sa2 account is a snap. But how to achieve its
apotheosis -- how to elevate it to SQL godhood? Not with groups.
Instead, SQL Server has built into it a number of "fixed server roles."
Why'd they do this? I think there are probably two reasons. First,
SQL's been around longer than Windows, and has its own notion of how to secure
things that doesn't exactly match the Windows model, and, second, the SQL Server
product -- the original Sybase program -- also predates Windows and I suspect
that the server role notion came in then; backward compatibility necessitated
keeping it around.
I won't go into much more detail here, as it's mostly beyond the scope of
what we need to do as administrators. But I brought it up so that you'd
understand that (1) we're going to want to create a second sa-like account
for reasons that will become clear, (2) how to assign a local SQL login to a
given server role -- I'll show you how later -- and (3) that the role we're
looking for is called "sysadmin." It's the "I can do anything" role.
Downloading SQL Server 2005 Express and Friends
Again, SSX is still in beta, so I may have to revise these instructions come
November. But if you want to play with SSX or use it in lieu of MSDE --
which I've done and have found no problems with -- then you'll want to go to
www.microsoft.com/downloads and pull down several items.
First, get the documentation. Go to
search on "SQL server 2005 documentation." Look for two downloads:
- "SQL Server 2005 Express Edition Documentation and Samples:
September 2005 Community Technology Preview (CTP)" and
- "SQL Server 2005 Community Technology Preview (CTP) September 2005:
Download both of them. The second one is the 2005 version of that convenient SQL Books Online that
Microsoft furnished for SQL Server 2000. It contains the Transact-SQL docs
and we'll need them, as some of the commands that we used before have changed.
Next, get the SQL Server Express program itself. Search on "sql
server 2005 express ." You'll get several hits, but just grab two of them:
- "Microsoft SQL Server 2005 Express Edition - Community Technology
Preview (CTP) September 2005." This is SSX itself. And when you go to
download it, there might be a newer version or, if you're reading this after
November, there will be a final version.
- "SQL Server 2005 Express Manager - Community Technology Preview (CTP)
June 2005." This is the graphical
tool that I've mentioned. It's not wonderful but it's better than
nothing and you may find it useful for some ugly SELECTs.
Finally, you'll need a new version of the .NET Framework, version 2.0.
Now, this gets a bit tricky, so permit me to beg you to pay close
attention to this point, or you'll be in for some frustration. The .NET
Framework version 2.0 is in beta as I write this, and the most recent version is
beta 2, which appeared in May. Now, if you search microsoft.com/downloads
for ".NET 2.0" then you'll get a link to download a file named dotnetfx.exe, and
the Web page will call it "beta 2." But if you were to look at the
product version (right-click the file, choose Properties, and click the Version
tab, then click the "Product Version" label) then you'd see that you downloaded
the "2.0.50215.44" version of the .NET framework.
Unfortunately, the September beta of SSX's Setup program doesn't like that
particular version of .NET 2.0's Beta 2. Yup, you read that right -- you
there are actually different versions of Beta 2 of the .NET Framework 2.0. Here's where you find
the one that you need:
- At www.microsoft.com/downloads, search for "SQL Server 2005 Express
- In the search results, choose "Microsoft SQL Server 2005 Express Edition
- Community Technology Preview (CTP) September 2005." You'll recognize this as
the same page that you downloaded the SSX beta from.
- Below the "Instructions" heading, notice the bullet point
"Prepare for installation."
- Under the bullet, you'll see one labeled "Step 4:" which includes a
hyperlink saying "the 32-bit version of Microsoft .NET Framework 2.0." Download
and install that version of .NET 2.0. It will have a product
version of 2.0.50727.26.
If you don't do that, then when you try to install SQL Server 2005
Express on your system, then SSX's Setup will tell you that
"SQL Server 2005 CTP Setup has detected incompatible beta components from
Visual Studio or SQL Server. You must run the build cleanup wizard from
the CD to remove previous SQL Server components and .NET Framework components,
and then run SQL Server CTP Setup again. For detailed instructions on
uninstalling SQL Server builds, see the SQL Server 2005 CTP readme file."
Summarizing, then, be sure that you get the right .NET Framework 2.0 beta 2.
You can double-check that you got the right one by looking at the file size and
- A file named dotnetfx.exe with a size of 22,989 KB and a product version
of 2.0.50215.44 will not work with the September CTP beta of SSX.
- A file named dotnetfx.exe with a size of 22,978 KB and a product version
of 2.0.50727.26 will work with the September CTP beta of SSX.
Now, this may cause you some headaches if you're playing with the beta of the
upcoming Visual Studio, as it too needs .NET 2.0. Uninstalling a VS beta
just to load an SSX beta might not be worth it. (Sounds like a job for
VMWare to me.) By the way, this Setup program needs Installer 3.0.
If you're running XP SP2 or 2003 SP1 then you've got it; otherwise, go to
Downloads and get it.
Sorry to make such a big deal of this .NET 2.0 stuff, but it caused me quite
a bit of frustration, and I don't want any of you having to deal with that kind
Installing SQL Server 2005 Express
Armed with those five downloads, we're ready to install.
Remove Any Old SSX or SQL Server 2005 Betas
The SSX setup program can get confused when installing a beta atop an older
one, so if your system has a copy of SQL Server 2005 beta or SQL Server 2005
Express beta on it, then remove those old betas before installing a new copy of
SSX. That goes double for any other .NET 2.0's you've got on your system,
for what will probably be obvious reasons. None of this will hopefully not be a problem with the final version.
Install .NET 2.0 Programming Framework... the Right One!
SQL Server 2005 and its little brother Express need .NET 2.0, so install the
Microsoft .NET Framework 2.0 Beta 2 (version 2.0.50727.26, recall) on your SSX test system. Just
double-click on the EXE you downloaded from Microsoft and a wizard starts.
Click Next, agree to the EULA, then click Install and wait for .NET 2.0 to
install. (You may wish to rent and view a video while waiting.) When it's
done, click Finish. Then reboot the system, even if the .NET framework
setup program doesn't tell you to, or you'll have to do it partway through the SSX setup.
Install SQL Server 2005 Books Online and SSX Books Online
Install both documentation files and you'll have both the main SQL Server
2005 and the SSX documentation on your hard disk. (Interestingly enough,
2005 Books Online is the largest file that you'll have to download!)
Install SQL Server 2005 Express
Now you're ready for the main event. You've already downloaded
SQLEXPR.EXE, a 55 MB file; double-click it to start it installing. That
unpacks the setup files, and leads to the EULA; accept it and click Next.
No, let me take that back -- read the EULA and accept it. The SQL Server
and .NET EULAs must be the most obnoxious in Microsoft's world, as they include
my "favorite" clause:
"You may not disclose the results of any benchmark test of the Software to
any third party without Microsoft's prior written consent."
Whenever I go to a big Microsoft event, it's inevitable that someone will
trot out the latest database benchmark numbers "proving" that SQL server is the
fastest database engine in the galaxy. That's why I think it's such a hoot
that Microsoft says that only they can release performance figures.
I wonder when they'll release "Microsoft Transporter" that moves matter
instantly like the one in Star Trek. You'll have to agree to the
Transporter EULA before you can use it, of course. And, not surprisingly,
Microsoft Transporter wouldn't work.
But the EULA would say that you couldn't tell anyone else how well or badly it
worked without Microsoft's consent, so other would-be customers wouldn't know.
(Hmmm, maybe buying some of that Microsoft stock isn't such
a bad idea after all.) Ah well; in any case, resistance is futile, so
agree to the EULA and click Next. You'll then see a page named "Installing
Prerequisites." Once it's done click Next. The wizard will
disappear for a second.
It may look as if the setup program is done, but it's not -- it's
just setting up another wizard. In a minute or two, you'll see "Welcome to
the Microsoft SQL Server Installation Wizard." Click Next and you'll get a
report about whether or not your system's ready for an SSX install. If not
then you'll get a recommendation about what to do to set it to rights. In
my experience the SSX setup program may complain that your system doesn't meet
SSX's minimum hardware requirements, but that's just a warning. When
satisfied that you've made SSX's setup program happy, click Next.
Once in the setup wizard proper, fill in a name and organization, and click
Next. On the next page, you can select what parts to install; unless
you're doing SQL development, just click Next to take the defaults.
The "Authentication Mode" page comes up next. It suggests "Windows
Authentication Mode," which you may recall disables the local accounts like sa
and instead relies on local Windows administrator-level accounts. We
can un-do this later if we wish, so just click Next again.
The next page, "Error and Usage Report Settings," lets SSX tell Microsoft
about any errors that pop up, as well as information about which features you do
or do not use. Leave it checked or un-check it as you like and click Next.
The next page says you're ready to install, so click the "Install" button.
When the wizard's finished, click the "Next" and then "Finish" buttons.
SSX is installed!
Command-Line SSX Installs
SSX downloads, recall, as a file named sqlexpr.exe. You can use
this to do command-line installs in much the same way that you did for MSDE.
SQLEXPR supports the options you met in the text about MSDE. Reviewing
those options from MSDE:
- SAPWD= lets you specify a password for the sa account. This option
was mandatory for MSDE but not SSX. If you omit this then SSX just
creates a random complex password for the sa account.
- DISABLENETWORKPROTOCOLS=0/1 works just as in MSDE. By default SSX
does not accept database commands across a network. You can change
that by either specifying DISABLENETWORKPROTOCOLS=1 on the SQLEXPR command
line, or you'll see in a bit that there's a new tool called "SQL
Server Configuration Manager" that lets you enable or disable network
protocols on an already-installed system.
- SECURITYMODE=SQL works exactly as it did on MSDE. By default SSX
only supports Windows Integrated logins. As with MSDE, you can change
that after the install with a Registry hack. There is no option on
SECURITYMODE to tell SSX to allow only Windows logons; just skip the SECURITYMODE option in order to restrict SQL to just Windows integrated
- INSTANCENAME=, as with MSDE, lets you create a "named instance" of SSX.
(Review the MSDE newsletter text for details, but basically an instance is just another
copy of SQL Server on a computer. Ever since SQL Server 2000 came out,
it's been possible to have more than one copy of SQL Server running on a
given computer.) What's different about SSX, however, is that by
default SQLEXPR creates an instance named "SQLEXPRESS." To defeat
this, and cause SQLEXPR to just create an instance with the default name,
then add the command-line option INSTANCENAME=MSSQLSERVER. Again,
skipping the INSTANCENAME= parameter will cause your SQL Server 2005 Express
to be installed as a named instance named "SQLEXPRESS."
SSX brings a few new options.
- -q says not to show the initial part of SQLEXPR where it unpacks itself
to a temporary file on the hard disk.
- /qn and /qb say to run the installation wizard without prompting the
user. /qb does not prompt the user but shows the wizard's progress.
/qn does not prompt the user and also does not show the wizard.
- ADDLOCAL= says to which of SSX's options to install, and it's a
It seems as if every edition of the currently available, free version SQL
Server has a "must include" option or two. MSDE 2000 A's was "SAPWD=;"
SSX's is the "ADDLOCAL=" option. If you skip an ADDLOCAL= option, then the
wizards stop before finishing the SSX install, and you end up with no
explanatory messages and no SSX. That's because ADDLOCAL has a default
value: "install nothing." Beyond its default value, ADDLOCAL takes
seven possible values when setting up SQL Server 2005 Express (it's got lots
more if you're installing the full SQL Server 2005): All, SQL_Engine,
SQL_Data_Files, SQL_Replication, Client_Components, Connectivity, and SDK.
Note that if you search the Web for these ADDLOCAL options, then you will come
across a number of pages that claim that SSX will take a bunch of other
settings; trust me, it won't, I tried them all. The best source of
information about acceptable ADDLOCAL values came from a file called
TEMPLATE.INI that I found by extracting all of the files in SQLEXPR.EXE (the -x
option lets you do that) and searching on "addlocal" in that directory.
Again, I expect the documentation on this to improve markedly when Microsoft
completes the product in November.
So what should you specify? Here's what they do.
- SQL_Engine and SQL_Data_Files appear to do exactly the same thing.
They install the basic database program itself, its client programs (osql,
sqlcmd, the "SQL Server Configuration Manager" snap-in and the "Surface Area
Configuration" (SAC) utility.
- "SQL_Replication" starts where SQL_Engine and SQL_Data_Files leave off,
adding code for database replication (the Program Files/Microsoft SQL
Server/90/COM folder gets about 4 MB larger if you specify this option) and a directory Repldata in
Program Files/Microsoft SQL Server/MSSQL.1/MSSQL. (At least, that's
the location if you create a default instance.) It also creates a
folder in Program Files named "MSXML 6.0."
- "Client_Components" does not install the database server program at all.
It does load all of the client programs (osql, sqlcmd, SAC, the
snap-in). It seems to be the setting to use if all you want on your
computer are the tools to control remote SQL servers.
- "Connectivity" installs the same things as Client_Components, but adds
the "MSXML 6.0" folder in Program Files.
- "SDK" installs the same things as Client_Components but adds a folder
Program Files/Microsoft SQL Server/90/SDK, which contains a few XML files
- Finally, "All" loads everything that the other six options offer.
Inasmuch as we're admin types of folks running apps simple enough to probably
not need replication, the "SQL_Engine" option should do fine. But note a
couple of traps about ADDLOCAL very carefully. First, you must type the
ADDLOCAL options using the exactly correct case. "Sql_engine" will fail;
Second, when ADDLOCAL fails, it's not very good about telling you. For
example, if you omit an ADDLOCAL option on SQLEXPR then SQLEXPR runs for a
while, decides that it's got nothing to install, and just exits, leaving you no
error messages -- just a little mess left behind in Program Files\Microsoft SQL
Server\90. Oh, and one more thing about ADDLOCAL -- you can specify more
than one option. Just put the options after ADDLOCAL= separated commas
with no spaces.
To tell SQL Server 2005 Express to install itself with the basic SQL engine
without any replication support and Windows authentication only, then, you'd
sqlexpr -q /qn addlocal=SQL_Engine
Type that, walk away, return in 15 minutes, and you ought to have a working
SSX running. And yes, you are reading that right -- the first option's
preceded by a dash (-q), the second by a forward slash (/qn) and the final one
with neither (addlocal=SQL_Engine).
As with MSDE, you can also put the options in an INI file and put that on the
command line. Here's what you might find in a basic INI file:
The line with the semicolon at the beginning of it is commented out -- I just
wanted to show you where to the line that would allow mixed authentications, but
I didn't want it to have any effect, so it's commented out with the semicolon. In that
file, I've specified a name and company name and told SQLEXPR to just install
the basic server. Remember, Microsoft recommends a named instance of
"SQLEXPRESS." If you want to install a default instance, then change the
line to "INSTANCENAME=MSSQLSERVER." Yes, it's annoying to have to type in
an instance name when starting up an administrative tool, but I imagine that
most vendors will follow Microsoft's suggestions, so I'll run most of the rest
of my examples assuming that you've got a named instance of SQLEXPRESS. Securitymode and
sapwd work the same way in SSX installs as they did in MSDE installs.
To use this, create a directory c:\ssx and type the above lines into Notepad.
Save the file as c:\ssx\sqlsetup.ini. Start up SQLEXPR like so:
sqlexpr -q /qn /settings c:\ssx\sqlsetup.ini
Walk away and in no time you'll have an SSX installed. Now you can
install SQL Server 2005 Express either interactively, with a long command line
or an INI file. Again, I recommend that however you install SSX, install
it with its default installation instance name of SQLEXPRESS, as I imagine that
most SSX-dependent apps will use that name.
Patching Your SQL Server 2005 Express System
As I write this, there are no patches for SSX and it's not clear if Microsoft
Update/WSUS will cover SSX patches, as it doesn't do SQL Server 2000 or MSDE
patches. But I would imagine that when the released version of SQL Server
Express does ship
then you'll find SSX patches at
http://www.microsoft.com/technet/security/current.aspx, as you'd expect to
for all of Microsoft's products.
Verifying Your SQL Server 2005 Express Setup
Assuming that you've used the /qn or /qb switches, then SSX's setup program
is pretty quiet. (That's what you asked it to do, after all.) As
I've explained, even without the "be quiet" switches, there are cases
where SSX doesn't understand an ADDLOCAL= option and responds by just stopping
in the middle of the install, having installed nothing. When that happens,
then you'll see that you have a folder named Program Files\Microsoft SQL
Server\90\Setup Bootstrap, but no other folders in Program Files\Microsoft SQL
But assuming that you did specify your Setup parameters right (or
wimped out and used the GUI, as if we'd ever do that!) and wanted a running SQL
Server 2005 Express database server, then what would you expect to see if the ?
Well, first, opening up Manage Computer will show that under Services and
Applications you see a snap-in that I've mentioned before called "SQL Server
Configuration Manager." You'll get this no matter how minimal your install
was -- you get it even if you just installed the tools, and no engine. To see if you've got a running database server, open up the Services
applet in the same area of Computer Management and you should see a service
named "SQL Server (MSSQLSERVER)" if you told SSX not to install a named
instance, or "SQL Server (SQLEXPRESS)" if you allowed SSX's Setup
program to install its preferred instance name.
Note that the service name that you'd use for a NET STOP command varies
between a named instance like the one that SSX installs by default, or a
non-named instance. (Notice that Microsoft calls an instance that doesn't
get a name a "default instance" but I'm trying to avoid that phrase because of
the fact that a default installation gets you a named instance,
but a non-default installation gets you a default instance.
Arrgh.) Here's the rule:
- If you modified SSX's installation to create the non-default setting of
a "default instance," then the service name is MSSQLSERVER, so for example
you'd stop it from the command line by typing NET STOP MSSQLSERVER.
- For any named instance, that instance's service name is MSSQL$instancename.
So, for example, the named instance that you get by running SSX's default
setup (named instance of "SQLEXPRESS") would have the service name MSSQL$SQLEXPRESS,
and you'd type NET STOP MSSQL$SQLEXPRESS.
If you played around with multiple instances of MSDE then you may have
noticed that MSDE named SQL's folder in Program Files in the same way as SSX
still names services, with names like Program Files\MSSQL$instancename.
But SSX doesn't name its Program Files directories like MSDE does. If you install
several instances of SSX
then you get a folder structure that looks like
- Program Files\Microsoft SQL Server\
- MSSQL.3\MSSQL\ etc
Want do see what instances are on a given SQL Server 2005 system?
There's a Registry key that lists the instance names as well as their numbers;
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL.
You could, then type
reg query "HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL"
If you do that, you'll get an output something like this, run on a system
where I installed three SSX instances:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL
SQLEXPRESS REG_SZ MSSQL.1
SQL2 REG_SZ MSSQL.2
MSSQLSERVER REG_SZ MSSQL.3
Where Everything Is
Now that you've got SQL Server Express loaded, where did Setup put
everything? You'll notice several items.
First, if you look in your MSSQL directory (skip back a few lines to see the
folder structure that SSX setup created to see where MSSQL is) then you'll see a
DATA folder. That's where any databases go by default. As with every
other version of SQL Server that I've ever seen, there are already some
databases there. In case you're new to SQL Server, the files with the
extension .MDF are the actual databases, and the ones with the extension .LDF
are the transaction log files.
Second, click Start/All Programs/Microsoft SQL Server 2005 CTP and you'll
notice a folder named "Configuration Tools" that contains the SQL Server
Configuration Manager and the SQL Server Surface Area Configuration tools, as
well as a tool that controls what gets reported to Microsoft, assuming that you
let the SSX Setup program report errors and feature usage to Microsoft.
(I'm guessing this will disappear in the final release.) We'll see what we
can do with the Configuration Manager and the SAC tool later on.
Controlling SQL Server 2005 Express With sqlcmd
Whew; we've seen about 15 ways to install SSX and now we've got it running.
Let's take it out for a spin. The command-line tool is, as I've said, no
longer called osql (although that's still around), it's sqlcmd. It takes
the same options as osql: -E for Windows integrated logins, -U loginname
and -P password to specify a login name and password for a local SQL
login, -S servername\instancename to log onto either a different
machine's server, or to log onto an instance on the local machine that's not the
Here are two tricks that you'll find useful. First, you can use a
period to stand for "this local server." That's useful when you want to
log onto SSX's default named instance of "sqlexpress." We never worried
about this about MSDE because MSDE default Setup options installed the default
instance rather than a named one. Second, if you don't specify either
-U and -P to do a SQL login, or -E for integrated Windows authentication
login then sqlcmd assumes -E.
Assuming that you want to log onto your newly-installed SQL Server 2005
Express, open a command prompt -- do not use the one that you installed from,
because it doesn't have the updated PATH variable that it'd need to find sqlcmd!
-- and type
sqlcmd -S .\sqlexpress
If you read the MSDE piece then you'll remember that like osql, sqlcmd is picky about the case of options: -S,
-U, -E or -P are different than -s, -u, -e, or -p would be. You'll get
that simple prompt:
Remember also that you type Transact-SQL (TSQL) commands into these prompts.
The TSQL commands can be multi-line commands, but sqlcmd won't do anything with
a command until you type "go" followed by an Enter. You can try this out
with what is probably the simplest query you can address to a SQL Server:
type "select @@servername," Enter, and then "go," and Enter. You'll get an
output like this:
1> select @@servername
What's that @@ thing, you say? SQL Server's got a handful of things you
can query to ask about the system. They're called "scalars" because
there's just one value, rather than the column of data you'd expect from most
SELECT statements. Some others include
- @@connections: the number of attempted connections since this instance
of SSX was last started
- @@busy and @@idle tell you how many "time ticks" the server has been
busy and idle; @@timeticks tells you how microseconds are in a tick.
- @@servername, as you've seen, returns the name of the server
- @@servicename returns the name of the service related to this instance,
the name that you'd use to NET START/STOP it
- @@total_errors, @@total_read, and @@total_write disk-specific activity
-- the number of disk errors (zero, I hope!), reads and writes since the
service was last started
- @@version tells the version of SQL Server running, the OS it's running
atop, and a few other details
But let's get back to seeing how to do things with SSX from the command line,
and how they're different than MSDE.
Creating And Deleting Databases
In the MSDE write-up, I showed you the T-SQL commands to create and delete
databases. They haven't changed, so if you'd like to follow along on the
example database that I'll be using then just type in these queries:
1>create database myfirst
If all goes well, you'll just get another 1> prompt. If you wanted to
delete it, then "drop database myfirst" would do the job, just as it did in
Scripting SQL Queries
Just as in MSDE, you can take any SQL query and type it into a Notepad file
and save it as a simple ASCII text file. People give such files the
extension ".sql," but you needn't do it; any extension works. sqlcmd will
then execute it if you call sqlcmd with the -i option, as did osql.
Assuming you'd created your file with the query and called it myquery.sql, you'd
tell sqlcmd to execute the file like so:
sqlcmd -S .\sqlexpress -i myquery.sql
No surprise there -- MSDE could do that. But sqlcmd also lets you start
up a query from a file right inside a session with the ":r" command. If I
were in the middle of a sqlcmd session, then, I could start myquery.sql from
right inside the session, like so:
(... results follow ...)
You can even build queries with variables in them. Suppose your query
file myquery.sql looked like this:
select $(colmn) from $(tabname)
We could then invoke it like so:
sqlcmd -S .\sqlexpress -i myquery.sql -v colmn="name,age" tabname="folks"
A nice extension to SQL's scripting abilities. As with osql, you can
also put any query that you like directly into sqlcmd with the -Q option:
sqlcmd -S .\sqlexpress -Q "select all from folks"
Creating SQL Tables and Adding Records
Again, good news for MSDE vets: it's identical to the method of creating tables and
adding records was in MSDE with osql. These statements, the same ones we
used with MSDE, will create the Folks table and populate it:
create table folks
( name varchar(30) not null primary key,
zip int not null )
Create the records like so:
insert into folks values('Mark Minasi',23456)
insert into folks values('Horatio Hornblower',30101)
insert into folks values('Stephen Maturin',23464)
insert into folks values('Jack Aubrey',87981)
insert into folks values('Thomas Cochrane',51931)
insert into folks values('Richard Bolitho',10595)
Don't forget the "go!"
List Databases And Tables
Same as in MSDE, but sysdatabases becomes sys.databases and sysobjects
To see the databases on a system:
select name from sys.databases
To see the tables in database "myfirst:"
select name from sys.objects where type='U'
Displaying Values With Select
No change from MSDE.
Reconfiguring SQL Server 2005 Express
Here there have been some changes. Let' s start from the no- or
Changing SQL Server 2005 Express Between Mixed and Windows Authentication
Almost no change. It's Windows by default, change it by going to
HKEY_LOCAL_MACHINE\ SOFTWARE\Microsoft\ Microsoft SQL Server\MSSQL.n\MSSQLServer,
"MSSQL.n" is the instance on your system. (Remember that Registry key that
tells us which instance name connects to what Registry key? This is where
we use it.) Inside that you'll find a Registry entry LoginMode. Set
it to 1 for Windows-only authentication, 2 for mixed (either Windows or local
SQL logins) authentication. Restart the SQL service to see the changes
Limiting SSX's RAM Usage
Given that SSX has been trimmed to never use more than 1 GB of RAM, I'm not
sure anyone even cares, but if anyone does, then works exactly as it did
in MSDE with osql, with one difference: the minimum RAM that SSX needs is 16 MB,
four times larger than the minimum memory cap that MSDE allowed.
Enabling or Disabling Network Protocols
The tool to do this under MSDE, the "SQL Server Network Utility," is gone,
and it's replaced by the "SQL Server Surface Area Configuration" (SAC) tool.
By default SSX does not allow any database queries from across the network; SAC
lets you change that. You can enable any one of three network protocol
- TCP/IP which, recall, really means "sockets"
- Named pipes
- TCP/IP and named pipes
Some database client tools are built to do just sockets, others just named
pipes, some -- like sqlcmd, for example -- do either. You may have to
experiment to figure out which yours does. Recall that I said earlier that a few of the old
protocols that MSDE 2000/SQL Server 2000 supported (Banyan, AppleTalk, Netware) don't
work any more.
To reconfigure network protocol support, open up SAC with Start / All
Programs / Microsoft SQL Server 2005 CTP / Configuration Tools / SQL Server
Surface Area Configuration. Then follow these steps to see where to
reconfigure network protocols:
- Click "Surface Area Configuration for Services and Connections."
- On the left-hand side of the screen, you'll see a server icon for each
instance of MSDE that you've got running on the system, and an icon for SQL
Server Browser. Open the one for the instance that you want and you'll
see "Database Engine;" open that and you'll see two options, Service and
- Click "Remote Connections" and you'll see the option to disable all
networking ("local connections only") or networking ("Local and remote
connections") and, under that, the three options - TCP/IP, named pipes,
- Choose "Using TCP/IP," "Using Named Pipes only," or "Using both TCP/IP
and named pipes."
The chances are good that if you're just serving clients on your LAN then
just named pipes will do. If this system accepts connections from
anywhere on the Internet then the chances are that "TCP/IP" is the way to
go. If you don't know, then turn them both on and experiment to see
what your client needs.
- Click OK and then OK when the reminder appears that you must restart the
instance's service before the changes take effect.
- Close SAC.
SAC also lets you turn services on or off. Remember, if you have named
instances of SSX and you enable TCP/IP, then you must turn on the SQL Browser
service; otherwise you don't need it. You can turn SQL Browser on from SAC.
While we're here, I should mention that SAC is also the place to
re-enable xp_cmdshell, if you need to. On the opening screen of SAC,
choose "Surface Area Configuration for Features" and again you'll see an icon
for each SQL Server 2005 Express instance; open it and "Database Engine" and
you'll see an option 'xp_cmdshell." There's a check box that lets you
re-enable it. But don't, if you don't have to.
SAC also has a partner sac.exe that lets you configure this stuff from the
command line. It's located in \Program Files\Microsoft SQL
Server\90\Shared. It will basically either export a current configuration
or import a configuration file. As the formats of the import and export
files are identical -- it's an XML-formatted ASCII file -- then you can just get
one system the way you like it, export its configuration and keep the file handy
for SAC-ing other systems.
Managing User Accounts in SQL Server 2005 Express
You can't get SQL Server to do anything for you unless you're logged in, and
you need some kind of account to log in. Recall from the MSDE article that
there are two kinds of accounts, a Windows account that's been authorized to log
onto the SQL server, or an account that exists solely in the SQL Server itself
called a "SQL login." That "login" is important -- a "user" means
something else, as particular databases have users. Servers have
"logins." Yeah, it seems weird, but again SQL Server has always had its
own security model that's actually fairly fine-grained.
There were, you may recall, three steps to getting an account access to a
- First, you either created the local SQL login, or you granted a Windows
account -- either a local or domain account -- the right to log onto the SQL
- Then you granted the account (Windows or SQL login) the right to access
the database. You couldn't actually do much of anything with this
- The third step was to grant that account the ability to use
particular SQL commands -- SELECT, INSERT, DROP, EXEC, that kind of thing.
SSX still has those three steps, but some of the commands have been
"deprecated," which is software industry talk for "we're still supporting this
in this new version, but it probably won't be around in the next
incarnation of SQL Server." So let's learn the new commands.
Creating a Local SQL Login
In MSDE, it was the "exec sp_addlogin" command. Now it's CREATE LOGIN.
It looks like
create login username with password='password'
So, to create a local SQL login named "lesspower" with password "swordfish,"
create login lesspower with password='swordfish'
Remember, if you want to play around with local accounts then you've first
got to reconfigure SSX to allow mixed authentication.
Allowing a Windows Account To Log Onto An SSX System
Using Windows integrated authentication, we just log onto a Windows box as
some account and then log onto the SQL Server, saying in effect "you know me."
Well, right out of the box an SSX system knows the local Administrator account
and in fact anyone in the local Administrators group... but that's about it.
To introduce another user, we'll need another version of the CREATE LOGIN
command. It looks like
create login [domain\username] from windows
If we had a local account named "wally" on SSXTEST, then the command to let
Wally log onto the SQL Server would look like
create login [ssxtest\wally] from windows
Deleting SQL Logins and Disconnecting Windows Users
Get rid of a SQL login or uninvite a Windows user from being able to log onto
the SQL Server with the new DROP LOGIN accountname command. We'd
get rid of Wally and lesspower like so:
1>drop login [ssxtest\wally]
2>drop login lesspower
Changing a SQL Login's Password
More changes: to change a login's password, forget exec sp_password;
now it's ALTER LOGIN. If you're a regular old user and want to change your
password, you've got to know the old one and type
alter login loginname with password='newpassword'
For example, lesspower could change his password from "swordfish" to "marlin"
with this command:
alter login lesspower with password='marlin' old_password='swordfish'
If, on the other hand, you're logged on as a "sysadmin," SQL-ese for an
administrator, and you want to change someone's password, then you needn't know
the old password -- just leave the "old_password=..." option off. For
example, you've read that your SSX systems's Setup program created a complex
random password for sa. But what if you want to log on as sa, given that
you don't know the password, nor does anyone else? Simple: log onto
the Windows system running the SSX server as an administrator and do a simple
Windows-integrated logon. SQL Server 2005 Express recognizes all local
admins as having the "sysadmin" role. You can then change sa's password to
"complexsapassword" with the following command:
alter login sa with password='complexsapassword'
Listing SQL Login and Windows User Accounts
Now that we've got all of these folks, how can we see a list of accounts that
SQL will permit to log in? With this query. Type
select left(name,40), type from sys.server_principals where (type='G'
or type='S' or type='U')
You'll get an output that looks something like this:
NT AUTHORITY\SYSTEM U
NT AUTHORITY\NETWORK SERVICE U
The "U" accounts are Windows users, the "S" accounts are SQL logins, and the
"G" accounts are recognized Windows accounts.
Making A New sa: Adding Server Roles
But suppose we wanted to create a second sa-type account? How would we
do it? Well, we know how to create a login, with CREATE LOGIN. But
how to make it an administrator? By assigning it to something called the
sysadmin "fixed server role." You do that with the sp_addsrvrole built-in
procedure. We could create an "sa2" account with full powers like so:
1> create login sa2 with password='complexsapassword'
1> exec sp_addsrvrolemember 'sa2','sysadmin'
There are other server roles; take a peek at the SQL Books Online for more
Giving an Account Access to a Database
Now that we've either got the Windows account recognized by the SQL Server
2005 Express system, or a new login, then we found in MSDE that it couldn't do
much of anything. The next thing we did was to grant access to a
particular database with the stored procedure exec sp_grantdbaccess. Now
it's a CREATE USER command. "User," you see, means "someone who can use
this particular database" in SQL-ese. So first you've got to USE the
database, then type create user loginname. For example,
1> use myfirst
2> create user [ssxtest\wally]
3> create user lesspower
You can un-user someone with the DROP USER command.
Giving an Account Access to SQL Commands on a Database
Once we've gotten a server to recognize someone (create login) and gotten a
database on that server to recognize someone (create user), then it's time to
figure out what kind of power that someone should have on particular tables on
that database. That's what GRANT does, as we saw in MSDE. Good news
-- no syntax changes. You'd let lesspower do SELECT and INSERT on the
myfirst database like so:
2>grant select,insert on folks to [ssxtest\wally]
To remove access, REVOKE also works as it did in MSDE.
In the MSDE write-up, we saw how to back up and restore databases, and
how to remove a database altogether from a server ("detach" is the SQL-ese) and
install it ("attach") it to another server. This will be useful if we've
ever got to decommission one server that uses SSX and want to install the
database on another SSX server.
Backing Up and Restoring Databases
Good news -- no syntax changes. It's BACKUP DATABASE and RESTORE
New syntax for this one, unfortunately. You detach a database as before
with the exec sp_detach_db stored procedure. For example, we'd detach
myfirst like so:
1>exec sp_detach_db 'myfirst','true'
Then, once it was detached, we could take its files "myfirst.mdf" and "myfirst_log.ldf"
and put them somewhere else -- say, on a new server in a new directory called
c:\dbs. We'd tell that server -- another SSX machine -- to use that
database with a modified CREATE DATABASE command. It looks like
1> create database myfirst on (filename='c:\dbs\myfirst.mdf')
2>log on (filename='c:\dbs\myfirst_log.ldf') for attach
Once that's done, the database is back in action.
Summary: SSX/T-SQL Cheat Sheet
This newsletter didn't turn to be as huge as the first MSDE one, but
it's still big enough. Let's finish off with a cheat sheet
summarizing the "how do I?"'s we've covered.
How To Run An SQL Statement in sqlcmd
sqlcmd.exe is the command-line tool for issuing SQL statements to MSDE
or SQL Server. Use either the -E option to log in using your currently
logged-on Windows credentials or the -U username -P password
to log on with a local SQL login account. The -E is actually
unnecessary, as it's a default option. Example:
C:\>sqlcmd -S .\sqlexpress
C:\>sqlcmd -U sa -P bigpassword
(do not type the number and ">" sign; that is the osql prompt)
|create SQL login account
||1>create login sue with password='suepwd'
|Delete SQL login account
||1>drop login sue
|Change password on SQL account
||1>alter login sue with password='newsuepwd' old_password='suepwd'
|Change password on SQL account without knowing current
password (admins only)
||1>alter login sue with password='newsuepwd'
|List all SQL login accounts
||1>select left(name,40), type from sys.server_principals
or type='S' or type='U')
|Grant a Windows account the right to log onto the MSDE
||1>create login [acme\joe] from windows
|Shift focus to a particular database on the server
|Grant a SQL login or Windows account the right to access a
database (this only "introduces" an account to a database --
account can now USE database, but can't query the db)
2>create user sue
3>create user [acme\joe]
|Revoke an account's right to access a database
2>drop user sue
|Grant a user the right to use a particular SQL statement on
a given table
2>grant all on customers to sue
3>grant select, insert, update on customers to [acme\joe]
|Take away a right to use a particular SQL statement
2>revoke select on customers to sue
3>revoke all on customers to [acme\joe]
|Create a database
||1>create database salesdb
|Delete a database
||1>drop database salesdb
|Create a table in a database
||use the database, then "create table"
2>create table customer
3>(custid char(7) not null primary key,
|Delete a table in a database
||use the database, then "drop table"
2>drop table customer
|Create a new record in a table
2>insert into customer
4>where name='Phyllis Scott'
|Show all records in a table
|List all databases on a system
||select from sys.databases
|Control column width in a SELECT statement
2>select left(name,9) as name
|List all tables in a database
||select from sys.objects
|Back up a database
||1>backup database salesdb
2>to disk =
|Restore a database
||1>restore database salesdb
2>from disk =
|Detach a database from a server so it can be removed or
2>exec sp_detach_db salesdb
|Attach a database, perhaps to "introduce" a set of database
files just moved to a server
||Create database ... with attach
||1>create database 'salesdb' on
|Enable network protocols
||Surface Area Configuration utility
|Disable network protocols
||Surface Area Configuration utility
|Enable SQL logins
||change loginmode to 2 (see "loginmode" earlier in document)
|Disable SQL logins
||change loginmode to 1 (see "loginmode" earlier in document)
I hope to see some of you at one of these shows!
- I will be keynoting the free Tampa IT Pro Camp 2016 on 20 August 2016.
Great speakers, sign up now to learn great stuff from some terrific
and tell 'em Mark sent you.
- I will be presenting at IT/Dev Connections in Vegas the week of 10
October. Http://www.itdevconnections.com/dc16/Public/Enter.aspx for more
- I will also be speaking at the Intersection show also in Vegas the
week of 24 October. Visit https://next.devintersection.com for
- December TechMentor is in Orlando this year and I'm keynoting about
Server 2016. https://techmentorevents.com/Home.aspx for all the scoop!
Bring Mark to your site to teach
I'm keeping busy doing Active Directory and Security seminars and writing,
but I've still got time to visit your firm. In just two days, I'll make
your current NT techies into 2000, XP, Active Directory and 2003
experts. (And better yet they won't have to sit through any Redmondian propaganda.) To join the large
educational, pharmaceutical, agricultural, aerospace, utility, banking,
government, telecommunication, law enforcement, publishing, transportation,
and other organizations that I've assisted, either take a peek at the course
outlines at www.minasi.com/presentations.htm,
mail our assistant Jean Snead at Assistant@Minasi.com,
or call her at (757) 426-1431 (only between noon-5 Eastern time, weekdays,
Until Next Month...
Have a quiet and safe month.
Please share this newsletter; I'd like very much to expand this periodical
into a useful source of NT/2000/2003/XP information. Please forward it
to any associates who might find it helpful, and accept my thanks. We
are now at over 40,000 subscribers and I hope to use this to get information
to every single Mastering 2003, XP, NT and 2000 Server reader. Thanks for
letting me visit with you, and take care. Many, many thanks to the
readers who have mailed me to offer suggestions, errata, and those kind
reviews. As always, I'm at http://www.minasi.com/gethelp
and please join us at the Forum with technical questions at
To subscribe, visit http://www.minasi.com/nwsreg.htm.
To change e-mail, format, etc., link to
To unsubscribe, link to http://www.minasi.com/unsubs.htm.
Visit the Archives at http://www.minasi.com/archive.htm.
Please do NOT reply to this mail; for comments, please link to
All contents copyright 2005 Mark Minasi. You are encouraged to quote this
material, SO LONG as you include this entire document; thanks.