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 http://www.minasi.com/edit-newsletter-record.htm.  Visit the Archives at http://www.minasi.com/archive.htm.  Please do NOT reply to this mail; for comments, please link to www.minasi.com/gethelp.  Document copyright 2005 Mark Minasi.

What's Inside

• News
  • Join Me At a Seminar!
• Tech Section
• The Accidental DBA's Guide Part 2: What You've Got To Know About SQL Server 2005 Express Edition
• Conferences
• Bring a Seminar to Your Site

News

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...

My "Windows 7 Support" Class is now Available as an Audio Course, in Two Versions

I'm incredibly busy traveling to consulting and teaching sites, so unfortunately I don't have any public XP-to-Windows-7 courses scheduled, but if you've been waiting to be able to buy the audio version of the course, then I'm very pleased to announce that it's out, and in two flavors:  a 9-CD set for those who already know Vista but need to be sharp on Windows 7, and a 20-CD set for those who sat back, kept supporting XP and waited for The Good Windows to appear.  (Just so you know, we Vista guys are jealous of the bunch of you.<g>)

Find out more at www.minasi.com/xptowin7audio and remember that (1) it's never too soon to start your Christmas shopping and (2) they make great stocking stuffers!  (And before you ask, the Server audio course is on the way, I promise.)

Rhonda Layfield's In-Depth Three-Day Hands-On "The Holy Grail of Windows Deployment" Class Comes to Northern Virginia September 21-23

The Doctor is in!  Rhonda Layfield, owner of deploymentdr.com and world-famous expert in Windows deployment tools, is running a neat-looking three-day hands-on class in the Dulles area.  You can read about the class here:

http://www.deploymentdr.com/index.php?page_id=29

And when you're ready to sign up, click here:

www.minasi.com/seminar-register.htm and then choose Rhonda's session.

Tech Section

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 administrators."

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 in SSX.

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 2005.  And 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 plus!
• 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 order.

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 still free.

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 Web application. 

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 memory."

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 "buying server.").  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 this?"
• 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 dudes.)

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 general.

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 depends.

• 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 instance's port.

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.

Named Pipes

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 chatty bandwidth-wise.

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.

Server Roles

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 www.microsoft.com/downloads and 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: Books Online"

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:

1. At www.microsoft.com/downloads, search for "SQL Server 2005 Express September."
2. 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.
3. Below the "Instructions" heading, notice the bullet point "Prepare for installation."
4. 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 product version:

• 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 of agita. 

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 logons.
• 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 mandatory option. 

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 and DLLs.
• 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; "SQL_Engine" succeeds.  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 type

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:

[Options]
username="Mark Minasi"
Companyname="Bigfirm Industries"
Addlocal=SQL_Engine
Instancename=SQLEXPRESS
;Securitymode=sql
sapwd=ComplexSAPassword1

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 Server.

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\
  • 80\
  • 90\
  • MSSQL.1\MSSQL\
  • MSSQL.2\MSSQL\
  • 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 default instance.

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:

1>

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
2> go
SSXTEST\SQLEXPRESS

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
2>go

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 MSDE.

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:

1>:r myquery.sql
(... results follow ...)
1>

You can even build queries with variables in them.  Suppose your query file myquery.sql looked like this:

select $(colmn) from $(tabname)
go

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:

use myfirst 
create table folks 
( name varchar(30) not null primary key, 
zip int not null ) 
go

Create the records like so:

use myfirst
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 becomes sys.objects.

To see the databases on a system:

use master
select name from sys.databases
go

To see the tables in database "myfirst:"

use myfirst
select name from sys.objects where type='U'
go

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 little-change stuff.

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 take effect.

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 options:

• 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 Remote Connections.
• 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, or both.
• 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.

Re-Enabling xp_cmdshell

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 database:

• 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 service.
• 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 however.
• 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," we'd type

create login lesspower with password='swordfish'
go

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
3>go

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'  old_password='oldpassword'

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:

                                         type
---------------------------------------- -----
sa                                          S
BUILTIN\Administrators                      G
NT AUTHORITY\SYSTEM                         U
NT AUTHORITY\NETWORK SERVICE     	    U
BUILTIN\Users                               G
lesspower                                   S
ssxtest\wally                               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'
2>go
1> exec sp_addsrvrolemember 'sa2','sysadmin'
2>go

There are other server roles; take a peek at the SQL Books Online for more info.

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
4> go

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:

1>use myfirst
2>grant select,insert on folks to [ssxtest\wally]
3>go

To remove access, REVOKE also works as it did in MSDE.

Managing Databases

 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 DATABASE.

Moving Databases

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'
2>go

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
3>go

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
1>

C:\>sqlcmd -U sa -P bigpassword
>

Conferences

Coming up...

• MTS 2010 Poland: I've been asked back to Warsaw and if you're thinking of attending MTS, please stop by my talks!
• Techmentor Orlando October 18-21 Las Vegas 2010: Techmentor's back and better than ever, as they return to Orlando with a great lineup of talks. I'm doing talks on DNSSEC, my in-depth Kerberos talk, Windows 7/R2 storage changes, and User Account Control. Info at www.techmentorevents.com.
• Connections Vegas November 1-4: We return to Mandalay Bay for another techstravaganza. Info at the WinConnections web site.
• TechEd EMEA:  No word yet if I'll be in Berlin, watch this space!

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, please).

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 www.minasi.com/forum. 

To subscribe, visit http://www.minasi.com/nwsreg.htm. To change e-mail, format, etc., link to http://www.minasi.com/edit-newsletter-record.htm.  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 http://www.minasi.com/gethelp.

All contents copyright 2005 Mark Minasi. You are encouraged to quote this material, SO LONG as you include this entire document; thanks.